[FIXED] Mysqli multi_query not executing all queries

Issue

I'm updating or inserting multiple rows in database tables using Mysqli and multi_query. They work fine when I use them on my local MAMP server but break down when I take them online. On the remote server only three of the four queries are performed...

Both environments have PHP and mysql version 5+ and include "PHP extension: mysqli". The only difference I see is the phpMyAdmin version. The remote server has 3.5.6, my local MAMP server has 4.2.5. Could this have an influence?

I'm of course changing passwords and all variables should be valid since they work locally.

I'm at a loss... Thanks!

$mysqli = new mysqli("localhost", "root", "root", "database");
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
$sql = "INSERT INTO categories (id, name, descr)  VALUES ('$id1', '$name1', '$descr1') ON DUPLICATE KEY UPDATE name='$name1', descr='$descr1';";
$sql.= "INSERT INTO categories (id, name, descr)  VALUES ('$id2', '$name2', '$descr2') ON DUPLICATE KEY UPDATE name='$name2', descr='$descr2';";
$sql.= "INSERT INTO categories (id, name, descr)  VALUES ('$id3', '$name3', '$descr3') ON DUPLICATE KEY UPDATE name='$name3', descr='$descr3';";
$sql.= "INSERT INTO categories (id, name, descr)  VALUES ('$id4', '$name4', '$descr4') ON DUPLICATE KEY UPDATE name='$name4', descr='$descr4';";

if (!$mysqli->multi_query($sql)) {
     echo "Multi query failed: (" . $mysqli->errno . ") " . $mysqli->error;
}else{
     echo("Succes.");
}
$mysqli->close()

UPDATE: Replacing the queries with a single query does work but not very do-able in all scenarios and requires a lot of re-writing... Using hard-coded variables does not help.

$sql2 = "INSERT INTO categories (id, name, descr)  VALUES ('$id1', '$name1', '$descr1'), ('$id2', '$name2', '$descr2'), ('$id3', '$name3', '$descr3'),('$id4', '$name4', '$descr4') 
    ON DUPLICATE KEY UPDATE name=VALUES(name), descr=VALUES(descr)";

Solution

Fixed the issue by using the 'Object oriented style' as shown on php.net. Still not sure why my previous approach only worked locally but okay, lets call it fixed.

/* execute multi query */
if ($mysqli->multi_query($query)) {
do {
    /* store first result set */
    if ($result = $mysqli->store_result()) {
        while ($row = $result->fetch_row()) {
            printf("%s\n", $row[0]);
        }
        $result->free();
    }
    /* print divider */
    if ($mysqli->more_results()) {
        printf("-----------------\n");
    }
} while ($mysqli->next_result());
}

/* close connection */
$mysqli->close();

Answered By - Jorgos