how to set different values for id column of several tables-CodePudding

I need to update id column of several tables with php uniqid() values
after running this code - all rows inside each table has the same value
how to set different value for each row ?

$arr = ['lorem', 'ipsum', 'dolor'];  // table names
foreach($arr as $tb){
    $st = $db-> prepare("select * from " . $tb);
    $st -> execute();
    $arrb = $st->fetchAll();
    foreach($arrb as $elb){
        $id = uniqid();
        $sqb = "update " . $tb . " set id = :aid";
        $stb = $db->prepare($sqb);
        $stb->execute([":aid" => $id]);
    }
}

CodePudding user response：

The issue is that you are updating all rows with the same value generated by the uniqid() function at the beginning of each iteration of the outer foreach loop. You need to update each row with a unique value generated by the uniqid() function.

Here's one way you can fix this issue:

$arr = ['lorem', 'ipsum', 'dolor'];  // table names
foreach($arr as $tb){
    $st = $db-> prepare("select * from " . $tb);
    $st -> execute();
    $arrb = $st->fetchAll();
    foreach($arrb as $elb){
        $id = uniqid();
        $sqb = "update " . $tb . " set id = :aid where id = :old_id";
        $stb = $db->prepare($sqb);
        $stb->execute([":aid" => $id, ":old_id" => $elb['id']]);
    }
}

In this code, for each iteration of the inner foreach loop, a new value is generated by the uniqid() function and the id column of that specific row is updated with that new value.

CodePudding user response：

As already pointed out by Amitesh, your code is setting the same uniqid() value to all rows in each table multiple times. Furthermore, you are preparing the same sql statement for each row, missing one of the important benefits of using prepared statements.

$arr = ['lorem', 'ipsum', 'dolor'];  // table names
foreach($arr as $tb){
    /*
     * assign temporary UUID (requires CHAR(36)) to all rows if there
     * is no other way of uniquely identifying the rows
     */
    $db->query("UPDATE {$tb} SET id = UUID()");

    $st = $db->prepare('select id from ' . $tb);
    $st->execute();
    $arrb = $st->fetchAll();

    // prepare once per table
    $sqb = "update $tb set id = :aid where id = :old_id";
    $stb = $db->prepare($sqb);

    foreach($arrb as $elb){
        // execute once per row
        $stb->execute([':aid' => uniqid(), ':old_id' => $elb['id']]);
    }
}