[SOLVED] MySQL query returns no results

[bassdog65]

Ok, I am new to PHP and MySQL, but I have a fairly good understanding of what I am doing, but clearly I am missing something.

 

First, here is what I running:

PHP version 5.2.2

MySQL version 5.0.45

 

I am setting up a basic forum like this, and my database structure looks like this:

5 tables, languages, posts, threads, users, words

posts stores the messages

threads stores the subjects

 

I have entries in every tables, and the PHP portion is fine, and I can enter threads into the forum, but when I go to check the messages, I keep getting no messages in the forum.

 

I ran a MySQL query in phpMyAdmin and it returns no rows, even though they are clearly there, so I think my MySQL syntax is wrong.  Here is the query I am trying to run:

 

SELECT t.subject, p.message, username, DATE_FORMAT( CONVERT_TZ( p.posted_ON,  'UTC',  'America/New_York' ) , '%e-%b-%y %l:%i %p' ) AS posted

FROM threads AS t

LEFT JOIN posts AS p

USING ( thread_id )

INNER JOIN users AS u ON p.user_id = u.user_id

WHERE t.thread_id =4

ORDER BY p.posted_on ASC

 

Every time I run it, it returns:

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0011 sec)

 

Clearly I am missing something, but I am new so I am not spotting my error with the joins.

 

Help please!

[Barand]

try changing the INNER to a LEFT join

[bassdog65]

Ok, that actually worked.  Forgive me for sounding nieve here, but my understanding was that needed to be an inner join.  Without explaining the entire concept of joins to me, is there a simple explanation of what I missed there?

 

Also, I very much appreciate the help.

[Barand]

Because you are joining it to a table which is already LEFT JOINED.

 

If you were joining it to the threads table then the INNER would be OK

[bassdog65]

Fantastic!  thank you so much.