Query a MySql Table Then Use The Results to Query Another Table

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.

    $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"); 
    $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>
    $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");
    $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>';

You can, but it would make your php code much uglier because you would then need to check each of the “header” records to see if they are the same or different.

In this case, the KISS principle would be in effect, and the simplest approach would be the best.

the answer is yes, you can combine the two queries, and you will gain some execution efficiency

but this question belongs in the php forum

@transfield are you aware that the old mysql_* extension was deprecated in version 5.5 of PHP and is being removed from version 7 of PHP? You need to be migrating over to either the mysqli_* extension or to PDO

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.