for each

[boionfire81]

is it possible to make a cron where each row can be assigned a random number value of it's own?

[Coly010]

Each row of what?

[Dayo]

you can do this with SQL (see the example below)

mysql> update test SET `random` = FLOOR(RAND() * 900) + 100; -- Random number between 100 and 1000

I created a simple table to test this with with 100k rows to see how efficient the RAND function is (sill be more efficient then running 100k+ queries)

Query OK, 126818 rows affected (2.27 sec)
Rows matched: 126818 Changed: 126818 Warnings: 0

change the 100 to the the minimum value of what you want then change 900 to the maximum value you want minus the minimum value (if that makes sense).

[boionfire81]

yes but if I run

 

$db->query("UPDATE `table` SET `column`=rand(100,900)");

 

every single entry gets the same update. Say it's stores inventory. Every item gets set to the exact same number of stock. Say it's "lucky chances" every player would get the exact same thing. How can I run a cron where each row/column would get it's own individual value?

[NonStopCoding]

yes but if I run

 

$db->query("UPDATE `table` SET `column`=rand(100,900)");

 

every single entry gets the same update. Say it's stores inventory. Every item gets set to the exact same number of stock. Say it's "lucky chances" every player would get the exact same thing. How can I run a cron where each row/column would get it's own individual value?

 

well you need to add a where statement into your query

$db->query("UPDATE `table` SET `column`=rand(100,900) WHERE `someid` = ".$somevariable['someid']);

 

Edit: the someid is usually the auto increment key value which for items is itmid

[boionfire81]

ok but say the table is items, the column itemstock using that logic every item would get the same value on update. That's the issue I'm having. And being as this theory will be applied to several similar cases it's something has to be figured out how to do without running thousands of queries.

[Dayo]

this was the output of the query i provided

Output

mysql> select * from test limit 10;
+----+--------+
| id | random |
+----+--------+
|  1 |    463 |
|  2 |    529 |
|  3 |    255 |
|  4 |    489 |
|  5 |    679 |
|  6 |    930 |
|  7 |    712 |
|  8 |    673 |
|  9 |    229 |
| 10 |    825 |
+----+--------+
10 rows in set (0.00 sec)

mysql> update test SET `random` = FLOOR(RAND() * 900) + 100; -- Random number between 100 and 1000
Query OK, 126664 rows affected (0.40 sec)
Rows matched: 126818  Changed: 126664  Warnings: 0

mysql> select * from test limit 10;
+----+--------+
| id | random |
+----+--------+
|  1 |    438 |
|  2 |    802 |
|  3 |    796 |
|  4 |    575 |
|  5 |    388 |
|  6 |    116 |
|  7 |    216 |
|  8 |    634 |
|  9 |    620 |
| 10 |    198 |
+----+--------+
10 rows in set (0.00 sec)

mysql>

It looks like you are trying to use PHP in a SQL query as rand(100, 900) is PHP syntax, the MySQL RAND() function has no parameters and only returns a random float from 0 to 1, you have to parse it like i did

Edited June 7, 2016 by Dayo

[boionfire81]

Perfect! worked like a charm :)

ty!

[Dayo]

No problem :)