Searching Multiple Tables

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. There may be an SQL statement that could do all this in one
command, but I am no SQL expert. 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.

Re: Searching Multiple Tables

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. There may be an SQL statement that could do all this in one
> command, but I am no SQL expert.

select
table3.attendee_name
from
table1, table2, table3
where
table1.show_name = 'Mickey Mouse' and
table1.show_id = table2.show_id and
table2.attendee_id = table3.attendee_id

Note that the default String enclosing characters in MySQL are not
standard, i.e., not '', so you have to change that.

Re: Searching Multiple Tables

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 denormalized 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

Re: Searching Multiple Tables

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

Re: Searching Multiple Tables

On Aug 7, 10:08 am, 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. There may be an SQL statement that could do all this in one
> command, but I am no SQL expert. 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.


$row_rsIDAttend = mysql_fetch_assoc($result_from_atendee_id_sql); //
result from sql request of tbl2


do {

$qry_rsAttendee = "SELECT name FROM table3 WHERE id = " .
$row_rsAttendee['attendee_id']; //sql tbl3
$result = mysql_query($qry_rsAttendee); // run query
$row_rsAttendee = mysql_fetch_assoc($result); //fetch the first/next
array of field data
$array_attendee_names[] = $row_rsAttendee['name']; //fill your data
array of names

}while ($row_rsIDAttend =
mysql_fetch_assoc($result_from_atendee_id_sql));


I wrote this out pretty quick, so check over my syntax, but this
should do what you want.
once the do loop is finished you will have an array
($array_attendee_names[]) with the number of elements in it that
represent your attendee list. Each individual element (attendee name)
can be accessed by refering to an element in the array
($array_attendee_names[1]). The elements start at 0. so if there are
5 attendees they will be numbered 0-4. If you want to show the 3rd
attendee in the list do this:

echo $array_attendee_names[2];

check out this for more on arrays:

http://us2.php.net/manual/en/language.types.array.php - basic array
use info

http://us2.php.net/manual/en/ref.array.php - array functions

GJ

Re: Searching Multiple Tables

On Aug 7, 9:26 am, Boris Stumm wrote:
> 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. There may be an SQL statement that could do all this in one
> > command, but I am no SQL expert.
>
> select
> table3.attendee_name
> from
> table1, table2, table3
> where
> table1.show_name = 'Mickey Mouse' and
> table1.show_id = table2.show_id and
> table2.attendee_id = table3.attendee_id
>
> Note that the default String enclosing characters in MySQL are not
> standard, i.e., not '', so you have to change that.

That worked like a charm. Thank you very much. I took SQL back in
college, and use it from time to time, but never really have a need
for it 90% of the time. Your example immediately made sense.