Using last inserted id in mysql multi query

Question

I am trying to use last inserted id in MySQL muli_query but I don't know what I missed

this is my code :

$query = "INSERT INTO posts 
                    (nparc,id_chauffeur,id_camion,
                     lot_de_bord,triangle,pelle,balai,date)
            values('$nparc','$id_chauffeur','$id_camion',
                    '$lot_de_bord','$triangle', '$pelle', 
                    '$balai','$get_datetime');";

$query .= "INSERT INTO photos
                        (post_id,64_image1, 64_image2, 64_image3, 
                         64_image4 ,date_upload)
                values('$mysqli->insert_id','$imsrc1','$imsrc2',
                       '$imsrc3','$imsrc4','$get_datetime');";

$result = mysqli_multi_query($connection, $query) ;

[How to prevent against SQL injection](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Mark, Jan 24 '20 at 14:09
You might be better creating a transaction for this and executing 2 seperate queries wrapped in commit/rollback — RiggsFolly, Jan 24 '20 at 14:15
`$last_id = mysqli_insert_id($connection);` here is the link : https://www.w3schools.com/php/php_mysql_insert_lastid.asp scroll page down to procedural. — , Jan 24 '20 at 14:17
@OualidOukassou the whole query - and check the link Mark put in :) — treyBake, Jan 24 '20 at 14:18
@Dilek never recommend w3schools for PHP - it's a terrible resource that's out of date and full of bad practices — treyBake, Jan 24 '20 at 14:18
@Dilek Beware of w3fools, it is not respected around here. Also check [The manual page](https://www.php.net/manual/en/mysqli.insert-id.php) for how to get this info using OOP — RiggsFolly, Jan 24 '20 at 14:20
@treyBake well its your tought! its a usefull site to me, they have all type of codes, prepared statements, object, pdo etc.. its up to users what they want to do with and which they want to use. — , Jan 24 '20 at 14:20
@Dilek [there's a manual](https://www.php.net/) from PHP themselves - much better resource. All of what you mention is included there - and you're right, it is up to the user, but much better to learn properly — treyBake, Jan 24 '20 at 14:22
@RiggsFolly I dont see the difference `mysqli_insert_id($link);` but I will refer to php site now on. — , Jan 24 '20 at 14:22
You can't use it like this. You're creating the query string, then inserting it. Unless you've already run a query, `$mysqli->insert_id` won't exist when you create the string. You'd be better off using transactions and running this as two queries. — aynber, Jan 24 '20 at 14:26
To get errors out of PHP even in a LIVE environment add these 4 lines to the top of any `MYSQLI_` based script you want to debug `ini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);`. This will force any `MYSQLI_` errors to generate an Exception that you can see on the browser as well as normal PHP errors. — RiggsFolly, Jan 24 '20 at 14:26
@RiggsFolly This would not work with `mysqli_multi_query`. This function is too broken. Yet another reason not to use it. — Dharman, Jan 24 '20 at 14:26
@Dharman Got to close to the problem :) Maybe the OP should follow [this comment](https://stackoverflow.com/questions/59898046/using-last-inserted-id-in-mysql-multi-query#comment105923623_59898046) — RiggsFolly, Jan 24 '20 at 14:28
If you want to use it as one multi_query, you might want to try [`LAST_INSERT_ID()`](https://dev.mysql.com/doc/refman/5.7/en/getting-unique-id.html) instead. — aynber, Jan 24 '20 at 14:28
With his codes it should be like this `if ($connection->multi_query($query) === TRUE) { $last_id = mysqli_insert_id($connection); echo $last_id;` anything else wont work. — , Jan 24 '20 at 14:36
@Dilek - That won't work since they need the last insert id as a value in the second query. — M. Eriksson, Jan 24 '20 at 14:37
@aynber thank you for helping must use LAST_INSERT_ID() instead of $mysqli->insert_id . can admin flag this like good answer — Oualid Oukassou, Jan 24 '20 at 14:39

score 2 · Accepted Answer · answered Jan 24 '20 at 14:35

2

Do not use mysqli_multi_query(). It is never recommended to use.

What you are probably looking for are transactions. You can execute both statements as prepared statements inside of a transaction.

try {
    $connection->begin_transaction();

    $stmt = $connection->prepare('INSERT INTO posts 
    (nparc,id_chauffeur,id_camion,lot_de_bord,triangle,pelle,balai,date)
    values(?,?,?,?,?,?,?,?)');
    $stmt->bind_param('ssssssss', $nparc, $id_chauffeur, $id_camion, $lot_de_bord, $triangle, $pelle, $balai, $get_datetime);
    $stmt->execute();

    $stmt = $connection->prepare('INSERT INTO photos
    (post_id, 64_image1, 64_image2, 64_image3, 64_image4 ,date_upload)
    values(?,?,?,?,?,?)');
    $stmt->bind_param('ssssss', $connection->insert_id, $imsrc1, $imsrc2, $imsrc3, $imsrc4, $get_datetime);
    $stmt->execute();

    $connection->commit();
} catch (\Throwable $e) {
    $connection->rollback();
}

Make sure that you have error reporting enabled otherwise your code won't work. You must put this line before new mysqli()

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

answered Jan 24 '20 at 14:35

Dharman

30,962
25
85
135

Tought prepared statements are better because of adding values on their types, No ? Your example inserting all Data as string, ids is well. – Jan 24 '20 at 15:19
@Dilek The types don't matter. What matters is that you bind the data separately. – Dharman Jan 24 '20 at 15:51
@Dilek I have to insert integer values too . – Oualid Oukassou Jan 27 '20 at 09:38
@oualid Integers are strings too. – Dharman Jan 27 '20 at 09:41
@OualidOukassou integers `i`, string `s`, more : https://www.php.net/manual/en/mysqli-stmt.bind-param.php or do what dharman said – Jan 27 '20 at 09:42
@Dharman well you are wrong on that I think, that will cause problems if he validate integer as a value example `intval(sss);`. or `/^[0-9]*$/` with a regepx OualidOukassou following php refers is beter always. – Jan 27 '20 at 09:50
@Dilek If you can show me an example where it actually causes a problem it would be great. You could also ask another question on stack overflow about it. – Dharman Jan 27 '20 at 09:52
@Dharman you got answer in my previus comment here is another, try to validate string with this function : `function filterNum($numbers){ if(!preg_match("/^[0-9]*$/",$numbers) === false){ return $numbers; }else{ return false; } }` I dont need to as a question I think.lol my question is very clear validate a string with the function in this comment. – Jan 27 '20 at 09:54
@dilek I don't understand you. Please ask a question if you have doubts about my recommendation. Comments are not for extended discussion. – Dharman Jan 27 '20 at 09:55
how can i check if result or if the transaction are complete – Oualid Oukassou Jan 27 '20 at 10:57
@oualid Please ask another question if you have a new problem. – Dharman Jan 27 '20 at 12:00

Using last inserted id in mysql multi query

1 Answers1