[RESOLVED] Send Query Results via Email

[transfield]

Hello,
I've written a code that queries one table based on the values found in another table. It then sends the query results via email. The code is working fine though I wrote it in a crude manner based on my limited knowledge. The code is displayed below.

My issue now is, I want the query results of the first row to be sent to the email address on the first row. Then followed by the query results on the second row to be sent to the email address on the second row until there are no more rows to loop through. Just in case you did not understand what I'm trying to achieve, please look at the screen shot attached. I trust that will give you a clearer picture.

Please show me how to modify my code. Thanks a lot.

<?php
//connection to MySql database

$query1=("SELECT * FROM developer_log"); 

$result1=mysql_query($query1); 
$num1=mysql_num_rows($result1);

$headers  = 'MIME-Version: 1.0' . "\r\n"; 
$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n"; 
$headers .= 'From: [e-mail]' . "\r\n" . 
   'Reply-To: [e-mail]' . "\r\n" . 
   'X-Mailer: PHP/' . phpversion();
$subject = "Your mail from webmaster";
$messageBody .= "<h3>Offices</h3>";
$messageBody .= "<table bgcolor='black' cellspacing='1' width='100%'><tr bgcolor='white' align='center'>";
$messageBody .= "<td><b>Name of Developer</b></td>";
$messageBody .= "<td><b>Name of Project</b></td>";
$messageBody .= "<td><b>Type of Property</b></td>";
$messageBody .= "<td><b>Levels</b></td>";
$messageBody .= "<td><b>Number of Units</b></td>";
$messageBody .= "</tr>"; 

while ($row1 = mysql_fetch_array($result1))
{ 
$id = $row1["id"];
$company = $row1["company"];
$project = $row1["project"];
$property = $row1["property"];
$email = $row1["email"];
$cc_email = $row1["cc_email"];

$query2= "SELECT * FROM developer WHERE name LIKE '%{$row1['company']}%' OR development LIKE '%{$row1['project']}%' OR type LIKE '%{$row1['property']}%'"; 
$result2=mysql_query($query2);
$num2=mysql_num_rows($result2);

while ($row2 = mysql_fetch_array($result2)) 
{ 
$messageBody .= "<tr bgcolor='white' align='center'>";
$messageBody .= "<td>".$row2["name"]."</td>";
$messageBody .= "<td>".$row2["development"]."</td>";
$messageBody .= "<td>".$row2["type"]."</td>";
$messageBody .= "<td>".$row2["levels"]."</td>";
$messageBody .= "<td>".$row2["quantity"]."</td>";
$messageBody .= "</tr>";
}
mail("$email", "$subject", "$messageBody", "$headers", "[e-mail]");	
} 
$messageBody .= "</table>";

?>

[bogu]

$query1=("SELECT * FROM developer_log ORDER BY Id ASC");

ORDER BY ...

[transfield]

Thanks for your reply, bogu. Sorry, I did not explain myself clearly. Referring back to my screen shot, what's currently happening is:-

The 1st email goes out to [e-mail] consists of the search results from the keywords pakadiri, anything & something.
The 2nd email goes out to [e-mail] consists of the search results from the keywords pakadiri, anything, something, farlim, nothing & everything.
The 3rd email goes out to [e-mail] consists of the search results from the keywords pakadiri, anything, something, farlim, nothing, everything, europlus, projrect & property.

I don't want the above to happen. What I want is below:-

The 1st email goes out to [e-mail] consists of the search results from the keywords pakadiri, anything & something.
The 2nd email goes out to [e-mail] consists of the search results from the keywords farlim, nothing & everything.
The 3rd email goes out to [e-mail] consists of the search results from the keywords europlus, projrect & property.

I suspect that I need to loop the code in a different way but I don't know how.

Please guide me. Thanks.

[bogu]

K, I understand now ....

<?php 
//connection to MySql database 

$query1=("SELECT * FROM developer_log"); 

$result1=mysql_query($query1); 
$num1=mysql_num_rows($result1); 

$headers  = 'MIME-Version: 1.0' . "\r\n"; 
$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n"; 
$headers .= 'From: [e-mail]' . "\r\n" . 
   'Reply-To: [e-mail]' . "\r\n" . 
   'X-Mailer: PHP/' . phpversion(); 
$subject = "Your mail from webmaster"; 
$message_tmp  = "<h3>Offices</h3>"; 
$message_tmp .= "<table bgcolor='black' cellspacing='1' width='100%'><tr bgcolor='white' align='center'>"; 
$message_tmp .= "<td><b>Name of Developer</b></td>"; 
$message_tmp .= "<td><b>Name of Project</b></td>"; 
$message_tmp .= "<td><b>Type of Property</b></td>"; 
$message_tmp .= "<td><b>Levels</b></td>"; 
$message_tmp .= "<td><b>Number of Units</b></td>"; 
$message_tmp .= "</tr>"; 
$message_tmp .= "[new_rows]"; 
$message_tmp .= "</table>"; 

while ($row1 = mysql_fetch_array($result1)) 
{ 

$id = $row1["id"]; 
$company = $row1["company"]; 
$project = $row1["project"]; 
$property = $row1["property"]; 
$email = $row1["email"]; 
$cc_email = $row1["cc_email"]; 

$query2= "SELECT * FROM developer WHERE name LIKE '%{$row1['company']}%' OR development LIKE '%{$row1['project']}%' OR type LIKE '%{$row1['property']}%'"; 
$result2=mysql_query($query2); 
$num2=mysql_num_rows($result2); 
$new_rows = "";
while ($row2 = mysql_fetch_array($result2)) 
{ 
    $new_rows .= "<tr bgcolor='white' align='center'>"; 
    $new_rows .= "<td>".$row2["name"]."</td>"; 
    $new_rows .= "<td>".$row2["development"]."</td>"; 
    $new_rows .= "<td>".$row2["type"]."</td>"; 
    $new_rows .= "<td>".$row2["levels"]."</td>"; 
    $new_rows .= "<td>".$row2["quantity"]."</td>"; 
    $new_rows .= "</tr>"; 
}
if (!empty($new_rows)) {
	$message = str_replace("[new_rows]",$new_rows,$message_tmp);
	mail($email, $subject, $message, $headers, "[e-mail]");     
}
} 
?>

I didnt check the code I modify for u but u'll get my ideea ...

[transfield]

Thank you very much for your guidance, bogu. Yes, your edited code works exactly how I want it to work. I now have to study the logic behind your code.

Thank you once again & have a good day.