Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

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

0
chaddcarr
Asked:
chaddcarr
1 Solution
 
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
 
GaryCommented:
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
 
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now