NYCPHP Meetup

[David Mintz]

I'm gonna try to explain this as briefly and clearly as I can.

I've got a scheduling program for us court interpreters where there's a
table called 'events'. An event has attributes like date, time, judge,
type of proceeding, language, etc. Of course things like judge and
language are stored as smallints pointing to related records in other
tables. Thus, in order to fetch human-meaningful data I have to join
several tables.

"SELECT events.event_id, events.docket,events.event_time,
 	judges.lastname, proceedings.type, languages.name,
	events.notes, languages.lang_id,
	FROM judges, proceedings, languages, events
	WHERE events.event_date = '$date'
	AND events.judge_id = judges.judge_id
	AND events.language_id = languages.lang_id
	AND events.proceeding_id = proceedings.proceeding_id"

OK, so far so good. But I also want to display data from tables that are
in a 1-M relationship to 'events'. An event can have zero or more
interpreters assigned to it, or zero or more defendant names associated
with it. And I only want to display row of data per event. So even if I
could figure out the LEFT JOIN syntax to left-join multiple tables in this
way, if such is possible, it still wouldn't give me what I want.

What I've been doing is building one associative array with the event_ids
as keys and merging it all together with three queries. (There are rarely
more than about 30 elements at this point.)

My question is, would it be more efficient to do the first query first,
and as I loop through the result, query the related tables, resulting in
maybe 60 queries per page access? Or do it with a lot fewer database
calls, but suck up more memory?

I've always thought it kind of rude to hammer the database when there's
another way, but frankly I'm not sure, maybe that's what MySQL is there
for.

TIA,

---
David Mintz
http://davidmintz.org/
Email: See http://dmintzweb.com/whitelist.php first!

"Y dále p'abajo"

	Tito Rojas