Multiple Query Insert in MySQL

Question

I'm having a problem trying to get the results value of my first SQL statement so I can then use in within my second statement.

My first statement is this:

$query = "SELECT restaurantid FROM restaurant WHERE username = '$_SESSION[username]'";
$result=mysql_query($query);

if(!$result)
{
 echo "Error: " . mysql_error() . "Query: " . $query;          
}

  while ($row = mysql_fetch_assoc($result)) {
echo $row['restaurantid'];
}

mysql_free_result($result);

From an inspector console I am using on my browser this statement returns a '1' when launched which is the variable I'm looking for.

Directly underneath my last line (mysql_free_result....) I being my second query like so:

$query = "INSERT INTO deal (dname, description, restaurantid) VALUES ('$name', '$desc', '$result')";
$add = mysql_query($query);
$num = mysql_numrows($add);

if($num != 0) {

echo "true";

} else {

echo "Error: " . mysql_error() . "Query: " . $query;

}

However my problem lies that whenever I try and execute this code my second statement does not appear to be picking up on the previous result value needed from the first query. Just hoping someone could shed some light on the situation for me. I am new to coding and I almost have this correct so all help and advice would be greatly appreciated! Thanks :)

Oskar Persson · Accepted Answer · 2013-02-17 12:01:13Z

1

First off, you shouldn't use Mysql since it has been deprecated and is vulnerable to SQL injections, use Mysqli instead.

$restaurantId = $row['restaurantid'];

Though by using Mysql, the query should look like this "INSERT INTO deal (dname, description, restaurantid) VALUES ('$name', '$desc', '$restaurantId')"

and by using Mysqli it should look like this:

$stmt = $mysqli -> prepare("INSERT INTO deal (dname, description, restaurantid) VALUES (?,?,?)"

$stmt -> bind_param('ssi', $name, $desc, $restaurantId);
$stmt -> execute();

edited Feb 17, 2013 at 12:01

answered Feb 17, 2013 at 11:50

Oskar Persson

6,85517 gold badges71 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2025934 Over a year ago

Hi @Oskwish, I am currently reading up about the switchover alright but as I said I'm new to coding and had mild experience with MySQL so that's why I'm using it now just to create logic behind the process before switching it over to Mysqli. I made the change you suggested however by inserting '$row['restaurantid']' I'm getting a string error, so I removed the commas around restaurantid and it's still not picking up that there is any value there! =/

user2025934 Over a year ago

HAVE IT! Yes that is perfect, thank you so much for you help!! I'm gunna go about chaging it to MySQLi now and teach myself that! Thanks so much!!! :)

Community · Accepted Answer · 2017-05-23 12:27:03Z

0

You could combine the queries into 1

INSERT INTO deal (dname, description, restaurantid) 
SELECT '$name', '$desc', restaurantid 
FROM restaurant 
WHERE username = '$_SESSION[username]'

BTW this is very vurnerable to SQL injections. See here for tips to avoid that

edited May 23, 2017 at 12:27

CommunityBot

11 silver badge

answered Feb 17, 2013 at 11:47

juergen d

205k40 gold badges303 silver badges377 bronze badges

1 Comment

user2025934 Over a year ago

Hi @juergend - Thanks for the speedy response - see the reason I couldn't combine these 2 statements as I was attempting before is because when the first statement was executed alone I was getting a "Resource Id 3" result and upon further investigation I found out it needs to be broken down as far as the mysql_free_result in order to get that referenced value! I'm currently reading up about SQL injections alright, as I said I'm learning so I'm trying to take baby steps by learning the basics first and going from there :)

foxns7 · Accepted Answer · 2013-02-17 12:12:41Z

0

If $_SESSION[username] is the newly inserted record, probably you can assign restaurantid to mysql_insert_id(); right after mysql_query("INSERT INTO restaurant VALUES (some_values)")

or if it's not,

$result = mysql_query($sql) or die(mysql_error());

    while($rows = mysql_fetch_array($result))
    {
        $restaurantid = $rows['restaurantid'];

    }

answered Feb 17, 2013 at 12:12

foxns7

5182 gold badges7 silver badges18 bronze badges

Collectives™ on Stack Overflow

Multiple Query Insert in MySQL

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related