CODEWITHSUNDEEP
phpmysqlsql
Alex Connolly
Alex Connolly

Reputation: 25

MySQL - SubQuery - Error in your SQL Syntax

I'm pretty new to SQL, so I'll try to keep it simple as to what I'm trying to do.

I have a system which I'm looking to select messages from, starting with the most recent first, select a maximum of 5 pieces of data, then after that resort them with the latest 'time' column last in order for them to display properly.

Here's the syntax I'm using:

SELECT * FROM messages WHERE sender = '$uid' AND reciever = '$new_user_id' 
OR reciever = '$uid' AND sender = '$new_user_id' ORDER BY id ASC 
FROM (SELECT * FROM messages ORDER BY time DESC)

And here's the error that I'm getting:

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 'FROM (SELECT * FROM messages ORDER BY time DESC)' at line 1

I understand that I'm getting something wrong here, but as SQL isn't really my thing, I haven't a clue where to turn.

A little help would go to great causes, I've been banging my head on the wall for hours.

Thanks.

Upvotes: 2

Views: 307

Answers (3)

Churk
Churk

Reputation: 4637

SELECT * FROM (
    SELECT *
    FROM messages 
    WHERE
    (sender = '$uid' AND reciever = '$new_user_id')
    OR (reciever = '$uid' AND sender = '$new_user_id')
    ORDER BY time asc
    LIMIT 5) AS a ORDER by a.time DESC

Upvotes: 0

thezboe
thezboe

Reputation: 552

No need for the subquery it seems. Take off the subquery and tack on ORDER BY time DESC LIMIT 5.

Upvotes: 0

hjpotter92
hjpotter92

Reputation: 80639

Try this:

SELECT * 
  FROM (SELECT * FROM messages ORDER BY time DESC LIMIT 5)
  WHERE ( sender = '$uid' 
    AND reciever = '$new_user_id' )
    OR ( reciever = '$uid' 
    AND sender = '$new_user_id' )
  ORDER BY time ASC;

EDIT Edited to resort with ascending time values in last 5 items inserted.

Upvotes: 2

Related Questions