how can I put consecutive numbers in fields? php mysql

Question

I have a problem with this code


 $queryF = "SELECT * FROM bot_pedidos WHERE id_origen = '1587871428' AND id_pedido_enc = 0";
$executeF = mysqli_query($conn, $queryF);
$fi = 1;
while($fila = mysqli_fetch_array($executeF)){
    $h = $fila["descripcion"];

    $msj .= $fi.$h."<br>";
    $d .= $fi++;

    $numeracion = $fi - 1;
    $updateNum = "UPDATE `bot_pedidos` SET `numeracion`= $numeracion WHERE id_pedido_enc = 0";
    $executeNum = mysqli_query($conn, $updateNum);  

 
}

I want to put in the field numeracion consecutive numbers like 1,2,3... But the problem is that the code update only with the last number.

Image from DB

so, how can I update the fields with consecutive numbers like 1, 2, 3, 4... no 3,3,3

You're not including any reference to an id in your `UPDATE` statement. Meaning that with each iteration you will update all of the records with `id_pedido_enc = 0`. — El_Vanja, Feb 04 '21 at 15:16
Exactly, but I need update with the consecutive numbers every record with id_pedido_enc = 0 — Alexis Murillo, Feb 04 '21 at 15:26

El_Vanja · Accepted Answer · 2021-02-04T15:44:51.567

In your SELECT statement you have a specific filtering condition WHERE id_origen = '1587871428' AND id_pedido_enc = 0 which selects only a handful of rows that match the criteria. But when you iterate this result set, you're using an UPDATE statement with only one condition:

UPDATE `bot_pedidos` SET `numeracion`= $numeracion WHERE id_pedido_enc = 0

So what does this actually do? It updates every row where id_pedido_enc is 0. And not just the ones where id_origen = '1587871428' - literally any row with id_pedido_enc = 0, of any id_origen. They all now have numeracion set to 1. In the next iteration they will all have 2, then 3 and so on, depending on the size of the original result set.

What you really want to do is set the numeracion for each row separately. And to reference just one specific row in the whole table, it's natural to use its id. So, when iterating your result set, include that row's id inside the UPDATE statement:

$updateNum = $conn->prepare('UPDATE `bot_pedidos` SET `numeracion`= ? WHERE id = ?');
$stmt->bind_param('ss', $numeracion, $fila['id']);
$stmt->execute();

If your primary key is not called id, then just change it to whatever it's called in your table.

Note that I have used prepared, parametrized statements instead of directly injecting the variable into the query string. You should always write your queries this way to prevent SQL injection and also because prepared statements automatically take care of any necessary quoting of the parameters you pass.

how can I put consecutive numbers in fields? php mysql

1 Answers1