Populating all rows from a column in SQL from a random PHP integer

Question

I have a SQL database with data however would like to populate a certain field in every row with a different random integer.

The code works to populate the field with one single random integer therefore my question is how do you loop for each row while assigning it a random integer.

Edit: Populating it dependent on another field condition

PHP Code Snippet:

 $data = rand(intval(5000),intval(57000));
 echo $data;
 $sql = "UPDATE Test SET Projection = ".$data;
 mysqli_query($conn,$sql);
 echo "complete"

Possible duplicate of [MySQL UPDATE with random number between 1-3](http://stackoverflow.com/questions/14865632/mysql-update-with-random-number-between-1-3) — u_mulder, Mar 01 '17 at 19:57

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

2

You could use an update too eg:

Update Test set Projection =  FLOOR(1 +  RAND() * 57000 )

To obtain a random integer R in the range i <= R < j,

use the expression
 FLOOR(i + RAND() * (j − i))

see man https://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html#function_rand

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 01 '17 at 19:57

ScaisEdge

131,976
10
91
107

That is exactly what I'm looking for. So why multiply the RAND by 10000? – Paragon Jenko Mar 01 '17 at 19:59
or 57000 if need – ScaisEdge Mar 01 '17 at 19:59
Is that the maximum? – Paragon Jenko Mar 01 '17 at 20:00
remove `AS UNSIGNED)` at the end of line. or add `CAST` before `Floor` @scaisEdge – MohaMad Mar 01 '17 at 20:11

MohaMad · Accepted Answer · 2017-03-01T20:28:02.133

2

you're about to update table with random integer, use MySQL:

SELECT (FLOOR( 1 + RAND( ) * 100 )) -- returns random int in 1-100

your php code:

//$data = rand(intval(5000),intval(57000));
//echo $data;
$low = 5000 ; $high = 57000 ; $d = $high - $low ;
$sql = "UPDATE Test
        SET Projection = (FLOOR( $low +RAND()* $d ))
        WHERE DayColumn in ( 'Friday' , 'Saturday' , 'Sunday')
        ;" ;
mysqli_query($conn,$sql);
echo "complete"

If you want do some thing else based on this value, before updating or after, let me know to edit my answer.

edited Mar 01 '17 at 20:28

answered Mar 01 '17 at 19:58

MohaMad

2,575
2
14
26

The SQL is more suitable as its a one time operation I was looking to do. What does the FLOOR statement do exactly in this query? – Paragon Jenko Mar 01 '17 at 20:01
@AlexJenkinson just read the manual at https://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html#function_floor – Alex Odenthal Mar 01 '17 at 20:03
converts random number (float or double) to int @AlexJenkinson – MohaMad Mar 01 '17 at 20:04
Is it possible to do this with a condition of another field needing a value to be true? – Paragon Jenko Mar 01 '17 at 20:14
1

yes, much complex or heavier than you want! let me edit the code @AlexJenkinson `WHERE col1 = 'val1' AND col2 = 'val2'` added to code – MohaMad Mar 01 '17 at 20:15
@MohaMad So for example if column Day = Friday, Saturday, Sunday. It edits through a random integer? – Paragon Jenko Mar 01 '17 at 20:20
1

Use `in` like this: `WHERE DayColumn in ( 'Friday' , 'Saturday' , 'Sunday')` if DayColumn is in this array, it would update table rows. and a question: DayColumn is string or `date` ? – MohaMad Mar 01 '17 at 20:22
@MohaMad why wouldn't you use an OR in this context? It is a string the date is another column itself. – Paragon Jenko Mar 01 '17 at 20:24
1

`OR` needs more typing! and I don't know any performance differences between `OR` and `IN (..)` [read here for performance and use `IN (..)` :D](http://stackoverflow.com/questions/782915/mysql-or-vs-in-performance) – MohaMad Mar 01 '17 at 20:25
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/136994/discussion-between-alex-jenkinson-and-mohamad). – Paragon Jenko Mar 01 '17 at 20:40

Populating all rows from a column in SQL from a random PHP integer

2 Answers2