My code below takes the results of $query1 and queries $query2. It then puts the results into a html table.
My question is, instead of having 2 separate queries & so much php code, is there a single query which can be used to achieve the same result?
Thanks for your help.
<?php
include("/path/to/my/server.com/public_html/db_connect.php");
$mktime = date('Y-m-d');
$sendate = date('Y-m-d', strtotime('2 days ago'));
$query1=("SELECT * FROM test_log WHERE '$mktime' < expiry ORDER BY id DESC");
$result1=mysql_query($query1);
$num1=mysql_num_rows($result1);
$subject = "Latest Classified Ads - Condo";
$message_tmp = "<div align='center'>";
$message_tmp .= "<TABLE cellSpacing=0 cellPadding=0 width=100% border=0 id='table1'>";
$message_tmp .= "<TR>";
$message_tmp .= "<TD width=100% height=20 bgcolor='#E5E5E5' align='left' valign='top'>
<p align='center'><b><font size='3'>Comprehensive Real Estate Agency Tools & Property Valuation Services at <a href=\"http://www.mysite.com/free-email-alerts\">http://www.mysite.com</a></font></b></TD>";
$message_tmp .= "</TR>";
$message_tmp .= "<TR>";
$message_tmp .= "<TD width=100% height=20 bgcolor='#EFEFEF' align='left' valign='top'>
~ Classified Ads<br>
~ Facebook Promotions<br>
~ Public Records Search<br>
~ Demographic Statistics<br>
~ Property Market Values<br>
~ Property Owners Listings<br>
~ Real Estate Price Trend Analysis<br>
</p></TD>";
$message_tmp .= "</TR>";
$message_tmp .= "<TR>";
$message_tmp .= "</TR>";
$message_tmp .= "</TABLE>";
$message_tmp .= "</div>";
$message_tmp .= "<h3>List of Latest Classified Ads - Condo</h3>";
$message_tmp .= "<table bgcolor='black' cellspacing='1' width='100%'><tr bgcolor='white' align='center'>";
$message_tmp .= "<td><b>Date</b></td>";
$message_tmp .= "<td><b>Location</b></td>";
$message_tmp .= "<td><b>Property Type</b></td>";
$message_tmp .= "<td><b>Tenure</b></td>";
$message_tmp .= "<td><b>Status</b></td>";
$message_tmp .= "<td><b>Sale / Rent</b></td>";
$message_tmp .= "<td><b>Size</b></td>";
$message_tmp .= "<td><b>Price</b></td>";
$message_tmp .= "<td><b>Price Psf</b></td>";
$message_tmp .= "<td><b>Rooms</b></td>";
$message_tmp .= "<td><b>Baths</b></td>";
$message_tmp .= "<td><b>Renovations</b></td>";
$message_tmp .= "<td><b>Furnishings</b></td>";
$message_tmp .= "<td><b>Phone</b></td>";
$message_tmp .= "<td><b>Advertiser</b></td>";
$message_tmp .= "<td><b>E Number</b></td>";
$message_tmp .= "</tr>";
$message_tmp .= "[new_rows]";
$message_tmp .= "</table>";
$message_tmp .= "<br><font color='#FF0000'><strong>To unsubscribe, go to</font> <a href=\"http://www.mysite.com/unsubscribe-from-email-alerts/\">http://www.mysite.com/unsubscribe-from-email-alerts/</a></strong>";
$message_tmp .= "<br><br><font color='#FF0000'><strong>Try our free services at</font> <a href=\"http://www.mysite.com/free-email-alerts/\">http://www.mysite.com/free-email-alerts/</a><font color='#FF0000'> your property market information provider.</font></strong>";
while ($row1 = mysql_fetch_array($result1))
{
$id = $row1["id"];
$keyword = $row1["keyword"];
$sale = $row1["sale_rent"];
$agents = $row1["e_num"];
$email = $row1["email"];
$cc_email = $row1["cc_email"];
$expiry = $row1["expiry"];
$query2= ("SELECT * FROM condo WHERE (location LIKE '%{$row1['keyword']}%' AND sale_rent LIKE '%{$row1['sale_rent']}%' AND e_num LIKE '{$row1['e_num']}') AND (date >= '$sendate') AND TRIM(IFNULL(`phone_1`,'')) <> '' ORDER BY sale_rent, location");
$result2=mysql_query($query2);
$num2=mysql_num_rows($result2);
$new_rows = "";
while ($row2 = mysql_fetch_array($result2))
{
$new_rows .= "<tr bgcolor='white' align='left'>";
$new_rows .= "<td align=center><font face=arial size=1.5>".$row2["date"]."</td>";
$new_rows .= "<td align=left><font face=arial size=1.5>".$row2["location"]."</td>";
$new_rows .= "<td align=center><font face=arial size=1.5>".$row2["property_type"]."</td>";
$new_rows .= "<td align=center><font face=arial size=1.5>".$row2["tenure"]."</td>";
$new_rows .= "<td align=left><font face=arial size=1.5>".$row2["status"]."</td>";
$new_rows .= "<td align=center><font face=arial size=1.5>".$row2["sale_rent"]."</td>";
$new_rows .= "<td align=center><font face=arial size=1.5>".$row2["size"]."</td>";
$new_rows .= "<td align=left><font face=arial size=1.5>".$row2["price"]."</td>";
$new_rows .= "<td align=center><font face=arial size=1.5>".$row2["price_psf"]."</td>";
$new_rows .= "<td align=center><font face=arial size=1.5>".$row2["rooms"]."</td>";
$new_rows .= "<td align=left><font face=arial size=1.5>".$row2["baths"]."</td>";
$new_rows .= "<td align=center><font face=arial size=1.5>".$row2["renovations"]."</td>";
$new_rows .= "<td align=center><font face=arial size=1.5>".$row2["furnishings"]."</td>";
$new_rows .= "<td align=left><font face=arial size=1.5>".$row2["phone_1"]."</td>";
$new_rows .= "<td align=center><font face=arial size=1.5>".$row2["advertiser"]."</td>";
$new_rows .= "<td align=center><font face=arial size=1.5>".$row2["e_num"]."</td>";
$new_rows .= "</tr>";
}
if (!empty($new_rows))
{
$message = str_replace("[new_rows]",$new_rows,$message_tmp);
$message .= "<br><br><font color='#FF0000'><strong>Your notification will expire on</font> $expiry</strong>";
$message .= "<br><br><font color='#FF0000'><strong>Your requested keyword is </font>$keyword</strong>";
echo $subject;
echo '<br>';
echo $message;
echo '<br>';
echo $headers;
echo '<br><br>';
}
}
?>