Extracting data from multiple tables and assigning it to variables

dougmcc1

I'm trying to select fields from 5 tables. This is the select query I'm using:

SELECT t1.page, t1.link, t2.page, t2.link, t3.page, t3.link, t4.page, t4.link, t5.page, t5.link FROM page1 t1, page2 t2, page3 t3, page4 t4, page5 t5 WHERE username='$username' AND id='$id'

I dont know if it works or not because I dont know how to extract the data into variables. Normally I would use:

extract($myrow);

Or:
$page1 = $myrow["??"];
$link1 = $myrow["??"];
...

But I don't know what the variable names are when using the extract() function and I dont know what field names to use when assigning the data to a variable.

Any ideas? Thanks.

LordShryku

Use aliases...

SELECT t1.page AS t1page, 
       t1.link AS t1link, 
       t2.page AS t2page,
       t2.link AS t2link......

Then it would be $myrow['t1page'], $myrow['t1link'], whatever you aliased it as.

dougmcc1

It doesn't work with my WHERE clause. How do I incorporate that into the select statement? Is it like this:

WHERE t1.username='$username' AND t2.username='$username' AND t3.username='$username' ... AND $t1.id='$id' AND t2.id='$id' AND t3.id='$id'...

LordShryku

Eh?

SELECT t1.page AS t1page, 
       t1.link AS t1link,
       t2.page AS t2page,
       t2.link AS t2link,
       t3.page AS t3page,
       t3.link AS t3link,
       t4.page AS t4page,
       t4.link AS t4link,
       t5.page AS t5page,
       t5.link AS t5link
  FROM page1 t1, page2 t2, page3 t3, page4 t4, page5 t5 
 WHERE username='$username' 
   AND id='$id'

dougmcc1

It works with t1.username='$username' AND t1.id='$id' but it breaks when I add all 10 parameters to the WHERE clause or if I just say WHERE username='$username' and id='$id'

LordShryku

Well, do you have id or username in more than one of the tables you're joining there?

dougmcc1

Yeah both fields are in all 5 tables

LordShryku

Ok, so if you don't tell the where clause which table to use in the comparison, how's it going to know which table you're talking about?

dougmcc1

Hey I'm asking the questions here 🙂

So you tell me 😛

Just kidding. Like I said, I tried doing where t#.username='$username' AND t#.id='$id' for all of them but that didnt work

LordShryku

Or clauses would work. I can't say I understand why you have these all stretched out into different tables, but to pull this off, I'd say you'd have to use the "or" clauses.

WHERE (t1.id = '$id'
       AND t1.usernname = '$username)
   OR (t2.id = '$id'
       AND t2.usernname = '$username)
....

dougmcc1

Cool, OR's worked but I know you're right about stretching out these fields into 5 tables. Now that I have this out of the way I'll look into that.

As always, thanks for your help.