Random Numbers

[ballouta]

Hello

 

I have a table that contains a column 'stats' which is set by defualt to 1,

I want to update all those rows to make the number randomly updated between 7000 and 10000

any help plz

 

Many thanks

[jtm62]

Hello

 

I have a table that contains a column 'stats' which is set by defualt to 1,

I want to update all those rows to make the number randomly updated between 7000 and 10000

any help please

 

Many thanks

 

Since no one else has taken a stab at this I will.  Based on the information for have given, you need to update the column 'stats' in a db table, with a number between 7000 and 10000.  Since you say the default is 1 I assume this column isn't the PK.  Therefore, the first thing we are going to do is execute a query to pull all the PKs where the column 'stats' = 1 (since we don't want to change a number that has been changed already into an array:

$i=0;
$get_pks = "select PK from table where stats=1";
$pks = mysql_query($get_pks);
while ($row = mysql_fetch_assoc($pks)){
    $pk_array[i]=$row["PK"];
    $i++;
}

 

Cool, so we got all our eligible tuples pulled into any array. Now we need to generate two random numbers: One to pick a tuple at random, and one to pick a number between 7000&10000.

 

For the tuple:

$num_elements = ((count($pk_array))-1); //since array started at zero, we want one less then the count
$tuple_num = rand(0, $num_elements);
$pk_to_update = $pk_array[$tuple_num];

 

For the new entry into stats:

$new_stat_value = rand(7000, 10000);

 

Now we know which tuple is being updated and what the new number will be, so lets update the table:

mysql_query("update table set stats='$new_stat_value' where PK='$pk_to_update'");

 

So, we have all the necessary code to pull it off, all we have to do is encapsulate it in a loop, but what kind... and when should it stop?  For this case a while should be used, and it should run until (count($pk_array))=0. Below is the code for everything.

$i=0;
$get_pks = "select PK from table where stats=1";
$pks = mysql_query($get_pks);
while ($row = mysql_fetch_assoc($pks)){
    $pk_array[i]=$row["PK"];
    $i++;
}
$num_elements = count($pk_array); 

while ($num_elements<>0){
$tuple_num = rand(0, $num_elements-1); //since array started at zero, we want one less than count
$pk_to_update = $pk_array[$tuple_num];
$new_stat_value = rand(7000, 10000);
mysql_query("update table set stats='$new_stat_value' where PK='$pk_to_update'");

$i=0;
$get_pks = "select PK from table where stats=1";
$pks = mysql_query($get_pks);
while ($row = mysql_fetch_assoc($pks)){
    $pk_array[i]=$row["PK"];
    $i++;
}
$num_elements = count($pk_array);
}

 

I'm new to PHP, but not to programming, and while there may be a more efficient way to do this, I'm fairly certain that this code will work for you.

[jtm62]

Hello

 

I have a table that contains a column 'stats' which is set by defualt to 1,

I want to update all those rows to make the number randomly updated between 7000 and 10000

any help please

 

Many thanks

 

Also, if you don't like the double random while method and would rather just insert a random number into each valid row you could use a for loop.

 

$i=0;
$get_pks = "select PK from table where stats=1";
$pks = mysql_query($get_pks);
while ($row = mysql_fetch_assoc($pks)){
    $pk_array[i]=$row["PK"];
    $i++;
}
$num_elements = count($pk_array); 
for ($j=0;$j<$num_elements;j++){
$new_stat_value = rand(7000, 10000);
mysql_query("update table set stats='$new_stat_value' where PK='$pk_array[j]'");
}

[ballouta]

Many thanks, I will read teh code very well and understand it and then test it

 

T C

[kickstart]

Hi

 

Or simply use SQL:-

 

UPDATE `RandomTest` 
SET `RandValue` = FLOOR(7000 + RAND() * (10000 - 7000))
WHERE `RandValue` = 1

 

All the best

 

Keith

[ballouta]

Thanks alot

this is much easier