Page 1 of 1

Creating Query where two variables from two tables match

Posted: Fri Jan 16, 2009 3:37 pm
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

Re: Creating Query where two variables from two tables match

Posted: Fri Jan 16, 2009 3:52 pm
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

Re: Creating Query where two variables from two tables match

Posted: Fri Jan 16, 2009 4:43 pm
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!