Page 1 of 1

8 Unique Random Records From My Database

Posted: Sat Sep 05, 2009 6:48 pm
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..

Re: 8 Unique Random Records From My Database

Posted: Sat Sep 05, 2009 7:02 pm
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...

Re: 8 Unique Random Records From My Database

Posted: Sat Sep 05, 2009 7:59 pm
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);
    }
 

Re: 8 Unique Random Records From My Database

Posted: Sat Sep 05, 2009 8:21 pm
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.

Re: 8 Unique Random Records From My Database

Posted: Sun Sep 06, 2009 2:37 pm
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);