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-17T12:01:13.097

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 '13 at 12:01

answered Feb 17 '13 at 11:50

Oskar Persson

7,267
12
48
107

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 Feb 17 '13 at 11:59
1

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!!! :) – user2025934 Feb 17 '13 at 12:06

score 0 · Answer 2 · edited May 23 '17 at 12:27

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 '17 at 12:27

Community

1
1

answered Feb 17 '13 at 11:47

juergen d

186,950
30
261
325

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 :) – user2025934 Feb 17 '13 at 11:51

score 0 · Answer 3 · answered Feb 17 '13 at 12:12

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'];

    }

Multiple Query Insert in MySQL

3 Answers3