4 Table LEFT JOIN Questions

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
sirstrumalot
Forum Commoner
Posts: 27
Joined: Mon May 18, 2009 10:26 pm

4 Table LEFT JOIN Questions

Post by sirstrumalot »

I'm trying to set a form up so that when a user wants to generate a report, it will show results of this query that is listed below. There are 4 tables:

=> Contacts - Holds patient information
=> MissedVisits - Missed Evaluations
=> Daily - Daily Evaluations
=> Evals - Full Evaluations

These tables all have tons of fields about the visits and are designed as seperate forms. Integrating them through a LEFT JOIN or INNER JOIN seems impractical, as it only seems practical to join one of the Evaluation tables to the Contacts table to link a patient to an evaluation.

My goal is to compare a set of criteria in a form to the tables and generate a report showing all records of events happening. It's easy if the user selects which evaluation they want to view, but I also want them to be able to select "All Form Types" and get a report showing all Evaluation types assigned to the $patID, or ID assigned to the patient, which is the same in all tables.

Is this query correct? If not, what should I use to query results from mulitple tables based on a date range (in the established format) and an ID?

NOTE: The SUBSTRING usage is to pull the month, day and year values from the TEXT formatted date MM/DD/YYYY and compare it to the exploded date set on a date field. This is what I use for date ranges.

Code: Select all

$query_selected = "SELECT * FROM contacts LEFT JOIN (missedVisit, daily, eval) ON (missedVisit.patient_id=contacts.contact_id AND eval.patient_id=contacts.contact_id AND daily.patient_id=contacts.contact_id) WHERE SUBSTRING(missedVisits.missed_aptDate,1,2) BETWEEN '$start_month' AND '$end_month' AND SUBSTRING(missedVisits.missed_aptDate,4,2) BETWEEN '$start_day' AND '$end_day' AND SUBSTRING(missedVisits.missed_aptDate,7,4) BETWEEN $start_year AND $end_year OR SUBSTRING(daily.daily_aptDate,1,2) BETWEEN '$start_month' AND '$end_month' AND SUBSTRING(daily.daily_aptDate,4,2) BETWEEN '$start_day' AND '$end_day' AND SUBSTRING(daily.daily_aptDate,7,4) BETWEEN $start_year AND $end_year OR SUBSTRING(eval.eval_aptDate,1,2) BETWEEN '$start_month' AND '$end_month' AND SUBSTRING(eval.eval_aptDate,4,2) BETWEEN '$start_day' AND '$end_day' AND SUBSTRING(eval.eval_aptDate,7,4) BETWEEN $start_year AND $end_year AND contacts.contact_id = ".$_GET['patID']."";
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: 4 Table LEFT JOIN Questions

Post by Eran »

It's hard to say since you didn't give the tables' structure. If the different evaluation tables are similar, they might actually belong in one table with a 'type' field separating the different rows. Otherwise, a UNION is probably in order to get all the data in one query.

I have a question though - why are you using a string field to store dates? use a date field (such as timestamp) for this purpose. You can then use date functions to extract whichever part you need it could use an index as well. Better yet, you can cut replace all your separate range checks with just one, instead of breaking down the date strings to years + months + days.
Post Reply