SELECT from 3 tables

[Ryan8986]

My 3 tables:

video
- id
- user_id
- title
- video
- category_text
(etc.)

registered_users
- id

quiz
- id
- video_id

 

I am currently pulling data from video and registered_users and running that data through a loop to display the records that meet the criteria.

This is what I have now (selecting from 2 tables), and it's working:
 

$rs = "SELECT video.id, video, title, description_text, category_text, level_text, pass_text, tags_text, photo, counter, user_name,
DATE_FORMAT(date, '%M %D, %Y') as date FROM video,
registered_users WHERE video.user_id = registered_users.id AND video.category_text = 'English' AND video.level_text = 'advanced' AND video.pass_text = 'featured' ORDER BY id DESC";
$result = mysql_query($rs);
while($row = mysql_fetch_array($result)) {....

I'm having trouble though including data from a third table called "quiz" where quiz.video_id = video.id.

I want to say: "now also include the data from 'quiz' where quiz.video_id = video.id"

I've tried several variations of the following, and it's not working. The page loads, but no content.
 

$rs = "SELECT v.*, ru.*, q.* FROM video v INNER JOIN registered_users ru on v.user_id = ru.id LEFT JOIN quiz q on q.video_id = v.id WHERE v.category_text = 'English' AND v.level_text = 'advanced' AND v.pass_text = 'featured' ORDER BY id DESC";
$result = mysql_query($rs);
while($row = mysql_fetch_array($result)) {....

Any help appreciated.

Thank you very much,

Ryan

[Barand]

First step is check the value of $result. If it's false you have a query error and you need to check what the error is.

 

Don't use "*" in the SELECT clause, specify the columns you need. You have duplicate column names returned and pairs of columns with same values.

 

Lastly, stop using the now defunct mysql_ set of functions (They've gone from php now). Use mysqli_ or, better, PDO functions