How do I query multiple tables??

jtrascap

Hey all,

I'm am totally new to PHP and MySQL, but I'm getting the grasp of it slowly. Usually I make these small single-table dbs I can connect to the web, but I "inherited" a multiple-table db that I have to generate a final result page from, and I'm stuck.

Table 1:
session_id name url desc

Table 2:
session_id presentation movie

Table 3:
session_id contacts

And my result has to be like:

session_id name url desc presentation movie contacts

Seems simple, but I'm getting boned trying to do it. And of course the deadline is "yesterday".

Any ideas, y'all? I'm really in a pickle, and I'm barely up to speed in the PHP world, so any explicit help would not only be appreciated, but would earn several beers from me when you're next in Amsterdam...

Thanks!

deimosaffair

amsterdam, eh? nice place, a friend of mine works there....

anyway....

You can do it both ways.
With sql, you must run a composite query , something like:
select x,y,z from table1
join table2 on .....
join table3 on ....

This is sql, not php, so read the mysql syntax or something.

with php, you can build a table and make a cycle to fill it up, say:
$table[\\'session_id\\'][0] = 0;
$table[\\'url\\'][0] = \\"empty\\";
$counter = 0;
// put code here to get result set from the db
while (result set not empty){
$table[\\'session_id\\'][$counter] = db result;
$table[\\'url\\'][$counter] = db result;
$counter++;
}
/ now you close this result and open the next one. and do this cycle again. don\\'t foerget to reset the counter /
$counter = 0;
while (result set not empty){
$table[field you want here][$counter] = db result;
$table[same story][$counter] = db result;
$counter++;
}

and this is it. crude but effective. But the SQL approach is way better.

rrijnders

You don't say what the relationship is between these tables; are they one-to-one, one-to-many, many-to-many? The relationship of the table entries will determine how you construct your access method.

If you're sure that each session_id has data in all three tables, and the relationship of the tables is one-to-one, you can use the following SQL:

SELECT * FROM table1 a, table2 b, table3 c, WHERE a.session_id=b.session_id AND a.session_id=b.session_id

If, however, the relationship between the tables is not as simple as that, you will need to do multiple queries to get the information you want.

-- Rich