query updating same value for all rows

[pranay1995]

Hi all
I have a system that im working on the the code works fine it, but theres a part thats giving me issues below is the code that loops through the number of records in the table and generates the same number of update queries with the random numbers like so:

generate.php code

<?php
include ('themes/config.php');
function genRandomString() {
    $length = 6;
    $characters = '023456789';
$string .= $characters[mt_rand(0, strlen($characters) -1 )];

    for ($p = 0; $p < $length; $p++) {
        $string .= $characters[mt_rand(0, strlen($characters))];
    }
    return $string;
}

    $q = mysql_query("SELECT * FROM details")or die(mysql_error());
    $r = mysql_num_rows($q);

    for($i = 0; $i<$r; $i++){

    $random_string = genRandomString();
    echo '<br>';
    echo $k = "UPDATE details SET password = '$random_string'";
$result = mysql_query($k);

}
?>

and here is the result

UPDATE details SET password = '9235270'
UPDATE details SET password = '599206'
UPDATE details SET password = '3565609'

problem 1) the database is only picking and updating the last query for all the rows which is:
UPDATE details SET password = '3565609'
i want it to update all 3 rows (all the rows when increased) with the different values.

problem 2) i want to apply a prfix e.g. ZX124873.

Please let me know if you need more information or clarification any kind of help will be appreciated. Thanks in advance.

[hericles]

Each of the updates is working correctly it's that the third one runs last and is therefore the one you see. You're problem is this:
echo $k = "UPDATE details SET password = '$random_string'";

This updates the password column in all records of the table as you're not targeting a particular row, like this:
echo $k = "UPDATE details SET password = '$random_string' WHERE id = SOME_ID";
You need to make each update statement target a particular row, presumably by including that row's ID (or other unique value) in a WHERE statement.

For the second question, use CONCAT:
echo $k = "UPDATE details SET password = CONCAT($prefix, '$random_string') WHERE id = SOME_ID";

Hope that helps.

[pranay1995]

@hericles thank you very much for the reply, yes as im trying to set up a cron i need it to update every month on the first one perticular column (password cloumn) with the different values not one specific id, i dont know if that makes sense but thats what i need it to do, update same column with different values for all users. as for the prefix ill try and get back to you please let me know what i should change in my code to achieve the goal. thanks

[diafol]

Is there any reason why you.re limiting your passwords like this? Seems retrograde to me. Six places with a choice of 9 chars will not be very secure. Uniqueness may not be guaranteed either. Just over 531k perms.

[hericles]

From what you said in your second comment my response still stands.
You will need to target each row individually to prevent all user's getting the same password. You say you don't need to target a specific individual but you do, to give them an individual password.

[diafol]

Actually, you can provide a general update statement without a loop and that will provide a different value:

UPDATE `details` SET `password` = CONCAT('ZX124873', RAND())

Will provide something along the lines of what you need, however, limiting to 6 chars or less and not allowing the number '1' will require a (far) more complicated statement.

[diafol]

Just had a look. You could do a simple thing like this:

<?php
/**
 * Created by PhpStorm.
 * User: diafol
 * Date: 21/02/2016
 * Time: 22:09
 */

function createUpdateSQL($length, $allowedChars='023456789', $multiplier=4294967296, $prefix=NULL)
{
    $seedField = 'id';
    $tableName='details';
    $fieldName='password';

    $sql = "UPDATE `$tableName` SET `$fieldName` = CONCAT(";
    if(!is_null($prefix)) $sql .= $prefix . ',';
    $m = strlen($allowedChars) - 1;
    $lines = [];
    $lines[] = "SUBSTRING('$allowedChars', RAND(@seed:=FLOOR(RAND(`$seedField`)*$multiplier))*$m+1,1)";
    if($length-1 > 0) $lines = array_merge($lines,array_fill(1,$length-1, "SUBSTRING('$allowedChars', RAND(@seed:=FLOOR(RAND(@seed)*$multiplier))*$m+1, 1)"));
    $sql .= implode(",", $lines) . ")";
    return $sql;
}

echo createUpdateSQL(6);

This will create your SQL string for you every month. Just change the multiplier, prefix, allowedCharacters etc. As this solution uses the id of the user as the starting seed, the result will always be the same for that particular user, unless you change the variables (multiplier/allowedChars/length)

The above outputs:

UPDATE `details` SET `password` = CONCAT(
SUBSTRING('023456789', RAND(@seed:=FLOOR(RAND(`id`)*4294967296))*8+1,1),
SUBSTRING('023456789', RAND(@seed:=FLOOR(RAND(@seed)*4294967296))*8+1, 1),
SUBSTRING('023456789', RAND(@seed:=FLOOR(RAND(@seed)*4294967296))*8+1, 1),
SUBSTRING('023456789', RAND(@seed:=FLOOR(RAND(@seed)*4294967296))*8+1, 1),
SUBSTRING('023456789', RAND(@seed:=FLOOR(RAND(@seed)*4294967296))*8+1, 1),
SUBSTRING('023456789', RAND(@seed:=FLOOR(RAND(@seed)*4294967296))*8+1, 1)
)

[pranay1995]

@hericles thank you for your suggestion i really appreciate the prefix part worked like a charm but i'd have to give @diafol's suggestion a shot, diafol thank you so much for your response i will try your suggestion and will get back to you. Thank you both again