3

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)";
Jorgos
  • 123
  • 1
  • 11
  • @Fred-ii- Could that be the issue? Those same statements do work locally... – Jorgos Feb 08 '15 at 22:12
  • 1
    `phpMyAdmin` is just ` web-based MySQL administration tool` it is neither required, nor it does have an influence on the features of `mysql` or `php` have. – t.niese Feb 08 '15 at 22:13
  • @Jorgos I deleted my comment. It is valid. I thought SET was needed, but after looking at the manual, both methods are valid http://dev.mysql.com/doc/refman/5.6/en/insert.html – Funk Forty Niner Feb 08 '15 at 22:13
  • Thanks for the confirmation, @t.niese! Expected that was the case. Any idea what else might cause the different behaviour? – Jorgos Feb 08 '15 at 22:30
  • If only the result of three of four queries can be seen and no error occurs, then two of them have the same key, otherwise I can't explain that behavior. But without the table structure of `categories` and the final `$sql` that is create on your remote, it is not possible to tell. – t.niese Feb 08 '15 at 22:37
  • Add error reporting to the top of your file(s) right after your opening ` – Funk Forty Niner Feb 08 '15 at 22:40
  • Also try removing the extra semi-colons in all `';";` to read as `'";` – Funk Forty Niner Feb 08 '15 at 22:42
  • @Fred-ii- The error reporting shows me some errors because I'm using insert php variables into javascript code, but nothing for the mysql queries. Also, I'm pretty confident that those semi-colons are required. Removing them stops the query from going through at all. @t.niese The odd thing is that only three out of four get inserted even when I'm just inserting (a value that is not the key), without checking for existing records to update. So a series of `$sql = "INSERT INTO categories (person_ID) VALUES ('$newID');"; $sql.= "INSERT INTO categories (person_ID) VALUES ('$newID');";` – Jorgos Feb 08 '15 at 22:59
  • If the variables are connected to the query, and you're getting errors, then that could explain it. What are the errors? – Funk Forty Niner Feb 08 '15 at 23:01
  • Will it fail after change all the variables to hard-coded values? – user4035 Feb 08 '15 at 23:08
  • @user4035 Thanks, I tried this, see updated question. – Jorgos Feb 09 '15 at 14:02

1 Answers1

2

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();
Jorgos
  • 123
  • 1
  • 11