glenelkins Posted May 6, 2009 Share Posted May 6, 2009 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? Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted May 6, 2009 Share Posted May 6, 2009 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? Quote Link to comment Share on other sites More sharing options...
glenelkins Posted May 6, 2009 Author Share Posted May 6, 2009 just one of them Quote Link to comment Share on other sites More sharing options...
revraz Posted May 6, 2009 Share Posted May 6, 2009 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? Quote Link to comment Share on other sites More sharing options...
glenelkins Posted May 6, 2009 Author Share Posted May 6, 2009 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 Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted May 6, 2009 Share Posted May 6, 2009 $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'" ); Quote Link to comment Share on other sites More sharing options...
glenelkins Posted May 6, 2009 Author Share Posted May 6, 2009 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? Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted May 6, 2009 Share Posted May 6, 2009 MySQL Alias just renames a returned column name for display. You'll still end up doing $r['something']. Quote Link to comment Share on other sites More sharing options...
revraz Posted May 6, 2009 Share Posted May 6, 2009 Did you try $r['hash'] ? Quote Link to comment Share on other sites More sharing options...
glenelkins Posted May 6, 2009 Author Share Posted May 6, 2009 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` Quote Link to comment Share on other sites More sharing options...
revraz Posted May 6, 2009 Share Posted May 6, 2009 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. Quote Link to comment Share on other sites More sharing options...
glenelkins Posted May 6, 2009 Author Share Posted May 6, 2009 lol ok yeh fair enough. but i still need an answer to my question Quote Link to comment Share on other sites More sharing options...
revraz Posted May 6, 2009 Share Posted May 6, 2009 what question Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted May 6, 2009 Share Posted May 6, 2009 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. Quote Link to comment Share on other sites More sharing options...
glenelkins Posted May 6, 2009 Author Share Posted May 6, 2009 thanks! thats what i needed to know Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.