Ansel_Tk1 Posted January 8, 2011 Share Posted January 8, 2011 Hi - wondering if someone could help me out here... I've tried both: SELECT listings.*, states_provinces.*, states_provinces_0.* FROM ((listings LEFT JOIN states_provinces ON states_provinces.StateID=listings.location1provstate) LEFT JOIN states_provinces AS states_provinces_0 ON states_provinces_0.StateID=listings.location2provstate) and SELECT listings.*, states_provinces.*, states_provinces_0.* FROM listings LEFT JOIN states_provinces ON states_provinces.StateID=listings.location1provstate LEFT JOIN states_provinces AS states_provinces_0 ON states_provinces_0.StateID=listings.location2provstate But neither <?php echo $row_Recordset1['states_provinces.StateName']; ?> or <?php echo $row_Recordset1['states_provinces_0.StateName']; ?> give me anything! I know the data is good in the lookup table and <?php echo $row_Recordset1['listings.locationXprovstate']; ?> returns the right StateID values. What the heck am I doing wrong? Many thanks for any assistance. Quote Link to comment https://forums.phpfreaks.com/topic/223772-syntax-of-2-left-joins-and-alias/ Share on other sites More sharing options...
Pikachu2000 Posted January 8, 2011 Share Posted January 8, 2011 Have you tried pasting either of those queries into phpMyAdmin to see exactly what they return? Quote Link to comment https://forums.phpfreaks.com/topic/223772-syntax-of-2-left-joins-and-alias/#findComment-1156658 Share on other sites More sharing options...
Ansel_Tk1 Posted January 8, 2011 Author Share Posted January 8, 2011 Thanks Pikachu - yes I have and both queries return the correct data from the StateName fields. This is why I am stumped! Quote Link to comment https://forums.phpfreaks.com/topic/223772-syntax-of-2-left-joins-and-alias/#findComment-1156661 Share on other sites More sharing options...
Pikachu2000 Posted January 8, 2011 Share Posted January 8, 2011 How are the results listed? As in what are the names that phpMyAdmin returns in the table header of each field? You should be able to use those same names as the array indices when referencing the values. Quote Link to comment https://forums.phpfreaks.com/topic/223772-syntax-of-2-left-joins-and-alias/#findComment-1156663 Share on other sites More sharing options...
Ansel_Tk1 Posted January 8, 2011 Author Share Posted January 8, 2011 Thank you for taking the time to help me Pikachu Both headers are named StateName but when I use <?php echo $row_Recordset1['StateName']; ?><?php echo $row_Recordset1['StateName']; ?> I get the same value for both (and the first one is correct). But when I try <?php echo $row_Recordset1['states_provinces.StateName']; ?><?php echo $row_Recordset1['states_provinces_0.StateName']; ?> I get nothing for either. Quote Link to comment https://forums.phpfreaks.com/topic/223772-syntax-of-2-left-joins-and-alias/#findComment-1156784 Share on other sites More sharing options...
Pikachu2000 Posted January 8, 2011 Share Posted January 8, 2011 OK, time to get smarter than the query, LOL. The result of the print_r() will show you how they are indexed in the array. $query = "SELECT listings.*, states_provinces.*, states_provinces_0.* FROM listings LEFT JOIN states_provinces ON states_provinces.StateID=listings.location1provstate LEFT JOIN states_provinces AS states_provinces_0 ON states_provinces_0.StateID=listings.location2provstate"; $result = mysql_query($query); $array = mysql_fetch_assoc($result); echo '<pre>'; print_r($array); echo '</pre>'; P.S. Since it appears this is ultimately a php issue, I've moved the thread to PHP coding help. Quote Link to comment https://forums.phpfreaks.com/topic/223772-syntax-of-2-left-joins-and-alias/#findComment-1156789 Share on other sites More sharing options...
Ansel_Tk1 Posted January 8, 2011 Author Share Posted January 8, 2011 This is what I got: Array ( [listing_id] => 2 [name] => schoolname [owner] => ownername => emailaddress [website] => website [logo] => [location1address1] => address1 [location1address2] => [location1city] => city [location1provstate] => 740 [location1postal] => M5R 2S7 [location1phone] => 416-555-1212 [location1fax] => [location1tollfree] => [location1long] => [location1lat] => [location1prim] => 1 [location2address1] => [location2address2] => [location2city] => City [location2provstate] => 742 [location2postal] => [location2phone] => [location2fax] => [location2tollfree] => [location2long] => [location2lat] => [location2prim] => [costperweekfrom] => 630 [costperweekto] => [agefrom] => 5 [ageto] => 11 [capacity] => 90 [optionslist] => [description] => [createddate] => 2011-01-08 15:02:00 [published] => 1 [stateID] => 742 [stateCountryID] => 2 [stateAbbrev] => QC [stateName] => Quebec [stateTax] => 0.00000 ) So it looks like the second JOIN is not outputting here? Quote Link to comment https://forums.phpfreaks.com/topic/223772-syntax-of-2-left-joins-and-alias/#findComment-1156805 Share on other sites More sharing options...
Pikachu2000 Posted January 8, 2011 Share Posted January 8, 2011 I take it those are all from only one table? Quote Link to comment https://forums.phpfreaks.com/topic/223772-syntax-of-2-left-joins-and-alias/#findComment-1156809 Share on other sites More sharing options...
Ansel_Tk1 Posted January 9, 2011 Author Share Posted January 9, 2011 No the last 5 are from the states_provinces table (starting from StateID) but it looks like only the first join is listed. Shouldn't there be a second listing from the states_provinces table from location2provstate? Quote Link to comment https://forums.phpfreaks.com/topic/223772-syntax-of-2-left-joins-and-alias/#findComment-1156906 Share on other sites More sharing options...
DavidAM Posted January 9, 2011 Share Posted January 9, 2011 The query is probably working fine. The problem is that you are returning multiple columns with the same name. For instance: SELECT Table1.StateName, Table2.StateName FROM ... will return two columns and they both will be named "StateName". When the database access layer (i.e. mysql_fetch_assoc()) dumps this into a PHP array, one column overwrites the other. You cannot have two indexes in the same array with the same name. The column names will NOT be qualified by the table name in the PHP array. So, you have two choices. Use mysql_fetch_array() to get the columns as a numeric array (without column names) or use an alias on some of the columns so they will have a different name in the array: SELECT Table1.StateName, Table2.StateName AS OtherStateName ... Quote Link to comment https://forums.phpfreaks.com/topic/223772-syntax-of-2-left-joins-and-alias/#findComment-1156956 Share on other sites More sharing options...
Ansel_Tk1 Posted January 9, 2011 Author Share Posted January 9, 2011 Thanks again Pikachu. This got it: SELECT states_provinces_0.StateName AS StateName_1, states_provinces_0.StateID AS StateID_1, listings.*, states_provinces.StateID, states_provinces.StateName FROM ((listings LEFT JOIN states_provinces ON states_provinces.StateID=listings.location1provstate) LEFT JOIN states_provinces AS states_provinces_0 ON states_provinces_0.StateID=listings.location2provstate) Quote Link to comment https://forums.phpfreaks.com/topic/223772-syntax-of-2-left-joins-and-alias/#findComment-1157027 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.