Update Help

Databases
1

Hi I’m trying to update one of my tables which I’ve just added 4 new fields. I want to fill the fields with random numbers. I have got other fields in the table with data I need and can not loose :slight_smile:

here is my code, im guessing “set” update all the fields with the same, is there a quick fix to change on field at a time with each random number generated?

while($row = mysql_fetch_assoc($result)){

$number1 = “”;
$number2 = “”;
$number3 = “”;
$number4 = “”;

$number1 = rand(16, 127);
$number2 = rand(16, 127);
$number3 = rand(16, 127);
$number4 = rand(16, 127);

mysql_query("UPDATE locations SET ran1 = '$number1'");
mysql_query("UPDATE locations SET ran2 = '$number2'");
mysql_query("UPDATE locations SET ran3 = '$number3'");
mysql_query("UPDATE locations SET ran4 = '$number4'");
//

}

2

Hi thanks for the reply.

I am getting random numbers but it is filling in every field for say ran1 with the same random number. I thought when the while loop, loops it would generate a new random number for the next row in my table.

Or am I just not following what your saying?

oh BTW the number need to between 16 and 127

3

Obviously that’s not working if all of your values end up being the same. (j/k)

The reason why all of your rows have the same value is because you have no WHERE clause in your SQL. You literally said update every ran1 value to be 5 (or whatever value php chose randomly). To see this, echo your query – it should make much more sense what you are telling MySQL to do.

What you should do is use RAND() inside of the MySQL query. MySQL will give a random value for each row since you are calling the RAND function instead of passing a constant number.

Additionally, if your query is as simple as you show in your first post, you can even combine the 4 queries into one to make it super fast:

UPDATE locations
   SET ran1 = RAND()
     , ran2 = RAND()
     , ran3 = RAND()
     , ran4 = RAND()

If you want to incorporate your min and max values, you still can:

UPDATE locations
   SET ran1 = FLOOR(6 + (RAND()* 121))
     , ran2 = FLOOR(6 + (RAND()* 121))
     , ran3 = FLOOR(6 + (RAND()* 121))
     , ran4 = FLOOR(6 + (RAND()* 121))
4

well the main reason was because I can add extra code after to make sure none of the 4 random numbers for each row match

5

What is the exact query you are running to produce your random values? Did you seed your RAND() with id like r937 suggested?

And to use a min and max for your random numbers, use this (from the MySQL docs):

To obtain a random integer R in the range i <= R < j, use the expression FLOOR(i + RAND() * (j - i)). For example, to obtain a random integer in the range the range 7 <= R < 12, you could use the following statement:

SELECT FLOOR(7 + (RAND() * 5));

6

Sorry guys i’m not too good with all this php/MySql stuff.

This is my understanding, using php function rand() I get a number between the values I wish and assign them to a variable. I then use this variable in the query to add the random number to my field.

For some reason when i go to phpmyadmin all 1200ish row have the same number for said field. I thought the while loop would allow me to run the php function each time meaning a different number would appear in each row.

7

why are you using rand in php? Why aren’t you using it directly in your update query?

8

use the RAND() function and base it on a value in the row

UPDATE locations SET ran1 = RAND(id)