Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > PHP Databases
|
PHP Databases Using PHP in conjunction with databases. PHP questions not specific to databases should be directed to one of the other PHP forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the PHP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 1st, 2005, 07:01 AM
Registered User
 
Join Date: Jul 2005
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Converting my sql select to use LEFT JOINs

Hi there - I have a php page that pulls data from a db of courses cross referenced against a schedule table so that I get a series of table rows printed out containing the dates when those courses are running.
The page is designed to show 6 months of the year at a time.

The current sql works fine if the course has a course date in every month from jan to jun or jul to dec but on some occasions the course might not run one particular month. My sql query only finds courses where a course has dates in a particular month, any gaps are ignored. Therefore the loop that uses this data to print out a line of dates doesn't 100% work because there are no gaps in the RS.

An example is where a course has dates in Jul, Aug, Sept, Oct, Nov, Dec - 6 columns are written out. If there is no date for Oct, only 5 cols of data are returned and my loop incorrectly runs only five times.

So the solution to my problem is to convert my existing query to use LEFT JOINs as these will return nulls and these I can detect and use in my loop to write out a blank cell when required.

Now my problem -

Code:
$getDatesAssociatedWithThisCourse = "SELECT tblCourses.courseID, tblCourses.duration, tblSchedule.day, tblSchedule.monthID, tblSchedule.yearID, tblYear.year
FROM tblCourses, tblSchedule, tblYear, tblMonth
WHERE tblCourses.courseID = tblSchedule.courseID 
AND tblSchedule.yearID = tblYear.yearID 
AND tblSchedule.monthID = tblMonth.monthID
AND tblCourses.courseID = ".$link." 
AND tblMonth.monthID = ".$idx." ORDER BY monthID ASC";
Here is my original query. Can anyone provide any tips on how to convert this to one that is LEFT JOIN based? I can only find examples that use two tables, not multiple ones, especially where in some cases vars are added to the sql.

Many thanks
Frank






Similar Threads
Thread Thread Starter Forum Replies Last Post
CR XI left outer joins not working saied80 Crystal Reports 0 January 17th, 2007 12:41 PM
SQL - Help with Joins oldmuttonhead Classic ASP Databases 0 January 21st, 2006 05:02 PM
Help converting MySQL SELECT to JET-SQL Alexandre Access 10 May 21st, 2004 02:31 PM
SQL Table Joins Dredd Classic ASP Databases 2 June 3rd, 2003 06:24 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.