Syntax of 2 left joins and alias

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.

Have you tried pasting either of those queries into phpMyAdmin to see exactly what they return?

Thanks Pikachu - yes I have and both queries return the correct data from the StateName fields. This is why I am stumped!

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.

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.

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.

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?

I take it those are all from only one table?

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?

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 ...

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)