SQL question

Adam

Got a script that is to run automatically each day, no interface. Have run into a slight problem though. It takes the data from a sql table, tbl_mail_list, and emails the people in the table an email, based on which email they have retrieved last, as there is a series of emails. So if joe bloggs has received email 2, he will receive email 3 the next time the script runs. It then inserts a new row into the table containing the most recent email number.

That all works fine, but for one thing. When the select statement runs again, it will email every email to joe bloggs, because there is a record of him after receiving email 1, and email 2, and email 3...

Using update isn't an option, so how do I select just the record with the highest email received value for each person? Bearing in mind i need to select the name and email address from the table too. Cheers.

Gosh

I'm assuming your query is based on your last query plus the need to only send the email with the highest value in ml_message, so here's the changes need to your code

<?

[COLOR=Red]$sql="SELECT * FROM tbl_mail_list ORDER by ml_recipient, ml_message DESC";[/COLOR]
            //debug($sql);
$result = mysql_query($sql) or die("Query failed : " . mysql_error());
$num_rows = mysql_num_rows($result);

[COLOR=Red]$prev_recipient = "";[/COLOR]

while($row=mysql_fetch_assoc($result)){
      
   $recipient=$row['ml_recipient'];
   $name=$row['ml_name'];

   $message1= "This is message number 1 and it is intended for $name";
   $message2= "This is message number 2 and it is intended for $name";
   $message3= "This is message number 3 and it is intended for $name";

   [COLOR=Red]if ($prev_recipient != $recipient){
      $prev_recipient = $recipient;[/COLOR]
      $to=$recipient;
      $subject="email";
      $headers = "From: blah@blah.com\n";
      ini_set ("SMTP", "your_SMTP_server");

       if($row['ml_message']==0){

           $body=$message1;
           mail($to, $subject, $body, $headers);

       }



       if($row['ml_message']== 1){

           $body=$message2;      
           mail($to, $subject, $body, $headers);

       }


       if($row['ml_message']== 2){

           $body=$message3;
           mail($to, $subject, $body, $headers);

       }
   }
[COLOR=Red]}[/COLOR]

?>

I've highlighted the changes, basically it still reads all of the records, sorting on recipient email address and message no (descending). In the code it checks the last recipient email to the previous and only sends an email if this is different. Since the second part of the sort is message no descending you'll get them in the order 2, 1, 0 for the same recipient. In which case 1 and 0 are ignored ...

Adam

Thats great, I was trying to work it with some kind of SELECT MAX() statement but couldn't work out how to return more than just one result.

It works great now, but if i run the script until all the people in the table have reached ml_message='3' the insert statement returns an error... the script now looks like this:

[PHP]<?php

include 'functions.php';
include 'header.php';

$sql="SELECT * FROM tbl_mail_list ORDER by ml_recipient, ml_message DESC";
//debug($sql);
$result = mysql_query($sql) or die("Query failed : " . mysql_error());
$num_rows = mysql_num_rows($result);
$prev_recipient = "";
while($row=mysql_fetch_assoc($result)){



$recipient=$row;
$name=$row;


$message1= "This is message number 1 and it is intended for $name";
$message2= "This is message number 2 and it is intended for $name";
$message3= "This is message number 3 and it is intended for $name";

if ($prev_recipient != $recipient){
$prev_recipient = $recipient;
$to=$recipient;
$subject="email";
$headers = "From: blah@blah.com\n";

echo $recipient . " " . $name . " " . $row ;

if($row==0){
$body=$message1;
echo "sending email_1 to " . $to;
//mail($to, $subject, $body, $headers);

}


if($row==1){

$body=$message2;
echo "sending email_2 to " . $to;
//mail($to, $subject, $body, $headers);

}


if($row==2){
$body=$message3;
echo "sending email_3 to " . $to;
//mail($to, $subject, $body, $headers);

}


if($body==$message1) $message_received='1';
if($body==$message2) $message_received='2';
if($body==$message3) $message_received='3';

$date=format(gmdate("d-M-Y"),"yyyy-mm-dd");

$SQL="INSERT INTO tbl_mail_list (ml_date_sent, ml_recipient, ml_message, ml_name) VALUES ($date, \"$recipient\", $message_received, \"$name\")";
debug($SQL);
mysql_query($SQL) or die("Query failed : " . mysql_error().debug($SQL));

}
}
?>[/PHP]

And looking at it now I've just realised it's because $message_received is null if every recipient has received each email... any idea how I might work around that?

Gosh

Either allow that field to have a Null value in the table definition or just before the SQL insert check to see if the value is null and if so skip the insert.

$message_received='';
if($body==$message1) $message_received='1';
if($body==$message2) $message_received='2';
if($body==$message3) $message_received='3';

if ($message_received != "") {
     $SQL="INSERT INTO tbl_mail_list (ml_date_sent, ml_recipient, ml_message, ml_name) VALUES ($date, \"$recipient\", $message_received, \"$name\")";
        debug($SQL);
        mysql_query($SQL) or die("Query failed : " . mysql_error().debug($SQL));
}

Adam

Sex in a jar! Cheers!

bishopLEN

fade_to_grey wrote:

Sex in a jar! Cheers!

Just got a flash back to when Gerry Ryan pulled out the jar on Podge & Rodge. Nasty!!

dob99

Alternatively, you could do

SELECT
   MAX(ml_message) AS last_message, ml_name, ml_recipient
FROM
   tbl_mail_list
GROUP BY
   ml_name, ml_recipient

Then you don't need the while-loop, since you will only get one record per user.

This allows the database to do the filtering, which is, generally, preferable to doing it externally, since it reduces the amount of data passed from the database to the application, which could be on another machine across the network somewhere.