selecting from multiple tables

[lorddemos90]

Here is my query to pull from DailyDataSF

 

mysql_select_db($database_SearchPrg, $SearchPrg);
$query_SingleFamily = "SELECT * FROM DailyDataSF WHERE STATUS = 'A' OR STATUS='P'";
$SingleFamily = mysql_query($query_SingleFamily, $SearchPrg) or die(mysql_error());
$totalRows_SingleFamily = mysql_num_rows($SingleFamily);

 

And here is my code to loop through each row and display the data.

 

//go through each table row and add data
if ($SingleFamily && mysql_num_rows($SingleFamily)>0) {
  while ($row_SingleFamily = mysql_fetch_assoc($SingleFamily)) 
  {$streetnum = htmlspecialchars($row_SingleFamily['STREET_NUM']);
$streetname = htmlspecialchars($row_SingleFamily['STREET_NAME']);
$City = htmlspecialchars($row_SingleFamily['CITY']);
$state = htmlspecialchars($row_SingleFamily['STATE']);
$zip = htmlspecialchars($row_SingleFamily['ZIP']);
$price = htmlspecialchars($row_SingleFamily['LIST_PRICE']);
$year = htmlspecialchars($row_SingleFamily['YEAR_BUILT']);
$beds = htmlspecialchars($row_SingleFamily['BEDROOMS']);
$baths = htmlspecialchars($row_SingleFamily['BATHS']);
$lotsize = htmlspecialchars($row_SingleFamily['LOT']);
$description = htmlspecialchars($row_SingleFamily['REMARKS']);
$mlsid = htmlspecialchars($row_SingleFamily['MLS_ACCT']);
$agentfn = htmlspecialchars($row_SingleFamily['LA_FIRST_NAME']);
$agentln = htmlspecialchars($row_SingleFamily['LA_LAST_NAME']);
$email = htmlspecialchars($row_SingleFamily['LA_MEMBER_EMAIL']);
$photo = htmlspecialchars($row_SingleFamily['UDF_PHOTO1']);
$school = htmlspecialchars($row_SingleFamily['MIDDLE_SCHOOL']);
$zoning = htmlspecialchars($row_SingleFamily['FTR_ZONING']);
      $str .= "\t<item>\n
        \t\t<title>{$streetnum}  {$streetname}</title>\n
            \t\t<description>{$description}</description>\n
		\t\t <link>http://www.peninsulafirst.idxre.com/idx/detail.cfm?cid=3705&pid={$mlsid}&bid=59&st=2&return=1</link>\n
		\t\t <guid>{$mlsid}</guid>\n
		\t\t <g:agent>{$agentfn}  {$agentln}</g:agent>\n
		\t\t <g:lot_size></g:lot_size>\n
		\t\t <g:bathrooms>{$baths}</g:bathrooms>\n
		\t\t <g:bedrooms>{$beds}</g:bedrooms>\n
		\t\t <g:broker>Peninsula Realty Group, Inc.</g:broker>\n
		\t\t <g:image_link>http://www.momls.com/photosnew/{$photo}</g:image_link>\n
		\t\t <g:listing_status>active</g:listing_status>\n
		\t\t <g:listing_type>for sale</g:listing_type>\n
		\t\t <g:location>{$streetnum}  {$streetname}, {$City}, {$state}, {$zip}, USA</g:location>\n
		\t\t <g:mls_listing_id>{$mlsid}</g:mls_listing_id>\n
		\t\t <g:id>{$mlsid}</g:id>\n
		\t\t <g:mls_name>Monmouth County Association of Realtors, Inc.</g:mls_name>\n
		\t\t <g:price>{$price}</g:price>\n
		\t\t <g:property_type>single family</g:property_type>\n
		\t\t <g:provider_class>broker</g:provider_class>\n
		\t\t <g:school>{$school}</g:school>\n
		\t\t <g:zoning>{$zoning}</g:zoning>\n
            \t</item>\n";}

};

 

What I want to be able to do is to add one more column to the loop called MORE_INFO from another table called SF_MORE_INFO that has a foreign key link to DailyDataSF in the column called MLS_ACCT.  Is there a way to do this and keep the same structure?  I'm stumped.

[thepip3r]

You can either perform a SQL JOIN in your initial query to pull the data you're looking for in a single MySQL query OR with the same setup you're running now, as long as you have the key from the table you want to the extra information from (i think it's SF_MORE_INFO), you could just run a seperate SQL query inside of your current loop (i.e. above) to get the extra information you want to add to your displayable results.

[lorddemos90]

I did

 

mysql_select_db($database_SearchPrg, $SearchPrg);
$query_SingleFamily = "SELECT * FROM DailyDataSF WHERE STATUS = 'A' OR STATUS='P'
INNER JOIN SF_More_Info
ON DailyDataSF.MLS_ACCT = SF_More_Info.MLS_ACCT";
$SingleFamily = mysql_query($query_SingleFamily, $SearchPrg) or die(mysql_error());
$totalRows_SingleFamily = mysql_num_rows($SingleFamily);

 

But it's giving me crap about it "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN SF_More_Info ON DailyDataSF.MLS_ACCT = SF_More_Info.MLS_ACCT' at line 2"