I have been doing lots of work on my forums. One of them was setting up the SQL for a "smarter" Forum. The following is the the original SQL query.

SELECT
title,
username,
`timestamp`,
thread_id,
forum_id,
`lock`,
FROM t_threads
WHERE active = 'true'
AND forum_id = '$_GET['forum_id']'
ORDER BY `timestamp` DESC

As you can see, this is a simple version of my forum query.
Here is the same query, after I fixed it up yesterday.


SELECT
COUNT(comment_id) AS id,
t_threads.title,
t_tcomments.thread_id,
edit,
MAX(t_tcomments.timestamp) AS date,
`lock`
FROM t_threads
LEFT JOIN t_tcomments
ON t_tcomments.thread_id = t_threads.thread_id
WHERE t_threads.active = 'true'
AND forum_id = '$_GET['forum_id']'
AND t_tcomments.active = 'true'
GROUP BY t_threads.title
ORDER BY date DESC

If you know basic SQL, you can loosely translate this. But I will translate.

SELECT selects the table columns that are required.
COUNT counts the specific column name. Counting the column 'comment_id' will return the number of comments.
AS gives the count-results and MAX results an alias.
MAX tells the code to return the maximum date.
All the rest of the columns are listed under in this syntax: table_name.column
It is only necessary to specify the table name if the column name is ambiguous (found on both join tables).
FROM specifies the primary table that you will be querying.
LEFT JOIN is like FROM, but it specifies the table to join.
ON specifies where the join will "hinge" at. The query will now look for matching results for the two specified columns.
WHERE will look for these conditions, and return the only the results that match them.
AND obviously joins the conditions if there are more than one to require.
GROUB BY is a confusing thing. It groups ant excess results from any COUNT and JOIN to the specified column
ORDER BY returns the results in the ASC (ascending) or DESC (descending) order of that list.


Confusing?
I thought this may help some of you young developers out there that want to learn.
Please ask any questions you might have!
Have a nice day!