Updating database

[cs1h]

Hi,

 

I want to be able to insert a random number into a column in my database, a different random number needs to be put into each different row but I have over 900 rows and it would take too long to do it manually. 

 

Each row has its own id, does anyone know how I could make an automated program to do this?

 

I hope I explained it well enough.

 

Thanks,

 

Colin

[schilly]

-select all your rows

-cycle through each one

-assign random number( rand() function) to it and run the update query

-put that random number in an array

-the next time you cycle through check the random number with the array to make sure you don't get any duplicates

 

[marcus]

Or assign a variable to a ranged value of 1 to 900, shuffle that value, this way there will be no duplicates, grant a foreach statement insert your rows, voila.

 

<?php
$r = range(1,900);

shuffle($r);

foreach($r AS $s){
echo $s . "<br>\n";
}
?>

[craygo]

<?php
$rand = "";
$sql = "SELECT id FROM `table`";
$res = mysql_query($sql) or die(mysql_error());
while($r = mysql_fetch_assoc($res)){
$rand = rand(1,3000);
$update = "UPDATE `table` SET `randomfield` = '$rand' WHERE id = '".$r['id']."' LIMIT 1";
mysql_query($update) or die($mysql_error());
}
?>

 

That is something very simple and doesn't check to see if the random value has a duplicate in the table. for that you could probably:

1. create an array which holds the random values

2. query the table to get the number of rows.

3. run a loop from the result

4. in each loop get the random value and add it to the array

5. once added you can check the rand value being generate against the values in the array.

6. once checked update the row.

 

Ray