How can I execute a query across two tables and multiple fields?

I'm building an employee training tracker. In one table (training_items) I have the details for each training item available. In another table (user_training_items), I associate user_id to item_id.

Basically, for a given user_id, I need to query the user_training_items table for NULL "date_completed" records then pull the details of those item_id's from the training_items table and display the results.

My tables are as follows:

training_items
- item_id
- item_title
- item_description
- item_page

user_training_items
- item_id
- user_id

Here is my code thus far. Output is a blank window. I'd appreciate if someone can tell me where I'm failing.

<?php

require_once 'scripts/app_config.php';
require_once 'scripts/database_connection.php';

$tempUserID = '65';

$query = "SELECT * FROM training_items, user_training_items WHERE user_training_items.user_id=$tempUserID AND user_training_items.item_id = training_items.item_id AND user_training_items.date_completed IS NULL";

$result = mysql_query($query) or die(mysql_error());

   while ($row = mysql_fetch_array($result)) {

  echo $row['training_items.item_title'];
  echo $row['training_items.item_description'];
  echo $row['training_items.item_page'];

 }
 
 ?>

Open in new window

chaddcarrAsked:
Who is Participating?
 
GaryConnect With a Mentor Commented:
Try this

$query = "SELECT * FROM training_items t1
	JOIN user_training_items t2
	ON t1.item_id=t2.item_id
	WHERE t2.user_id=$tempUserID 
	AND t2.date_completed IS NULL";

Open in new window

Though there is no date_completed in what you say your table design is and are you sure the date_completed column is/defaults to Null
0
 
Ray PaseurCommented:
It's possible the query found no data.  You can use the "num_rows()" function to find out. (Note the warning on the man page):
http://php.net/manual/en/function.mysql-num-rows.php

Where and how to learn PHP and MySQL:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html

How to get away from using MySQL (and why you must do this):
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

A function you can drop into your script to see if there is data in a variable, such as $row:
http://php.net/manual/en/function.var-dump.php
0
 
jekautzCommented:
To query data from two or more tables, you need to use a JOIN statement.
http://www.w3schools.com/sql/sql_join_left.asp
http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

I might not have this right, but you are looking to do something like this:
SELECT * FROM training_items
WHERE user_training_items.user_id=$tempUserID AND user_training_items.item_id = training_items.item_id AND user_training_items.date_completed IS NULL
LEFT JOIN user_training_items ON training_items.item_id=user_training_items.item_id;

Open in new window

0
 
chaddcarrAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.