MySQL: Trouble with LAST_INSERT_ID()

Question

I want to insert multiple tables in same query with table2 retrieve the id from table1

Here's my sql code

 $q = "
    INSERT INTO Product (pName, pBrand, pCategory, pSize, pQuantity, pPrice, pDetail)
        VALUES('$name', '$brand', '$category', '$size', '$quantity', '$price', '$detail');
    INSERT INTO Image (iName, iExt, iSize, pID)
        VALUES('$img_name', '$img_ext', '$img_size', LAST_INSERT_ID());";

 $mysqli->query($q);

It shown the syntax error. but I copied the output of $q to Run in SQL query at phpMyAdmin it worked. Could you anyone point me out where is my mistake?

    INSERT INTO Product (pName, pBrand, pCategory, pSize, pQuantity, pPrice, pDetail)
        VALUES(....); #1 row affected
    INSERT INTO Image (iName, iExt, iSize, pID)
        VALUES(....); #1 row affected

I don't believe that you can run multiple statements with a single query call — andrewsi, Sep 29 '12 at 04:50
Don't know if it is related or duplicate of [your previous question](http://stackoverflow.com/q/12649706/1369235) — Himanshu, Sep 29 '12 at 04:52
You may need to look at this (http://stackoverflow.com/questions/2708237/php-mysql-transactions-examples) — user1406062, Sep 29 '12 at 04:54
@TimG he's already using mysqli, a better advice is to use prepared statements. — Ja͢ck, Sep 29 '12 at 05:49

score 2 · Answer 1 · answered Sep 29 '12 at 04:52

2

I don't believe that you can run multiple statements with a single query call; you need to call mysqli_multi_query instead:

http://php.net/manual/en/mysqli.multi-query.php

answered Sep 29 '12 at 04:52

andrewsi

10,807
132
35
51

score 0 · Answer 2 · answered Sep 29 '12 at 05:06

$q = "
    INSERT INTO Product (pName, pBrand, pCategory, pSize, pQuantity, pPrice, pDetail)
        VALUES('$name', '$brand', '$category', '$size', '$quantity', '$price', '$detail');
    INSERT INTO Image (iName, iExt, iSize, pID)
        VALUES('$img_name', '$img_ext', '$img_size', LAST_INSERT_ID());";
$arr=explode(";",$q)
for($i=0;isset($arr[$i]);$i++)//or use limit($arr) in place of isset
{

 $mysqli->query('"'.$arr[$i].'"');
}

score 0 · Answer 3 · answered Sep 29 '12 at 05:07

    By default, mysql_query() and mysql_real_query() 
interpret their statement string argument 
as a single statement to be executed, and you process
 the result according to whether 
the statement produces a result set 
(a set of rows, as for SELECT) 
or an affected-rows count (as for INSERT, UPDATE, and so forth).

read C API Support for mysql

MySQL: Trouble with LAST_INSERT_ID()

3 Answers3