Creating Query where two variables from two tables match

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
slaterino
Forum Commoner
Posts: 46
Joined: Fri Jul 11, 2008 10:50 am

Creating Query where two variables from two tables match

Post by slaterino »

Hi,
I am trying to create a query where 'cl_course' in the 'classes' table is simply an ID number for a course. In a seperate table, 'courses' I have a corresponding ID number, called 'cou_ID' in this table, as well as a 'cou_name' field which is the name of the course.

I am therefore trying to create a query where it lists all the entries from the 'classes' table but replaces the 'cl_course' field with 'cou_name' from the 'courses' table. I have come up with a query which almost works, but lists an entry for every course as well as every class, when it should just be for every class. This is the coding I have at the moment. I have tried a number of things but can't work out where I would add some sort of class stating that 'cl_course' equals 'cou_ID'.

Code: Select all

<?php
 
        $result = mysql_query("SELECT * FROM classes, courses ORDER BY cl_date", $conn);
        while($myrow = mysql_fetch_array($result))
             {
               echo "<tr><td><b>";
               echo $myrow['cou_name'];
               echo "</b></td><td> ";
               echo $myrow['cl_date'];
               echo "</td><td><a href=\"edit.php?cl_ID=" . $myrow['cl_ID'] . "\">Edit</a> | <a href=\"delete.php?cl_ID=" . $myrow['cl_ID'] . "\">Delete</a></td></tr><br />";             }
?>
Thanks
Russ
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Creating Query where two variables from two tables match

Post by califdon »

What you seem to be asking about is known as a JOIN. Any time you need to extract related data from more than one table, it is a JOIN. There are 2 general ways to create joins in SQL:
  1. Use JOIN ... ON syntax
  2. Use the WHERE clause
Read this: http://www.tizag.com/mysqlTutorial/mysqljoins.php
slaterino
Forum Commoner
Posts: 46
Joined: Fri Jul 11, 2008 10:50 am

Re: Creating Query where two variables from two tables match

Post by slaterino »

Hey,
Thanks for the help. Think I've got it sorted now. Am using the following:

Code: Select all

       $result = mysql_query("SELECT * FROM classes JOIN courses ON (courses.cou_ID = classes.cl_course) ORDER BY cl_date ", $conn);
Thanks!
Post Reply