CODEWITHSUNDEEP
phpsqlmysqljoin
Pryztojny
Pryztojny

Reputation:

Help with this JOIN

$threads = mysql_query("SELECT DISTINCT t.id, t.title
                        FROM threads t LEFT JOIN comments c ON t.id = c.parentID
                        ORDER BY c.date DESC");

while ($thread = mysql_fetch_assoc($threads)) {

 echo "<li><a href=\"?threadID=$thread[id]\">".htmlspecialchars($thread['title'])."</a></li>\n";

}

Can anyone see a problem in this code? It wont work as i want it. I want it the thread that was commented in latest on top, and the the next latest etc.

echo $thread[parentID] echoes nothing

comments:
id, comment, parentID, date

Upvotes: 1

Views: 145

Answers (5)

Justin Yost
Justin Yost

Reputation: 2340

I tried this and it worked as what it sounds like you want

SELECT DISTINCT t.id, t.title FROM threads AS t LEFT JOIN comments AS c ON t.id = c.parent_id ORDER BY c.date DESC

The only real thing that I changed was using

FROM threads AS T

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425843

You are ordering by a column not in your DISTINCT list.

This is valid syntax in MySQL, however this ORDER BY is meaningless.

This syntax is a MySQL extension, this query will fail in any other RDBMS engine of the big four.

It is used to simplify the DISTINCT and GROUP BY queries in the cases when same value of column in SELECT or ORDER BY always corresponds to same value of column in GROUP BY or DISTINCT.

If your case, you can have multiple values of c.date for each value of t.id, and which value of c.date will be selected for ORDER BY is far from being guaranteed (it can be the last value, the first value or any other value).

Rewrite your query as this:

SELECT  t.id, t.title,
        (
        SELECT  c.date
        FROM    comments c
        WHERE   c.parent_id = t.id
        ORDER BY
                c.date DESC
        LIMIT 1
        ) lastcomment
FROM    threads t
ORDER BY
        ISNULL(lastcomment), lastcomment

Upvotes: 2

Jon
Jon

Reputation: 2085

I haven't used MySQL in awhile so this is something I know works in tSQL. You could try:

SELECT DISTINCT t.id, t.title
FROM threads t LEFT JOIN comments c ON t.id = c.parentID AND 
c.id = (SELECT MAX(c.id) FROM comments where comments.parentID = t.id)
ORDER BY c.date DESC

This could easily get a bit slow with a lot of rows in comments. It might make some sense to store the last comment date in threads if you can if you notice a performance problem.

Upvotes: 0

dogatonic
dogatonic

Reputation: 2788

i'm not sure exactly what you are trying to achieve, but if you want to echo the parentID, you need to include it in the query.

$threads = mysql_query("SELECT DISTINCT t.id, t.title, c.parentID
                        FROM threads t LEFT JOIN comments c ON t.id = c.parentID
                        ORDER BY c.date DESC");

Upvotes: -1

Mez
Mez

Reputation: 24951

Try running the query in MySQL Query browser on your data, and see if this returns any results. If it does, then the issue is with your echo, not your query.

Upvotes: 0

Related Questions