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
Solved

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

Posted on 2014-07-30
4
256 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 109

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Get to know the ins and outs of building a web-based ERP system for your enterprise. Development timeline, technology, and costs outlined.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to dynamically set the form action using jQuery.
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.

856 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