Mysql_query won't copy id into another row

Question

I have a INSERT query to copy the value of id into another row called sortingId, which works fine.

It works too when I try to copy id, into sortingId directly in phpmyadmin.

But combined in the following code, it dosen't work. No errors, but sortingId is just 0.

And if I change $sql2 to "asdjh" it doesn't get and error, so it seem like the second mysql_query is just ignored and never executed

$sql1 = "INSERT INTO items (name) VALUES (\'TEC TEST !"#!12\');";
$sql2 = "SELECT @last_id := MAX(id) FROM items; UPDATE items SET sortingId = id WHERE id = @last_id;";

$sql = mysqli_query($this->db, $sql1);
$sql = mysqli_query($this->db, $sql2);

if (mysqli_insert_id($this->db) > 0) {
    $this->response($this->json(array( 'inserted_id' => mysqli_insert_id($this->db))), 200);
}

UPDATE - with the big help from tadman:

Had to separate the mysqli queries by removing the ";" and make a query for each line:

$sql1 = "INSERT INTO items (name) VALUES (\'TEC TEST !"#!12\')";
$sql2 = "SELECT @last_id := MAX(id) FROM items";
$sql3 = "UPDATE items SET sortingId = id WHERE id = @last_id";

$sql = mysqli_query($this->db, $sql1);

if(mysqli_insert_id($this->db) > 0){
    $responsedId = mysqli_insert_id($this->db);
    $sql = mysqli_query($this->db, $sql2);
    $sql = mysqli_query($this->db, $sql3);      
    $this->response($this->json(array( 'inserted_id' => $responsedId)), 200);
}

Note: The object-oriented interface to `mysqli` is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface. Before you get too invested in the procedural style it’s worth switching over. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is an artifact from the PHP 4 era when `mysqli` API was introduced and should not be used in new code. — tadman, Mar 06 '18 at 18:27
Note: Try and get out of the habit of declaring SQL statements in throw-away variables that are used only once. It's a lot easier to follow code where the query is supplied directly to the function, and there's no longer a chance of messing up and sending in `$sql3` instead of the visually similar `$sql8`. — tadman, Mar 06 '18 at 18:27
You can't run more than one query per `query` call. Split that up into two separate operations. — tadman, Mar 06 '18 at 18:27
A lot of problems can be detected and resolved by [enabling exceptions in `mysqli`](https://stackoverflow.com/questions/14578243/turning-query-errors-to-exceptions-in-mysqli) so mistakes aren't easily ignored. — tadman, Mar 06 '18 at 18:27
Thanks a lot! @tadman for all your detailed answers!... I´m new to mysqli and the code i´m trying to modify here is from a tutorial on rest APIs... So you say my problem is that I have two calls on one query? Isn't my code two separate query calls? one with $sql1 and one with $sql2? I can't see how I'm going to make this work. I tried with changing the last $sql to $sqlSecound — Jonas Borneland, Mar 06 '18 at 19:04
The `;` separator is only meaningful to MySQL clients to indicate "end of statement", not low-level drivers like `mysqli` where it's pointless and should be omitted. It's three statements, two of which are jammed into the same string. You'll need to break that out into two separate, self-contained queries. — tadman, Mar 06 '18 at 19:05
Yes!!! great, thank you so much! Can you make it as an answer, so I can mark it as solved? :) — Jonas Borneland, Mar 06 '18 at 19:20

score 0 · Accepted Answer · answered Mar 06 '18 at 20:17

First, you have an escaping/quoting problem in the first example that can be fixed:

$stmt = $this->db->prepare("INSERT INTO items (name) VALUES (?)");
$stmt->bind_param("s", "TEC TEST !\"#!12");
$stmt->execute();

The bind_param function makes it very easy to insert arbitrary data without having to fuss over escaping issues, and as a bonus makes it reliably safe to insert user-supplied data as well without the risk of SQL injections.

The biggest problem here is trying to force two queries into a single query call:

$sql2 = "SELECT @last_id := MAX(id) FROM items; UPDATE items SET sortingId = id WHERE id = @last_id;";

$sql = mysqli_query($this->db, $sql2);

Where the ; separator is only used by interactive clients like the mysql command-line tool or popular front-ends. The mysqli driver doesn't do this for you, instead you need to split it up:

$res1 = $this->db->query("SELECT @last_id := MAX(id) FROM items");
$res2 = $this->db->query("UPDATE items SET sortingId = id WHERE id = @last_id");

Where there I've applied some of the advice I've given about how to use mysqli more effectively as well.

One final thing to note here is that PDO tends to be a lot more pleasant to use, so if you're just getting started consider that approach as well. It's not MySQL specific, so if you ever use another database back-end your time spent learning it won't be wasted.

Mysql_query won't copy id into another row

1 Answers1