英文:
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.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
如果您注意到在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.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.
答案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'
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论