8 Unique Random Records From My Database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
zunebuggy
Forum Commoner
Posts: 41
Joined: Wed Aug 27, 2008 1:22 pm

8 Unique Random Records From My Database

Post by zunebuggy »

I have image URLs in my MySQL database and a boolean field named random that flags some of the images (with a 1) as being OK to display randomly on a page. I want to choose 8 random images from my database that are flagged AND none of the 8 images should repeat either. I read about SELECT * FROM table ORDER BY RAND() LIMIT 0,1 and tried to use that but I only get Warnings or Resource id#3. Here is the last thing I tried.

Code: Select all

 
$sql="SELECT * FROM mytablename";
$result = mysql_query($sql);
$number = mysql_num_rows($result);
 
$sql2="SELECT * FROM mytablename ORDER BY RAND() LIMIT 0,$number-1";
$result2 = mysql_query($sql2);
echo $result2;
 
Even if I got this to work, I don't know how to do it 7 more times and not repeat any.

I'm getting comfortable with php, but when I start mixing MySQL with it, I spin my wheels with constant errors

Thank you..
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

Re: 8 Unique Random Records From My Database

Post by jackpf »

You need to use one of the fetch functions to retrieve the records.

Look into mysql_fetch_array() or something similar. Also, may be worth looking up a php/mysql tutorial...
zunebuggy
Forum Commoner
Posts: 41
Joined: Wed Aug 27, 2008 1:22 pm

Re: 8 Unique Random Records From My Database

Post by zunebuggy »

OK... I got the first fetch query to work here but it errors on the second fetch query. I think I'm getting close....

Code: Select all

 
$sql="SELECT pic_number, pic_field2, pic_field3, pic_field4, pic_field5, pic_field6 FROM mytable WHERE random = 1 ORDER BY RAND() LIMIT 1";
$result = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_array($result) or die(mysql_error());
$rnpic1=$row['pic_number'];
$mpicf21=$row['pic_field2'];
$mpicf31=$row['pic_field3'];
$mpicf41=$row['pic_field4'];
$mpicf51=$row['pic_field5'];
$mpicf61=$row['pic_field6'];
    do {
    $result = mysql_query($sql) or die(mysql_error());
    $row = mysql_fetch_array($result) or die(mysql_error());
    while ($row['pic_number']<>$rnpic1);
    }
$rnpic2=$row['pic_number'];
$mpicf22=$row['pic_field2'];
$mpicf32=$row['pic_field3'];
$mpicf42=$row['pic_field4'];
$mpicf52=$row['pic_field5'];
$mpicf62=$row['pic_field6'];
    do {
    $result = mysql_query($sql) or die(mysql_error());
    $row = mysql_fetch_array($result) or die(mysql_error());
    while ($row['pic_number']<>$rnpic1 and $row['pic_number']<>$rnpic2);
    }
$rnpic3=$row['pic_number'];
$mpicf23=$row['pic_field2'];
$mpicf33=$row['pic_field3'];
$mpicf43=$row['pic_field4'];
$mpicf53=$row['pic_field5'];
$mpicf63=$row['pic_field6'];
    do {
    $result = mysql_query($sql) or die(mysql_error());
    $row = mysql_fetch_array($result) or die(mysql_error());
    while ($row['pic_number']<>$rnpic1 and $row['pic_number']<>$rnpic2 and $row['pic_number']<>$rnpic3);
    }
$rnpic4=$row['pic_number'];
$mpicf24=$row['pic_field2'];
$mpicf34=$row['pic_field3'];
$mpicf44=$row['pic_field4'];
$mpicf54=$row['pic_field5'];
$mpicf64=$row['pic_field6'];
    do {
    $result = mysql_query($sql) or die(mysql_error());
    $row = mysql_fetch_array($result) or die(mysql_error());
    while ($row['pic_number']<>$rnpic1 and $row['pic_number']<>$rnpic2 and $row['pic_number']<>$rnpic3) and $row['pic_number']<>$rnpic4);
    }
$rnpic5=$row['pic_number'];
$mpicf25=$row['pic_field2'];
$mpicf35=$row['pic_field3'];
$mpicf45=$row['pic_field4'];
$mpicf55=$row['pic_field5'];
$mpicf65=$row['pic_field6'];
    do {
    $result = mysql_query($sql) or die(mysql_error());
    $row = mysql_fetch_array($result) or die(mysql_error());
    while ($row['pic_number']<>$rnpic1 and $row['pic_number']<>$rnpic2 and $row['pic_number']<>$rnpic3) and $row['pic_number']<>$rnpic4) and $row['pic_number']<>$rnpic5);
    }
$rnpic6=$row['pic_number'];
$mpicf26=$row['pic_field2'];
$mpicf36=$row['pic_field3'];
$mpicf46=$row['pic_field4'];
$mpicf56=$row['pic_field5'];
$mpicf66=$row['pic_field6'];
    do {
    $result = mysql_query($sql) or die(mysql_error());
    $row = mysql_fetch_array($result) or die(mysql_error());
    while ($row['pic_number']<>$rnpic1 and $row['pic_number']<>$rnpic2 and $row['pic_number']<>$rnpic3) and $row['pic_number']<>$rnpic4) and $row['pic_number']<>$rnpic5) and $row['pic_number']<>$rnpic6);
    }
 
zunebuggy
Forum Commoner
Posts: 41
Joined: Wed Aug 27, 2008 1:22 pm

Re: 8 Unique Random Records From My Database

Post by zunebuggy »

In the code above, I am getting the following error and I don't see it:
Parse error: syntax error, unexpected T_VARIABLE, expecting T_WHILE in /home/content/b/b/e/myserver/html/mysite/index.php on line 16
I don't see what is wrong about line 16. It is essential identical to line 5 which works.
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: 8 Unique Random Records From My Database

Post by Darhazer »

Code: Select all

do {
    $result = mysql_query($sql) or die(mysql_error());
    $row = mysql_fetch_array($result) or die(mysql_error());
    while ($row['pic_number']<>$rnpic1);
    }
Should be:

Code: Select all

do {
    $result = mysql_query($sql) or die(mysql_error());
    $row = mysql_fetch_array($result) or die(mysql_error());
    }
    while ($row['pic_number']<>$rnpic1);
   
Post Reply