英文:
handling mysqli returned null value
问题
我正在编写一个小型的网络应用程序作为报告工具。我已经编写了一个循环来获取每天的值，但在某一天，我们的服务器出了问题，数据丢失了。当我在MySQL中运行查询时，返回了NULL。我正在尝试编写一些错误处理，但它似乎无法正常工作。我做错了什么？
$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";$dATH = 0; // Set null value to 0}echo $athStart."-".$athEnd."=".$dATH."<br>";//array_push($dailyATH,$dATH);}$i++;}
当前结果看起来像这样。
2023-05-01 00:00:00-2023-05-01 23:59:59=1373.22892023-05-02 00:00:00-2023-05-02 23:59:592023-05-02 00:00:00-2023-05-02 23:59:59=2561.09252023-05-03 00:00:00-2023-05-03 23:59:592023-05-03 00:00:00-2023-05-03 23:59:59=6065.29802023-05-04 00:00:00-2023-05-04 23:59:592023-05-05 00:00:00-2023-05-05 23:59:592023-05-05 00:00:00-2023-05-05 23:59:59=3681.43052023-05-06 00:00:00-2023-05-06 23:59:592023-05-06 00:00:00-2023-05-06 23:59:59=8739.7841
如果您注意到在2023-05-04上，没有数据，并且跳到了下一个日期。我正在尝试捕获null并将其设置为0，但似乎我甚至不能捕获null数据。任何帮助将不胜感激。
英文:
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.22892023-05-02 00:00:00-2023-05-02 23:59:592023-05-02 00:00:00-2023-05-02 23:59:59=2561.09252023-05-03 00:00:00-2023-05-03 23:59:592023-05-03 00:00:00-2023-05-03 23:59:59=6065.29802023-05-04 00:00:00-2023-05-04 23:59:592023-05-05 00:00:00-2023-05-05 23:59:592023-05-05 00:00:00-2023-05-05 23:59:59=3681.43052023-05-06 00:00:00-2023-05-06 23:59:592023-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.
答案1
得分: 0
使用空值合并运算符而不是if语句。
$dATH = $athResult->fetch_column() ?? 0;echo $athStart . "-" . $athEnd . "=" . $dATH . "</br>";//array_push($dailyATH,$dATH);
你也可以在SQL中这样做：
SELECT IFNULL(SUM(performances*(TIME_TO_SEC(duration)/3600)), 0) AS 'ATH'
英文:
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'
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论