Page 1 of 1

Update every record with a random number

Posted: Thu Jan 08, 2009 5:11 pm
by scottwater
I want to display pictures in a random order. I think I should create a new field and fill it with random numbers and order it by this new field. I tried ORDER BY rand() but that didn't work right.

It changes the first record only.

Code: Select all

//Printing out values before the change
$query="SELECT * FROM test";
    $result=mysql_query($query) or die(mysql_error());
    while($row=mysql_fetch_array($result))
    {
    echo"ID " . $row['id'] . ": " . $row['the_rand'] . "<br>";
    }
 
$i=1;
    
    
    while($i<=$totalRows_Recordset1)
    {
        $id = $row_Recordset1['id'] ;
        $new_rand=rand();
        //echo "UPDATE test SET the_rand=$new_rand WHERE the_rand=$i...<br>";
        mysql_query("UPDATE test SET the_rand=$new_rand WHERE ID=$id") or die(mysql_error());
        $i++;
        $id = $row_Recordset1['id'] ;
    }
    echo "-------------------------------<br>";
    
    $query="SELECT * FROM test";
    $result=mysql_query($query) or die(mysql_error());
    while($row=mysql_fetch_array($result))
    {
    echo"ID " . $row['id'] . ": " . $row['the_rand'] . "<br>";
    }
   
the output:

ID 1: 1696848048
ID 2: 241
ID 3: 18
-------------------------------
ID 1: 681325506
ID 2: 241
ID 3: 18

When I change "WHERE ID=$id" to WHERE ID=$i I get random numbers.

But that would not work if the id's are 50,51,52

I could really use some advice and am I approaching my goal the right way? I was thinking about runnig this daily to shuffle the cards so to speak.

Thanks! -Scott

Re: Update every record with a random number

Posted: Thu Jan 08, 2009 6:04 pm
by VladSun
scottwater wrote:I want to display pictures in a random order. ... I tried ORDER BY rand() but that didn't work right.
Try fixing it - your current approach is very wrong.

Re: Update every record with a random number

Posted: Thu Jan 08, 2009 9:36 pm
by scottwater
This works. But not real sure its right.

Code: Select all

$query="SELECT * FROM items";
    $result=mysql_query($query) or die(mysql_error());
    while($row=mysql_fetch_array($result))
    {
//    echo"ID " . $row['id'] . ": " . $row['the_rand'] . "<br>";
    
    $id = $row['id'] ;
    $the_rand = rand(0,100000) ;
    $sql_update = "UPDATE items SET the_rand = '$the_rand' WHERE id=$id"; 
     mysql_query($sql_update) or die(mysql_error());
    }
 
//    echo "------------------<br>";
    
    $query="SELECT * FROM items";
    $result=mysql_query($query) or die(mysql_error());
    while($row=mysql_fetch_array($result))
    {
    echo"ID " . $row['id'] . ": " . $row['the_rand'] . "<br>";
    }
 


-Scott

Re: Update every record with a random number

Posted: Sat Jan 10, 2009 7:05 pm
by requinix
scottwater wrote:This works. But not real sure its right.
Forget that code ever existed.

All you need is one query like

Code: Select all

SELECT * FROM items ORDER BY RAND()