mysqli_multi_query() function

[CORT0619]

I have googled examples of the use of the mysqli_multi_query and I can't really find anything that applies to what I am doing. Essentially I want to do a insert query, a select query and then a insert query that are dependent on one another but I can't find any examples of anything like this. Does anyone know how to use the mysqli_multi_query() function well and can explain how I can use this??

 

Thanks alot!

[The Little Guy]

Something like this:

 

mysqli_multi_query($db, "
insert into mytable(col1) values ('awesome');
select * from mytable where col1 = 'awesome';
insert into anothertable (col1) values ('last query');
");

[CORT0619]

i did something like the following and i'm receiving a syntax error:

 

$q = "INSERT INTO blah(username, password) VALUES('$username', '$password', 1)";

$q .= "SELECT user_id FROM blah WHERE login_name='$login_name'";

$q .= "INSERT INTO blah2(first_name, last_name, user_id) VALUES('$first_name', '$last_name', '$user_id')";

$r = mysqli_multi_query($db, $q);

if($r) {

 

but like i said i'm receiving a syntax error. Any ideas?

[The Little Guy]

your queries need semi colons at the end of them, notice my code. Also your first query has more values than columns

 

$q = "INSERT INTO blah(username, password) VALUES('$username', '$password', 1);";

$q .= "SELECT user_id FROM blah WHERE login_name='$login_name';";

$q .= "INSERT INTO blah2(first_name, last_name, user_id) VALUES('$first_name', '$last_name', '$user_id');";

$r = mysqli_multi_query($db, $q);

[CORT0619]

Thanks alot that fixed the syntax error that I was receiving but the only problem is the second insert statement is not working and the reason I did the select statement was to get the user_id because the user_id is a foreign key so it has to be the same in both tables.

[The Little Guy]

Save it to a mysql variable like this:

 

$q = "INSERT INTO blah(username, password) VALUES('$username', '$password', 1);";

$q .= "set @user_id = (SELECT user_id FROM blah WHERE login_name='$login_name' limit 1);";

$q .= "INSERT INTO blah2(first_name, last_name, user_id) VALUES('$first_name', '$last_name', @user_id);";

$r = mysqli_multi_query($db, $q);

[The Little Guy]

btw, mysql and php both have the LAST_INSERT_ID() function, they both return a last insert id from an auto increment field.

[CORT0619]

Guy,

 

You are amazing!! I set the select to a variable just like you said and it worked perfectly. I understand mysql okay, but adding php to it is different because you have to figure out how to use certain functions with php. Is there somewhere I can go to find out how to use mysql in php?

 

Also with the insert last id query, where would I use that? Where I manage my database or in my php?

 

Once again thanks so much for your help I've been struggling with this problem for a while.

[CORT0619]

*I meant LAST_INSERT_ID() function*

[The Little Guy]

You would/could use it like this:

$q = "INSERT INTO blah(username, password) VALUES('$username', '$password', 1);";

$q .= "INSERT INTO blah2(first_name, last_name, user_id) VALUES('$first_name', '$last_name', last_insert_id());";

$r = mysqli_multi_query($db, $q);