Posted by Erwin Moller on 08/07/07 14:30

Jerim79 wrote:
> I am trying to create a search system for an existing database.
> Because of the way the database is setup, I need to traverse three
> different tables, gathering information:
>
> 1) Go into table1 and lookup show_id by show name. (For instance,
> let's say I am looking for the Mickey Mouse show. I look it up and
> find that it's show_id is 6)
>
> 2) Go into table2 and lookup all attendee_ids for show_id 6. (For
> instance, I lookup show_id 6 and find 5 attendees. 1, 2, 3, 4 and 5
> are the attendee_id's)
>
> 3) Go into table3 and lookup attendee names, from the 5 attendee_ids.
> (For instance, I lookup 1, 2, 3, 4 and 5 to find their names are Jane,
> Alice, Tom, Peter and Greg.)
>
> I am not free to adjust the tables, so I have to stick with what is
> there.

Hi,

So far it sounds like a nice normalized database.
Please don't 'adjust' it. ;-)
I think your database is designed just fine (based on what you told).

There may be an SQL statement that could do all this in one
> command, but I am no SQL expert.

Yes, that is easy done in one SQL statement that joins the 3 tables.

I was wondering if anyone had some
> PHP oriented solutions. I can get the show_id, no problem. I seem to
> be having a problem with the rest, as it involves arrays. After I go
> into table2 and get all the attendee_ids, I get an array of
> attendee_ids that I need to search table3 for, to create a new array
> of attendee names.

Don't.
Write a single query that joins the 3 tables and let the database do the
hard work.

You might want to throw in the 3 relevant tables (how they are
structured) if you want us to make the join.
(Or better, repost that to a databasenewsgroup)

Good luck.

Regards,
Erwin Moller

[Back to original message]