Much better way of doing this with a join?

[c_shelswell]

Hi i've got a query for mysql together in php but i've had to make 2 queries to get my result when i'm sure i think i could use a join but i'm really not to sure on joins even after doing a good bit of reading.

i've got 2 tables that my query joins already to get two bit of info but then i need to find one more bit of info from a 3rd table using the info from the first half of the query. Here's my code it might be easier.

[code]
$query = "select download_id, media_id from reg_users, purchases where reg_users.email='$email' and purchases.purch_code='$purchCode' and purchases.link_code='$seclCode'";

$result = mysql_query($query);

while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
    $details[] = $row['media_id'];

}

$query = "select title from media where media_id='$details[0]'";
$result = mysql_query($query);

while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
    $title[] = $row['title'];
}
[/code]

as you can see i'm getting one result from the first query but then having to do another query to get my final bit of info. is there a better way to do this?

Cheers

[weknowtheworld]

Hi,

I think you must use a left or right join.

[fenway]

I can re-write that as a join, but i'm not sure how you've linked purchases to reg_users in that first query.

[c_shelswell]

tbh neither am i. I'm not to sure on joins and i found this in a book. It seems to give the desired results so i kept it. The query takes download_id and media_id from the Purchases table where the email matches in reg_users and purch code and link code match in the purchases table.

It works but what i don't get about it is that at the start of the query i have to write "select download_id and media_id from reg_users, purchases" it's the "from reg users" bit that gets me as i actually want to select them from purchases so why do i put reg_users first?

Cheers

[fenway]

Well, you should explicitly indicate which field is from which table with a table prefix (table.column); but you're saying that there's no link between users & purchases?

[c_shelswell]

sorry not sure how you mean by a link? My two tables look like this:

reg_users:
Username - varchar(30) pri key
password - varchar(32)
email - varchar(50)
userid - varchar(32)
userlevel - tinyint(1)
timestamp- int(11)

purchases:
download_id int(10) pri key
media_id int(10)
username varchar(30)
etc etc

I assumed i would link them with the username? Or have i missed something? Thanks very much for your help

[fenway]

Well, a FK constraint would be better... all I meant was that this condition isn't in your query at all!