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);