JuliusSecret JuliusSecret - 5 months ago 17
SQL Question

PHP/MySQL: updating every row with a random value - foreach not working (updating all rows with same value)

I have around 1000 records in my MySQL database. I recently added a new column called UniqueKey and I want to update all rows in my table with a random, unique key.

This is what I got:

$updatequery = "SELECT * FROM pictures";
$resultt = $conn->query($updatequery);

while ($roww = $resultt->fetch_array()) {
$rowws[] = $roww;
}

foreach($rowws as &$roww) {
$UniqueKey = substr(str_shuffle('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'), 0, 6);
$sqlupdate = "UPDATE pictures SET UniqueKey = '$UniqueKey'";
mysqli_query($conn, $sqlupdate);

}


The problem here is as follows. It DOES create a UniqueKey, but it uploads this new random value to ALL my rows. How can I do this?:


  • generate random key by $UniqyeKey

  • Insert this key in the first row

  • regenerate a new key and insert this in the next row

  • etc etc



Thanks in advance! Have been trying to fix this for hours..

Answer

There is no need to use the extra foreach loop. You can do the all the job within the while loop itself, as follows:

 while ($roww = $resultt->fetch_array()) {
       $UniqueKey = substr(str_shuffle('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'), 0, 6);
        $sqlupdate = "UPDATE pictures SET UniqueKey = '$UniqueKey' WHERE id = '".$roww['id']."'";
        mysqli_query($conn, $sqlupdate);
    }

Note: The given example assumes that id is the Primary Key in your table. You can change that accordingly

Related: Setting value for one column of all records in table