Jump to content
Ryan8986

SELECT from 3 tables

Recommended Posts

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

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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

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.