mysql join with same field names

[glenelkins]

Hi

 

I have 2 tables that i have joined. One of the fields in each table is called "hash". how does this work?? For example:

 

$result = mysql_query ( "SELECT * FROM `theatres` LEFT JOIN `pantomimes_to_theatres` ON `theatres`.`id` = `pantomimes_to_theatres`.`theatre_id` WHERE `pantomimes_to_theatres`.`pantomime_id` = '$id'" );

while ( $r =mysql_fetch_array ( $result ) ) {

    echo $r['hash']; // HOW CAN I TELL WHICH TABLE IT SHOULD COME FROM?
}

 

When i echo $r['hash'] how can i tell it which table i want the hash from?

[Ken2k7]

People need to use * a little more conservatively when using SELECT, especially when you don't need ALL the rows returned. Do you need to select the column hash from ALL the tables or just one of them?

[glenelkins]

just one of them

[revraz]

We can't answer what one you want it from, only you can.  So what table do you want it from?

 

 

When i echo $r['hash'] how can i tell it which table i want the hash from?

[glenelkins]

Ok

 

I want the following fields from the "pantomimes_to_theatres" table

 

start_date

end_date

poster

hash

 

And the following from "theatres"

 

name

address

phone

booking_url

 

And they must join together on `theatres`.`id` = `pantomimes_to_theatres`.`theatre_id` WHERE `pantomimes_to_theatres`.`pantomime_id` = '$id'

 

I did look into using Aliases a little but not sure i follow 100%, as when i tried it with the * wildcard, like this $r["table_alias(field)"]  it didnt work

[Ken2k7]

$result = mysql_query ( "SELECT pt.start_date, pt.end_date, pt.poster, pt.hash, t.name, t.address, t.phone, t.booking_url FROM `theatres` t LEFT JOIN `pantomimes_to_theatres` pt ON t.`id` = pt.`theatre_id` WHERE pt.`pantomime_id` = '$id'" );

[glenelkins]

yes thats what i have just done. i actually wrote the same query you did before you posted it.

 

But now, how would i echo the hash??

 

i mean i tried ( $r["pt(hash)"] ) it doesnt work i dont want to just use $r['hash'] here because i want to know how i would use the mysql Aliases to echo out in php

 

I have seen in some code somewhere the use of AS in the mysql query?

[Ken2k7]

MySQL Alias just renames a returned column name for display. You'll still end up doing $r['something'].

[revraz]

Did you try $r['hash'] ?

[glenelkins]

so if i got the hash field from both tables, how would i only display the hash from say the theatres table?

 

so for example the query was:

 

 

"SELECT t.`name`, t.`address`, t.`phone`, t.`booking_url`, t.`hash`, ptt.`start_date`, ptt.`end_date`, ptt.`poster`, ptt.`hash` FROM `theatres` t LEFT JOIN `pantomimes_to_theatres` ptt ON t.`id` = ptt.`theatre_id` WHERE ptt.`pantomime_id` = '$id'"

 

How would i echo the t.`hash` in php and not the ptt.`hash`

[revraz]

It would be ambiguous and you would alias one of them.  The best way to handle this is to change the field names in your tables when you have a primary key and foreign key.

[glenelkins]

lol ok yeh fair enough. but i still need an answer to my question

[revraz]

what question

[Ken2k7]

SELECT t.`name`, t.`address`, t.`phone`, t.`booking_url`, t.`hash` AS `t_hash`, ptt.`start_date`, ptt.`end_date`, ptt.`poster`, ptt.`hash` AS `ptt_hash` FROM `theatres` t LEFT JOIN `pantomimes_to_theatres` ptt ON t.`id` = ptt.`theatre_id` WHERE ptt.`pantomime_id` = '$id'

 

You're still selecting them in the form of $r['tbl_hash'], so nothing really changed.

[glenelkins]

thanks! thats what i needed to know