Jump to content

SELECT from 3 tables

Recommended Posts

My 3 tables:

- id
- user_id
- title
- video
- category_text

- id

- 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,


Share this post

Link to post
Share on other sites

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

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.