MySql / PHP Alert system not reporting only one seemingly random piece of data

Question

I have had a usage reporting system in place for sometime to notify users that they are using too much data. Suddenly random lines stopped reporting. I assumed there was just an error in the data going in, but I have thoroughly check it and can find no reason why this would be occurring.

Here is what I have set-up:

Alert system:

#!/usr/bin/php -q
<?php

$db_host = "localhost"; 
$db_username = "XXX"; 
$db_pass = "XXX"; 
$db_name = "XXX"; 
mysql_connect("$db_host","$db_username","$db_pass") or die(mysql_error()); 
mysql_select_db("$db_name") or die ("no database");

$sql =  "SELECT date,phonenumber,email, dataplan AS currentplan, SUM(datamb) AS value_sum FROM maindata2 GROUP BY phonenumber, dataplan";


$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)){
if ($result) {
  $row = mysql_fetch_assoc($result);
  $plan = $row['currentplan'] ;
  $date = $row['date'] ;
  $inventory = ROUND ($row["value_sum"],2) ;
  $recipient = $row['email'];
  $line = $row['phonenumber'];
  if ($inventory > (.75 * $plan)) {
    $msg = "message";
    mail($recipient,, "Alert from ", $msg);
  }
}
else {
  $msg = "An error occurred: " . mysql_error();
  mail($recipient, "Alert from ", $msg, $headers);

}
}




?>

The Data I am using is the following:

"5/28/2014","xxx-xxx-0904","20","email@email.com, email@email.com, email@email.com","461.80","email@email.com"
"5/28/2014","xxx-xxx-0905","20","email@email.com, email@email.com, email@email.com","418.80","email@email.com"
"5/28/2014","xxx-xxx-0906","20","email@email.com, email@email.com, email@email.com","461.80","email@email.com"

The middle line does not report, all others do. Any thoughts would be great, I have tried changing the group by to every possible combination, but I can not get that middle line to report.

if ($inventory > (.75 * $plan) <--- Is it meeting this requirement? It's hard to tell with no sql data. — Joe Swindell, May 29 '14 at 15:46
Yes, the plan is the 20 value, and the usage is the 400+ value, so it is well over the limit and should be reporting — user3688183, May 29 '14 at 15:49
try: while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) Also ,your if($result) should be above your while. — Joe Swindell, May 29 '14 at 15:55

src · Accepted Answer · 2014-05-29T16:00:53.910

You're calling mysql_fetch_assoc twice before cheking the values. So you're only processing every second row. And you have to test if $result is not false before calling mysql_fetch_assoc. Also the mysql extension is deprecated since PHP 5.5, you should rewrite your code to mysqli or PDO.

if ($result) {
    while ($row = mysql_fetch_assoc($result)){
        $plan = $row['currentplan'] ;
        $date = $row['date'] ;
        $inventory = ROUND ($row["value_sum"],2) ;
        $recipient = $row['email'];
        $line = $row['phonenumber'];
        if ($inventory > (.75 * $plan)) {
            $msg = "message";
            mail($recipient,, "Alert from ", $msg);
        }
    }
} else {
    $msg = "An error occurred: " . mysql_error();
}

Notice here src has the if ($result) at the top. If $result is false you don't want to try to do anything because an error or no results were returned by mysql. Good answer. — Joe Swindell, May 29 '14 at 15:57

MySql / PHP Alert system not reporting only one seemingly random piece of data

1 Answers1