[SOLVED] Variables from JOIN in mysql question

[graham23s]

Hi GUys,

 

just a question on when i join 2 tables in mysql, say both of my tables have an 'id' (which most of the  time they do)

 

if i do:

 

$id = $row['id'];

 

how can i distinguish between the id's? i mean if table 1 has and id and table 2 has an id how would i set the variable?

 

thanks guys

 

Graham

[Barand]

Give them different aliases

 

SELECT a.id as aid, b.id as bid ,.... FROM tablea a INNER JOIN tableb b ON a.colx = b.coly

[graham23s]

Hi Barand,

 

my query is chopped up like:

 

     $search_query = "SELECT * ";
     $search_query .= "FROM `uploaded_files` LEFT JOIN categories ON uploaded_files.cat_id=categories.id";
     $search_query .= " WHERE (`file_name` LIKE '%$keywords%' OR `file_name` LIKE '%$keywords%')";

 

you see i use * to grab a few rows, where abouts exactly would i place the aliases?

 

thanks for your help

 

Graham

[redarrow]

Dont no if this will help but here's all little example ok.

 

we select 2 tables using inner join but you

only need to say the word join ok.

 

where selecting users as a letter a and pric as letter b.

 

then were saying on a.id=b.id that means select all the database

 

entrys that match the id of the same person.

 

example 1

 

$query="select * from users as a join price as b  on (a.id=b.id)";

 

the result would giv you all the tables that match and show ok.

 

example 2.

if we wanted to show all the results to a users that was logged into

there account we would add the where clause to end off the query.

 

$query="select * from users as a join price as b  on (a.id=b.id) where id=' "'.$_SESSION['id']."' ";

 

when resulted the query would only display all that belongs to the current user.

 

also you can select the table types to display as all abouve would show the id and ect ect

but if you wanted example

 

name date price

 

$query="select name,date,price from users as a join price as b  on (a.id=b.id) where id=' "'.$_SESSION['id']."' ";

 

and if you wanted to sum max min count

 

example

 

$query="select name,date,sum(price) as sum from users as a join price as b  on (a.id=b.id) where id=' "'.$_SESSION['id']."' ";

 

this will show the sum off the current users price.

 

ect ect ect ect ok.

 

if i am wrong on anythink please Quote me ok.

 

love learning but if it all wrong sorry only lerning mysql from a book today ha ha abot time

 

 

 

[Barand]

That's one of several reasons not to use "SELECT * " instead of specifying the columns you need.

 

Others

 

• it's inefficient as it returns larger result sets that neccessary
  
• someone looking at code can see what info comes from which table if they are specified, * tells you nothing
  
• you have no control over the sequence of fields
   
  

[graham23s]

thanks for the input and help guys, i definately understand it a lot better now.

 

cheers

 

Graham

[redarrow]

This better dont no theo always back up.

 

i think it would search the table field for file_name a and b not sure theo.

$search_query = "SELECT * ";
    $search_query .= "FROM `uploaded_files` as a  JOIN categories  as b ON (a.cat_id=b.id)";
    $search_query .= " WHERE (`a.file_name` LIKE '%$keywords%' OR `b.file_name` LIKE '%$keywords%')";