Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

php

mysqli

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?

like image 781
BrokenCode Avatar asked Jun 07 '12 00:06

BrokenCode


People also ask

How to execute multiple queries in php?

Multiple statements or multi queries must be executed with mysqli::multi_query(). The individual statements of the statement string are separated by semicolon. Then, all result sets returned by the executed statements must be fetched.

What is multi query?

Definition. Multiple queries in a single report makes the report retrieve information from the Data Warehouse multiple times. Normally, a report will fetch data from the database only once.

How do I find mysqli query error?

Just simply add or die(mysqli_error($db)); at the end of your query, this will print the mysqli error.


2 Answers

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

like image 92
drew010 Avatar answered Nov 16 '22 23:11

drew010


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.

like image 31
Your Common Sense Avatar answered Nov 17 '22 00:11

Your Common Sense