MySql/PHP Insert SET Last_Insert_ID()

Question

I have the following PHP Code

$con = mysqli_connect($host, $user, $password, $database) or die ("Couldn't connect to database"); //assume these variables are declared

$sql = "INSERT INTO Users
       (
           FirstName
           ,MiddleName
           ,LastName
       )
       Values
       (
           'John'
           ,'A'
           ,'Smith'
       );

       SET @petID = LAST_INSERT_ID();

       INSERT INTO Pets
       (
           UserID
           ,Name
       )
       Values
       (
           @petID
           ,'myPet'
       );";

if(!mysqli_query($con, $sql))
{
    die('Error: ' . mysqli($con));
}

mysqli_close($con);

When I attempt to execute this code, this error happens:

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @petID = LAST_INSERT_ID(); INSERT INTO Pets ( UserID ,Name ' at line 24

To test if there was an error with mySql syntax, I put the string in a file and ran it directly using: mysql -u root -p myDatabase < InsertSqlStatement.sql The statement executed without any problems.

What is wrong with my PHP Code?

ChunkyBaconPlz · Accepted Answer · 2013-08-27 20:38:01Z

3

MySQLi won't do multiple queries for security purposes; you want mysqli_multi_query:

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

answered Aug 27, 2013 at 20:38

ChunkyBaconPlz

5822 silver badges10 bronze badges

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

Comments

Sven · Accepted Answer · 2013-08-27 20:37:51Z

2

You cannot execute more than one SQL query in a call to mysqli_query. If you want to execute multiple queries, either send them one by one or make use of mysqli_multi_query.

answered Aug 27, 2013 at 20:37

Sven

71.2k10 gold badges113 silver badges113 bronze badges

Comments

Marc B · Accepted Answer · 2013-08-27 20:37:20Z

1

You cannot run multiple queries within a single query() call in PHP. This is a simplistic security mechanism against some forms of SQL injection attacks. This is built into mysql PHP drivers, and is not something you can bypass. It runs properly when you copy the query string and run it seperately, because the command line tools are NOT bound by the same restriction.

You'll have to run the queries separately:

$result = mysqli_query('INSERT ...');
$result = mysqli_query('SELECT @petID = ...');
$result = mysqli_query('INSERT ....');

answered Aug 27, 2013 at 20:37

Marc B

362k44 gold badges433 silver badges508 bronze badges

1 Comment

Rhs Over a year ago

How does the scope of @petID work? If I do a query to set the petID, will the following mysqli_query be able to pick it up?

Collectives™ on Stack Overflow

MySql/PHP Insert SET Last_Insert_ID()

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related