Update every record with a random number

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
scottwater
Forum Newbie
Posts: 18
Joined: Tue Nov 11, 2008 9:07 pm
Location: Richmond, VA

Update every record with a random number

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Update every record with a random number

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
scottwater
Forum Newbie
Posts: 18
Joined: Tue Nov 11, 2008 9:07 pm
Location: Richmond, VA

Re: Update every record with a random number

Post 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
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Update every record with a random number

Post 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()


Last bumped by scottwater on Sat Jan 10, 2009 7:05 pm.
Post Reply