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...
$table = "vi";
$table = "t"
$query = "SELECT * FROM tblTickets as t, tblViolations as v,
tblVehicleInfo as vi
".$table.".$searchBy = '$searchByNumber'
t.ticketNumber = v.ticketNumber
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?
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!
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
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.
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!