Recordset with 2 JOINS
Hi - I realize this is more of a mySQL/PHP question but hopefully some kind soul out there can point out what I am doing wrong.
My records have 2 fields: location1provstate and location2provstate
In my forms I have inserted the ID from the table states_provinces for the fields above.
However when I am trying to display my records I can't figure out how to reference the separate values from the lookup table. Here is what I have:
_______________________
mysql_select_db($database_boomcms, $boomcms);
$query_Recordset1 = "SELECT listings.*, states_provinces.* 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) ";
$Recordset1 = mysql_query($query_Recordset1, $boomcms) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
<?php do { ?>
<?php echo $row_Recordset1['name']; ?><?php echo $row_Recordset1['StateName']; ?><br />
<?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
___________________
<?php echo $row_Recordset1['StateName']; ?> gives me the correct value for location1provstate from the states_provinces lookup table but obviously I also need to get location2provstate.
I have tried <?php echo $row_Recordset1['states_provinces.StateName']; ?> and <?php echo $row_Recordset1['states_provinces_0.StateName']; ?> but I don't get anything back.
Can someone point out what I am doing wrong?
Many thanks.
