Solved

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

Posted on 2014-07-30
4
240 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
4 Comments
 
LVL 108

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Boost your ability to deliver ambitious and competitive web apps by choosing the right JavaScript framework to best suit your project’s needs.
This video teaches users how to migrate an existing Wordpress website to a new domain.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

757 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now