p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

beginning_php thread: Multiple Table query


Message #1 by "John Arbon" <subscriptions@c...> on Tue, 15 Oct 2002 01:01:30
I have this query...

  switch($searchBy)
  {
    case "permitNumber":
      $table = "vi";
      break;

    default:
      $table = "t"
      break;
  }
  $query = "SELECT * FROM tblTickets as t, tblViolations as v, 
tblVehicleInfo as vi
            WHERE
            ".$table.".$searchBy = '$searchByNumber'
            AND
            t.ticketNumber = v.ticketNumber
            AND
            t.licensePlate = vi.licensePlate
            ";

...that is supposed to query multiple tables.

ticketNumber is a field that is common to tblTickets and tblViolations.
licensePlate is a field that is common to tblTickets and tblVehicleInfo.

Now the question.

The table tblTickets has a one-to-many relationship to tblViolations
and tblVehicleInfo has a one-to-many relationship to tblTickets

Will this query pull all the related information from all tables when the 
ticketNumber meets all the conditions?

If so, how do I retrieve the results?  For some reason I don't think that 
the mysql_fetch_array will work with this, or will it?

John
Message #2 by "Nikolai Devereaux" <yomama@u...> on Mon, 14 Oct 2002 16:59:26 -0700
Before I actually read and answer the question, I have to laugh that you'd need
a database to keep track of all your speeding tickets!

Not that I'm without fault -- I've been to more traffic school classes more
times than I went to my high school physics class. =)


Message #3 by "John Arbon" <subscriptions@c...> on Tue, 15 Oct 2002 01:22:12
Goodness!  Who said that this was for me? LOL!!

Thanks for looking!

John
Message #4 by "Nikolai Devereaux" <yomama@u...> on Mon, 14 Oct 2002 17:22:16 -0700
On to business...  to answer your question, mysql_fetch_array() will return one
row of result data from a select query.  I know you already know this, but it's
important to repeat it plainly.

Your query selects * from three tables, so mysql_fetch_array() will either
return FALSE or an array containing an index for every column in all three
tables.

Since your WHERE clause lists three restrictions, the only rows that match will
be the ones where all conditions are met.

The first restriction is in your $table.$searchBy expression, so for every row
in $table where the $searchBy column = $searchByNumber, you'll find all the
rows in tblViolations and tblVehicleInfo where the appropriate foreign key
columns also match up.

Hope this makes sense..

If it doesn't, you can always see the structure of the result from
mysql_fetch_array for yourself using print_r(), or printr() (my simple wrapper
function that I've posted to the list a bunch of times).

The key thing to remember is that there is an index in the array returned from
mysql_fetch_xxx() for each column that you SELECT in the query.


Take care,

Nik

Message #5 by "John Arbon" <subscriptions@c...> on Mon, 21 Oct 2002 15:58:26
Nik,  sorry this response is so late.  Thanks for your help!

John

  Return to Index