By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,854 Members | 2,015 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,854 IT Pros & Developers. It's quick & easy.

Searching Multiple Tables

P: n/a
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.

Aug 7 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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.

Aug 7 '07 #2

P: n/a
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
Aug 7 '07 #3

P: n/a
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
Aug 7 '07 #4

P: n/a
On Aug 7, 10:08 am, Jerim79 <my...@hotmail.comwrote:
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

Aug 7 '07 #5

P: n/a
On Aug 7, 9:26 am, Boris Stumm <st...@informatik.uni-kl.dewrote:
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.

Aug 7 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.