Jump to content

mysql join with same field names


glenelkins

Recommended Posts

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

$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'" );

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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`

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.