Sort MySQL query by a datestamp in another table

I want build a query to return a list of project IDs for the logged-in user, sorted most re开发者_StackOverflow社区cent to least recent?

I have 2 MySQL tables:

• Table 'project_access' contains 'project_id' and'member_id' which attaches users to each project ID.
• Table 'projects' contains a matching 'project_id' and all the details about the project including 'updated' which is a datestamp.

This is what I've got so far:

SELECT project_id FROM project_access WHERE member_id='" . $_SESSION["myid"] . "'  ORDER BY updated DESC"

'updated' is actually in the other table. How do I write this into the query?

Thanks

You use a JOIN clause, as follows:

SELECT projects.project_id, projects.updated
FROM project_access 
INNER JOIN projects
ON project_access.project_id=projects.project_id 
WHERE project_access.member_id='" . $_SESSION["myid"] . "'  
ORDER BY projects.updated DESC

Note: you should read about JOIN clauses:

http://dev.mysql.com/doc/refman/5.0/en/join.html

.. the above query might be appropriate or not depending on your requirements and your tables structure.

You should also make sure that you escape strings correctly, when dealing with SQL statements, in order to avoid SQL injection attacks:

http://php.net/manual/en/function.mysql-real-escape-string.php

http://en.wikipedia.org/wiki/SQL_injection

Your code should read:

WHERE project_access.member_id='" . mysql_real_escape_string($_SESSION["myid"]) . "'

(I assume that you're using PHP here).

SQL JOIN tutorials:

http://www.halfgaar.net/sql-joins-are-easy

http://www.tizag.com/mysqlTutorial/mysqljoins.php

this will do what your after.

"SELECT 
    projects.project_id
FROM project_access 
INNER JOIN projects ON (project_access.project_id = projects.project_id)
WHERE project_access.member_id='" . $_SESSION["myid"] . "'  
ORDER BY projects.updated DESC"

I suggest you read some basic query tutorials though especially relating to joins. This one should help http://www.sql-tutorial.net/SQL-JOIN.asp