mysqli_multi_query function not updating all table rows in database

Question

I have been attempting to use mysqli_multi_query to update multiple table rows at once. I have found that this function always leads to the update of all rows except for 1 row. For instance, if I had 5 rows of data that I designated to be updated, then only 4 rows are actually updated. Even when I increased the numbers of rows to 6 or 7 etc, there is only 'n-1' rows actually updated ('n' being the numbers that I designated to be updated).

some of the code is below:

<?php    $jag = mysqli_connect($host, $user, $pass, $db); // connects to the database

            /* check connection */
            if (mysqli_connect_errno()) {
                printf("Connect failed: %s\n", mysqli_connect_error());
                exit();
            }


                            $query2 .= "UPDATE inst_prod_stor SET age_type = '2' WHERE payer_mail = '$pmail' AND file_name = '$fname';";

            print_r($query2);



            // execute the 'update' multi query
            $result2 = mysqli_multi_query($jag, $query2) or die(mysqli_error($jag));

            mysqli_close($jag); ?>

I have also checked the source code on my webpage after I execute the php file that cotains this code and it is fine. I receive no errors. I actually used the 'print_r' function to check that no data was being left out before the 'mysqli_multi_query' function executed. And in fact, no data was left out. The result of the function is almost perfect every single time I execute the code. The only imperfection is the fact that one row of the table is never of updated. And each time it's a different row.

I really need help on this one, it is pretty much the last leg of a 2 or 3 week coding journey before I finish up a project that I am currently working on. Thanks!

Answer 1

$result2 = mysqli_multi_query($jag, $query2) or die(mysqli_error($jag));

will only die() if the first query in $query2 has an error.

I suggest that you use a do-while loop to establish a means to debug non-first query sql failures using mysqli_error() & mysqli_affected_rows().

Strict Standards: mysqli_next_result() error with mysqli_multi_query

Answer 2

use mysql_real_escape_string and remove . near $query2

$pmail = mysql_real_escape_string($pmail);
$fname= mysql_real_escape_string($fname);

   $query2 = "UPDATE inst_prod_stor SET age_type = '2' WHERE payer_mail = '$pmail' AND file_name = '$fname';";