Page 1 of 1
Basic query syntax help please
Posted: Tue May 17, 2005 2:49 am
by thesimon
I have the following query
Code: Select all
SELECT *
FROM pressreleases
ORDER BY RAND() LIMIT 8
Which takes 8 random press releases, which is great, however i would like to change it so it selects the 8 lastest press releases, (it has a "orderdate" column with unix timestamps), then sorts them in a random order.
Posted: Tue May 17, 2005 2:54 am
by JayBird
Code: Select all
SELECT * FROM pressreleases ORDER BY orderdate DESC LIMIT 8
Posted: Tue May 17, 2005 2:55 am
by thesimon
thats great, thanks, however i need to randomise that selection, so take that 8 you have got and order it random
Posted: Tue May 17, 2005 3:03 am
by JayBird
think this will do it
Code: Select all
SELECT * FROM (SELECT * FROM pressreleases as pr ORDER BY orderdate DESC LIMIT 8) pr_rand ORDER BY RAND()
Posted: Tue May 17, 2005 3:21 am
by thesimon
its spitting the dummy for some reason
Posted: Tue May 17, 2005 3:24 am
by JayBird
thesimon wrote:its spitting the dummy for some reason
what's the error?
Posted: Tue May 17, 2005 4:11 am
by thesimon
Error 1065 You have an error in your SQL syntax......to use near 'SELECT * FROM pressreleases as pr ORDER BY orderdate DESC LIMIT
Posted: Tue May 17, 2005 4:27 am
by JayBird
ive just double checked it, works for me
Paste the exact SQL you are using
Posted: Tue May 17, 2005 5:08 am
by thesimon
Must be doing something wrong, just cant pick it.
Code: Select all
<?php
mysql_select_db($database_keenandb, $keenandb);
$query_stringwalk = "SELECT * FROM (SELECT * FROM pressreleases as pr ORDER BY orderdate DESC LIMIT pr_rand ORDER BY RAND()";
$stringwalk = mysql_query($query_stringwalk, $keenandb) or die(mysql_error());
$row_stringwalk = mysql_fetch_assoc($stringwalk);
$totalRows_stringwalk = mysql_num_rows($stringwalk);
?>
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM pressreleases as pr ORDER BY orderdate DESC LIMIT
Posted: Tue May 17, 2005 5:32 am
by thesimon
I changed it to
Code: Select all
(SELECT * FROM pressreleases as pr ORDER BY orderdate DESC LIMIT 8) ORDER BY RAND()
And it is now working beautifully. I cannot thank you enough for your help. You just saved me a long night(it will still be long, but i wont need to worry about that).