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

by

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

More Related Contents:

  1. Object of class mysqli_result could not be converted to string
  2. SELECT COUNT(*) AS count – How to use this count
  3. Mysqli update throwing Call to a member function bind_param() error [duplicate]
  4. Strict Standards: mysqli_next_result() error with mysqli_multi_query
  5. Why does mysqli num_rows always return 0?
  6. mysqli::mysqli(): (HY000/2002): Can’t connect to local MySQL server through socket ‘MySQL’ (2)
  7. What is the difference between MySQL, MySQLi and PDO? [closed]
  8. Retrieving Multiple Result sets with stored procedure in php/mysqli
  9. Mysql No connection could be made because the target machine actively refused it
  10. How to connect to MySQL database in PHP using mysqli extension?
  11. How do I count query result rows?
  12. Bind Param with array of parameters
  13. How do you use IN clauses with mysqli prepared statements [duplicate]
  14. What to do with mysqli problems? Errors like mysqli_fetch_array(): Argument #1 must be of type mysqli_result and such
  15. Fatal error: Uncaught exception ‘mysqli_sql_exception’ with message ‘No index used in query/prepared statement’
  16. How to fetch all in assoc array from a prepared statement?
  17. Fatal error: Call to undefined function mysqli_result()
  18. Fatal error: Call to undefined method mysqli_stmt::fetch_array() [duplicate]
  19. Why I am getting an error when using w3school tutorial? [duplicate]
  20. Using wildcards in prepared statement
  21. How to remove the fatal error when fetching an assoc array
  22. Fatal error: Call to undefined method mysqli_stmt::get_result()
  23. PHP MySQLi Multiple Inserts
  24. Fatal error: Call to a member function prepare() on a non-object in
  25. Call to undefined method mysqli_result::fetch()
  26. Which is fastest in PHP- MySQL or MySQLi?
  27. prepared parameterized query with PDO
  28. How to make mysqli connect function?
  29. Dynamically bind mysqli_stmt parameters and then bind result
  30. Check to see if an email is already in the database using prepared statements

Leave a Comment