Loop a query

[Mr Chris]

Hello,

 

I was looking for some help.  I have a table:

 

 

and I wish to use that table to query another table.

 

and then send an email out with the results of the query:

 

<?php 

// Start the connection to the database 

// End the connection to the database   

$current_url = $_SERVER['SERVER_NAME']; 

$messagebody = "** This is an automated message - please do not reply **:\n\n Please find homes that match your criterian\n"; 

$query_one = "SELECT *
FROM property_weekly AS p, properties_wanted as pw
WHERE p.address_4=pw.county
AND pw.property_id='1'
AND p.town=pw.town
AND p.bedrooms=pw.bedrooms
ORDER BY pw.price 
DESC LIMIT 6"; 
$result = mysql_query($query_one)  OR die(mysql_error()); 
while ($row = mysql_fetch_array($result)) { 
  $messagebody .= stripslashes($row['address_1']) . "\n" . "http://$current_url/live/stories/story.php?prop_id=" . $row['prop_id'] . "\n\n"; 
} 

$query_two = "SELECT your_name, your_email FROM `properties_wanted` WHERE property_id = '1'"; 
$result = mysql_query($query_two)  OR die(mysql_error()); 

$number_of_results = mysql_num_rows($result); 

If (mysql_num_rows($result) > 0) { 
  echo "We have send the email out to <b>".$number_of_results."</b> registered users"; 
} 
else { 
  $row = mysql_fetch_array( $result ); 
  echo "Nobody has registered for email alerts"; 
} 

while ($row = mysql_fetch_array($result)) { 

$lines = "-------------------------------------------------------------------------------------------------------------\n"; 
$your_name = "Dear ". $row['your_name'] ."\n\n"; 
$unsubscribe = "To unsubscribe from this email alert please visit the link below:\n"; 
$messagelink = "http://$current_url/live/email_alerts/unsubscribe_email.php?email=". $row['your_email'] ."\n\n"; 

$message = $your_name.$messagebody.$lines.$unsubscribe.$messagelink; 

  mail($row['your_email'], "Property Details", $message, "From: [email protected]"); 
} 

?>  

 

Now in BOTH of my queries in the code you will see the condition Where property_id=1 and what I want to do is loop through the table shown for each row and modify the query.  Ie so the next condition is the next property_id in the database ie:property_id=2 and keep on looping depending on the number of records. 

 

Can anyone help?

 

Thanks

[wildteen88]

If you remove AND pw.property_id='1' from the query it should loop through all rows.

[Mr Chris]

Thanks, yes it does, but not how I want it to.

 

It then just emails each user in the table shown the full set of results for the query:

 

$query_one = "SELECT *
FROM property_weekly AS p, properties_wanted as pw
WHERE p.address_4=pw.county
AND p.town=pw.town
AND p.bedrooms=pw.bedrooms
ORDER BY pw.price 
DESC LIMIT 6";

 

I've tested it and I know if each property_id is changed in each query to the next row in the database automatically then the code I have written works great. It's just getting it to do that.

 

Any other suggestions