0

I have a simple question, I wanted to know how to make a database query run at the end of the previous one. In my case I am doing an insert and then an update of that insert. Does not update because the insert has not finished.

enter image description here

This is all in php

$id_mensaje_converted = "123";
$body_str = "Hello";

$consulta = "INSERT INTO mensajes (id_mensaje, body) VALUES ('$id_mensaje_converted', '$body_str')";
$resultado = mysqli_query( $conexion, $consulta );

//Then by post I receive a status

if (isset($_POST['status'])){
    $status_str = $_POST['status'];
    $consulta = "UPDATE mensajes SET status = '$status_str' WHERE id_mensaje ='$id_mensaje_converted'";
    $resultado = mysqli_query( $conexion, $consulta );
}
Ruben Amezcua
  • 47
  • 5
  • 2
    Why is this tagged `asynchronous`? PHP is synchronous and will execute your statements sequentially. Something else is going on here. Please turn on error reporting. Also, you're wide open to SQL injection attacks. Look into prepared statements. – waterloomatt Mar 12 '20 at 17:10
  • An easy way to check for errors - https://stackoverflow.com/a/17053489/296555 – waterloomatt Mar 12 '20 at 17:17
  • @waterloomatt Thanks for reply, my actual query has filters, this is just a simple example. I edit the question with a picture of what is happening. – Ruben Amezcua Mar 12 '20 at 17:17
  • @waterloomatt there is no error in the query, the problem is that you want to do an update of an id that does not exist because the insert has not finished. – Ruben Amezcua Mar 12 '20 at 17:19
  • Unless you're using one of those fancy asynchronous PHP frameworks like ReactPHP, the behaviour you want is basically the only one you can get :-? – Álvaro González Mar 12 '20 at 17:21
  • @Ruben - if you're using vanilla PHP, it is not concurrent - it is blocking. You need special libraries to introduce concurrency. If you're using callbacks or some other library then you need to specify that in your question. – waterloomatt Mar 12 '20 at 17:21
  • I am using vanilla php, I don't want to use frameworks, a library if it can be appropriate – Ruben Amezcua Mar 12 '20 at 17:24
  • `... the problem is that you want to do an update of an id that does not exist because the insert has not finished.` but the example you give you are providing the id `$id_mensaje_converted` in both cases so you're not really relying on the insert. Do you mean to use something like `insert_id` to get the newly inserted ID and use that in the update? If you post the relevant parts of your real code, we'd have a better idea. – waterloomatt Mar 12 '20 at 17:28
  • @waterloomatt yes, the problem is I cannot do the update if I have not finished the insert, I have added a new code. – Ruben Amezcua Mar 12 '20 at 17:38
  • OK, so this may be a logic error but we're still not seeing the complete picture yet. Is that all in the same script file? Are there other conditionals around the `insert`? The way I read what you've posted is, the insert will happen for all requests (`get`, `post`, etc.) and the update will only happen for `post` requests. Is that right? Please turn on error reporting and also add some checks around those SQL statements. You can have no confidence in the flow if you aren't checking if they worked. And I say again, this is not a concurrency issue. DB calls will block. – waterloomatt Mar 12 '20 at 17:45
  • Just a hunch, but I suspect your insert is failing but since you're not checking anything the script continues and the update doesn't have anything to update. – waterloomatt Mar 12 '20 at 17:52
  • 1
    Add this to the top of your script: `mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);ini_set('display_errors', 1); ini_set('display_startup_errors', 1); error_reporting(E_ALL);` and then also see the first link I posted. Please post back after making these changes. Thx! – waterloomatt Mar 12 '20 at 17:56
  • 1
    This should only be possible if the `INSERT` query was done with flag `MYSQLI_ASYNC` as third parameter to `mysqli_query()`. Ordinarily, that `mysqli_query()` call will block until the DBMS finished the request, and the subsequent `UPDATE` should work. You probably have other problems in your code that are causing whatever strange behavior you're experiencing. – Havenard Mar 12 '20 at 17:59
  • @waterloomatt I'll try it tomorrow, but if the code is all in a php file and the insert works because I look in the bbdd and there is the insert but the update is not done. – Ruben Amezcua Mar 12 '20 at 18:00
  • 1
    Also, for the love of baby Jesus use [prepared statements](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php)... – Havenard Mar 12 '20 at 18:01
  • `I'll try it tomorrow, but if the code is all in a php file and the insert works because I look in the bbdd and there is the insert but the update is not done.` I could be wrong, but that's likely the issue! If the row is already in the DB, and then your run the `insert` again with the same ID it will blow up with a unique constraint error. Try the methods I've outlined and see if the query is throwing an error. – waterloomatt Mar 12 '20 at 18:08
  • Thanks to all for the answers and the query filters that you have told me, finally I have put in the two queries INSERT INTO ON DUPLICATE KEY UPDATE. with this I always have everything updated. – Ruben Amezcua Mar 13 '20 at 17:18

0 Answers0