Solved

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

Posted on 2014-07-30
4
263 Views
Last Modified: 2014-07-30
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
Comment
Question by:chaddcarr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40230382
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
 
LVL 58

Accepted Solution

by:
Gary earned 200 total points
ID: 40230388
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
 
LVL 4

Expert Comment

by:jekautz
ID: 40230420
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
 

Author Closing Comment

by:chaddcarr
ID: 40230531
Thanks!
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When the s#!t hits the fan, you don’t have time to look up who’s on call, draft emails, call collaborators, or send text messages. An instant chat window is definitely the way to go, especially one like HipChat. HipChat is a true business app. An…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
The viewer will learn how to count occurrences of each item in an array.
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question