MYSQL calling a query inside a loop stops the loop

Question

Ok what i want is to go through database and send an email to every email whose database stored time and date is lower then current date and whose sent value is 0. Whats working is it goes through the loop of email and sends the emails however what i want to do is to set sent value in the database from 0 to 1 so that if i ran the program in the future it wont send the emails again to people that i already have send an email. i tried updating the send value to 1 inside my while loop but when i execute my file it stops after just one email, so it doesn't loop through database

<?php
include_once("db.php");
$date = new DateTime();
//echo $date->format('Y-m-d H:i:s')

$query = "SELECT timedate, email, sent, msgid  FROM mailer";
$result = mysql_query($query);
echo "<table>";
while($row = mysql_fetch_array($result)){
       $tot = $row['timedate'];
       $ema = $row['email'];
       $sendflag = $row['sent'];
       $mess = $row['msgid'];

    if(strtotime($tot) > time()) {
    //echo "<tr><td>" .$row['timedate']."</td><td>";
    echo"database dates higher then now dates" . "<br>";
    echo "<tr><td>" .$row['email']."</td><td>" ."<br>";
   echo "<tr><td>" .$row['timedate']."</td><td>" ."<br>";
      echo "<tr><td>" .$row['sent']."</td><td>" ."<br>";
}
else {
    echo"database dates lower then now dates" . "<br>";
    echo "<tr><td>" .$row['email']."</td><td>". "<br>";
       echo "<tr><td>" .$row['timedate']."</td><td>" ."<br>";
          echo "<tr><td>" .$row['sent']."</td><td>" ."<br>";
$subject = "This is subject";
   $message = "This is simple text message.";
   $header = "From:abc@somedomain.com \r\n";
   $to = $row['email'];
   $retval = mail ($to,$subject,$message,$header);
   if( $retval == true )  
   {
      echo "Message sent successfully..." ."<br>";
   }
   else
   {
      echo "Message could not be sent..." ."<br>";
   }

    //$sql = "UPDATE mailer SET sent = 1 WHERE msgid = $mess";
    $query = "UPDATE mailer SET sent = 1 WHERE msgid = $mess";
    $result = mysql_query($query);





}








}
mysql_close();
?>

You can't do another query while going through the recordset of a query. Read all the recordset to an array, then loop on the array, and you can do the second query in the loop. — developerwjk, May 05 '15 at 20:15
Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and consider using [PDO](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, May 05 '15 at 20:21
Original mysql extension is deprecated in PHP version 5.5 and higher. You don't have to use PDO. There is also a mysql improved extension and it is not deprecated yet and I hope it won't be soon. — sajushko, May 05 '15 at 20:49

score 0 · Accepted Answer · answered May 05 '15 at 20:18

0

Rename the $result at the end to $result2 for example. You are overwriting the variable that you are looping.

answered May 05 '15 at 20:18

sajushko

418
4
13

And $query as well. rename it to $query2 – ledesma May 05 '15 at 20:20

MYSQL calling a query inside a loop stops the loop

1 Answers1