Why can't I run two mysqli queries? The second one fails

Question

Is it possible to have two mysqli queries like so?

mysqli_query($dblink, "INSERT INTO images (project_id, user_id, image_name, date_created, link_to_file, link_to_thumbnail, given_name) VALUES ('$project_id', '$user_id', '$image_name', '$date_created', '$link_to_file', '$thumbnail', '$ImageName')");
mysqli_query($dblink, "INSERT INTO images_history (project_id, user_id, image_name, date_created, link_to_file, link_to_thumbnail, given_name, day, month, year) VALUES ('$project_id', '$user_id', '$image_name', '$date_created', '$link_to_file', '$thumbnail', '$ImageName', '$day', '$month', '$year')");

Basically I want to update two tables in my DB. Is there a better way to do this?

You say it is possible but the above is not working. It is only inserting into images but not into images_history. — BrokenCode, Jun 07 '12 at 00:34
well maybe you should check the particular query for issues, its nothing to do with any other query. — , Jun 07 '12 at 00:36
@PartisanEntity You should change `mysql_error()` to `mysqli_error()` since the two are different and `mysql_error()` will not give you any message back when a `mysqli` query fails. — drew010, Jun 07 '12 at 00:55
Of course you can run two or ten or any number of queries. The number doesn't matter. The problem is not related to the number. There is just an error when the second query is executed. — Your Common Sense, Nov 09 '19 at 20:34

score 26 · Accepted Answer · answered Jun 07 '12 at 00:54

26

It is possible with mysqli_multi_query().

Example:

<?php

$mysqli = new mysqli($host, $user, $password, $database);

// create string of queries separated by ;
$query  = "INSERT INTO images (project_id, user_id, image_name, date_created, link_to_file, link_to_thumbnail, given_name) VALUES ('$project_id', '$user_id', '$image_name', '$date_created', '$link_to_file', '$thumbnail', '$ImageName');";
$query .= "INSERT INTO images_history (project_id, user_id, image_name, date_created, link_to_file, link_to_thumbnail, given_name, day, month, year) VALUES ('$project_id', '$user_id', '$image_name', '$date_created', '$link_to_file', '$thumbnail', '$ImageName', '$day', '$month', '$year');";

// execute query - $result is false if the first query failed
$result = mysqli_multi_query($mysqli, $query);

if ($result) {
    do {
        // grab the result of the next query
        if (($result = mysqli_store_result($mysqli)) === false && mysqli_error($mysqli) != '') {
            echo "Query failed: " . mysqli_error($mysqli);
        }
    } while (mysqli_more_results($mysqli) && mysqli_next_result($mysqli)); // while there are more results
} else {
    echo "First query failed..." . mysqli_error($mysqli);
}

The key is that you must use mysqli_multi_query if you want to execute more than one query in a single call. For security reasons, mysqli_query will not execute multiple queries to prevent SQL injections.

Also keep in mind the behavior of mysqli_store_result. It returns FALSE if the query has no result set (which INSERT queries do not) so you must also check mysqli_error to see that it returns an empty string meaning the INSERT was successful.

See:
mysqli_multi_query
mysqli_more_results
mysqli_next_result
mysqli_store_result

answered Jun 07 '12 at 00:54

drew010

68,777
11
134
162

11

I khow this is old, but to whoever still using mysqli_multi_query, as @dre010 explain: _For security reasons, mysqli_query will not execute multiple queries to prevent SQL injections_. So i think you should consider to separate your query into several individual query and use the prepared statement – David Nov 22 '15 at 02:35
I'm confused about this. I'm trying to adapt this so that I can say, 'if a string is found by searching the first table, return the results. If it is not found by searching the first table, and it is not found by searching the second table, then insert it into the second table.' So run the first SELECT statement, but only run the second if the first returns no results. Is there a way to differentiate between which SELECT statement you wish to use within the mysqli_multi_query? – Muckee Jun 27 '16 at 13:06
1

@DjDaihatsu I would say don't over complicate the code and just write the two selects as separate queries and not use multi_select. Depending on your table structures or select lists, the way to differentiate would be to look at what rows were returned in the result and you'll know which statement it came from but that adds extra unnecessary logic that can be avoided if you just make two separate calls to mysqli_query. – drew010 Jun 27 '16 at 15:31
Thanks @drew010 ! So it is acceptable to run multiple separate select statements on different tables within a database before closing a connection? I must have made another error somewhere in my code as I'm having trouble returning results from the second table (in the same database). – Muckee Jun 28 '16 at 09:43
@DjDaihatsu Completely acceptable. Using separate result sets you can even issue separate queries to other tables while still consuming other results. But it's totally fine to issue multiple selects to different tables on the same connection. – drew010 Jun 28 '16 at 14:53
@David the irony is, **nobody asked** how to execute two queries "in a single call". This answer is not only self-contradicting, telling you at the same time that allowing multiple queries is **dangerous** and then proceeding to explain how to do so (as though mysqli_multi_query is somehow magically safer in this regard); but it is also **misleading**, as there is **no mention** of running queries in one go in the question. The OP just wanted to run two queries, no matter how. And the problem was clearly an error on the second one. Probably the worst answer on the whole Stack Overflow site. – Your Common Sense Dec 21 '20 at 15:04
For clarity, if you do try to squeeze two statements into one mysqli_query() call, it will not run either one; it will yield an error ("You have an error in your SQL syntax"), which is good to mitigate injection. This doesn't seem clear from the documentation. – dlo Mar 12 '21 at 16:55
@dlo mitigating a secondary query call has **absolutely nothing to do** with mitigating injections. Go figure. – Your Common Sense Jan 13 '22 at 14:51

score 22 · Answer 2 · edited Mar 15 '22 at 14:49

It's possible. Just use two prepared queries.

$stmt = $dblink->prepare("INSERT INTO images 
(project_id, user_id, image_name, date_created, link_to_file, link_to_thumbnail, given_name) 
VALUES (?,?,?,?,?,?,?)");
$stmt->bind_param("ssssss", $project_id, $user_id, $image_name, $date_created, $link_to_file, $thumbnail, $ImageName);
$stmt->execute();

$stmt = $dblink->prepare("INSERT INTO images_history 
(project_id, user_id, image_name, date_created, link_to_file, link_to_thumbnail, given_name, day, month, year)
VALUES (?,?,?,?,?,?,?,?,?,?)");
$stmt->bind_param("ssssssssss", $project_id, $user_id, $image_name, $date_created, $link_to_file, $thumbnail, $ImageName, $day, $month, $year);
$stmt->execute();

It is not only much cleaner but also 100% safe from SQL injection.

And if one of your queries fails, simply ask mysqli for the error message and then fix the error.

Some answers on Stack Overflow are so self-contradicting that it's just mind-blowing.

The key is that you must use mysqli_multi_query if you want to execute more than one query in a single call. For security reasons, mysqli_query will not execute multiple queries to prevent SQL injections.

It basically says, "The key is that you must use a firearm without a safety catch, because a regular weapon won't let you shoot yourself in the foot. So here is the way to break it down and now you can cripple yourself in a single shot!"

Despite the fact the OP didn't ask how to run two queries in a single call, despite citing the explicit warning that the ability to run multiple queries in a single call is inherently dangerous, the answer nonchalantly provides the way to circumvent this limitation.

The worst part, all this dangerous and toilsome mess is for naught. Simply because there is not a single reason to run several queries in a single call. Running queries one by one is how a database API is meant to be used.

Why can't I run two mysqli queries? The second one fails

2 Answers2

It's possible. Just use two prepared queries.

Linked

Related