Basic query syntax help please

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
thesimon
Forum Commoner
Posts: 40
Joined: Sun Mar 13, 2005 9:44 pm

Basic query syntax help please

Post 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.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Code: Select all

SELECT * FROM pressreleases ORDER BY orderdate DESC LIMIT 8
thesimon
Forum Commoner
Posts: 40
Joined: Sun Mar 13, 2005 9:44 pm

Post by thesimon »

thats great, thanks, however i need to randomise that selection, so take that 8 you have got and order it random
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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()
thesimon
Forum Commoner
Posts: 40
Joined: Sun Mar 13, 2005 9:44 pm

Post by thesimon »

its spitting the dummy for some reason
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

thesimon wrote:its spitting the dummy for some reason
what's the error?
thesimon
Forum Commoner
Posts: 40
Joined: Sun Mar 13, 2005 9:44 pm

Post 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
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

ive just double checked it, works for me

Paste the exact SQL you are using
thesimon
Forum Commoner
Posts: 40
Joined: Sun Mar 13, 2005 9:44 pm

Post 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
thesimon
Forum Commoner
Posts: 40
Joined: Sun Mar 13, 2005 9:44 pm

Post 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).
Post Reply