Posted by gentleJuggernaut on 08/07/07 14:37

On Aug 7, 10:08 am, Jerim79 <my...@hotmail.com> 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

[Back to original message]