handling mysqli returned null value

Question

I am writing a small web application as a report tool. I have a loop written in to get a value for each day, but I had a problem with our server on one of the days, and data is missing. When I run the query in mySQL, I get NULL returned. I am trying to write some error handling, but it just isn't quite working correctly. What am I doing wrong?

$ath_q="SELECT SUM(performances*(TIME_TO_SEC(duration)/3600)) AS 'ATH'
from streamstats.livestats INNER JOIN streamstats.library USING(artist,song)where ts BETWEEN ? AND ?";

$i=0;

$dailyATH=[];

echo count($dateRange)."</br>";

while ($i < count($dateRange)){
    //echo $i."</br>";
    $athStart=$dateRange[$i]." ".$dayStart;
    $athEnd=$dateRange[$i]." ".$dayEnd;
    echo $athStart."-".$athEnd."</br>";
    $athStmt=$mysqli->prepare($ath_q);
    $athStmt->bind_param('ss',$athStart,$athEnd);
    $athStmt->execute();
    $athResult=$athStmt->get_result();

    if ($dATH=$athResult->fetch_column()){
        if ($dATH===null){
            echo "no value";
        }
        echo $athStart."-".$athEnd."=".$dATH."</br>";
        //array_push($dailyATH,$dATH);
    }
    $i++;
}

current result looks like this.

2023-05-01 00:00:00-2023-05-01 23:59:59=1373.2289
2023-05-02 00:00:00-2023-05-02 23:59:59
2023-05-02 00:00:00-2023-05-02 23:59:59=2561.0925
2023-05-03 00:00:00-2023-05-03 23:59:59
2023-05-03 00:00:00-2023-05-03 23:59:59=6065.2980
2023-05-04 00:00:00-2023-05-04 23:59:59
2023-05-05 00:00:00-2023-05-05 23:59:59
2023-05-05 00:00:00-2023-05-05 23:59:59=3681.4305
2023-05-06 00:00:00-2023-05-06 23:59:59
2023-05-06 00:00:00-2023-05-06 23:59:59=8739.7841

If you notice on 2023-05-04, there is no data, and it skips to the next one. I am trying to catch the null and set it to 0, but it seems like I can't even catch the null data. Any help would be greatly appreciated.

What do you mean catch the null value? Which value should be null and why? — Dharman, Jun 01 '23 at 22:39
`if ($dATH=$athResult->fetch_column()){` already checks if the value is null — Dharman, Jun 01 '23 at 22:40
@Dharman, thank you. So I have my error handling in the wrong spot is what it sounds like. How should I handle this? — Geerdes, Jun 01 '23 at 22:46
Note that a query returning null is not an error. So you shouldn't call it error handling. — Your Common Sense, Jun 02 '23 at 05:49

score 0 · Accepted Answer · answered Jun 01 '23 at 22:53

0

Use the null coalescing operator instead of if statements.

    $dATH = $athResult->fetch_column() ?? 0;
    echo $athStart."-".$athEnd."=".$dATH."</br>";
    //array_push($dailyATH,$dATH);

You could also do it in the SQL:

SELECT IFNULL(SUM(performances*(TIME_TO_SEC(duration)/3600)), 0) AS 'ATH'

answered Jun 01 '23 at 22:53

Barmar

741,623
53
500
612

Thank you Barmar. I added the else statement to the if statement and it was good. – Geerdes Jun 01 '23 at 22:59
I got rid of the `if` statement since you said you want to treat it as 0. So there's no place to put `else`. – Barmar Jun 01 '23 at 23:00
I wrote a 0 into the array I was storing my data into with the else statement. – Geerdes Jun 01 '23 at 23:10

handling mysqli returned null value

1 Answers1