数组循环假设没有数据? [英] Array Loop Assuming Nil Data?

问题描述

我已经使用时间戳在每个月的计数循环中创建了一个自动填充数组，例如2016-06-11 00:00:00-循环从中获取的数据库布局是:

I've created an auto-populating array in a counted loop for each month using timestamps, e.g 2016-06-11 00:00:00 - the database layout that the loop is fetching from is:

TimeRecord          | Views 
2016-06-11 00:00:00 | 22
2016-08-11 00:00:00 | 44

现在，上面的示例跳过了07(七月)月，该月没有数据，因此当循环遍历该月时，它应该返回null或0，但返回先前已知的数字(在这种情况下为22).

Now, the above example skips the 07 (July) month, there is NO DATA for this month, and so when the loop goes through this month, it should return either null or 0, but it returns the previously known number (which is 22 in this case).

对于2016-09年，由于没有指定数据，因此该数组将为"44".

For 2016-09, there is no specified data, so the array will give "44".

循环和从数据库获取以生成数组的代码如下:

The code for the loop and fetching from the database to generate the array is like so:

$startMonth = date("y-m-d");
$endMonth = date("y-m-d");

while ($Month <= 12){

$ViewsThisMonth = mysqli_fetch_object(mysqli_query($db, "SELECT    SUM(Views) AS NumberFinalViews, EXTRACT(YEAR_MONTH FROM TimeRecord) AS YearMonth FROM BlogViews WHERE TimeRecord > '$startMonth' AND TimeRecord < '$endMonth' GROUP BY YearMonth"));

if (is_null($ViewsThisMonth->NumberFinalViews)){
$ViewsThisMonth->NumberFinalViews = 0;
}

$ArrayTimeStamp[] = $ViewsThisMonth->NumberFinalViews;
$Month = $Month + 1;
$startMonth = date("y-m-d",strtotime("-$Month month"));
}

返回的JSON编码数组示例为:

An example returned JSON Encoded Array is:

[0,"29392","333","4000","4000","99","99","99","99","99","99","99"]

可以在此处找到导致上述数组的数据库值的屏幕快照.如您所见，由于第5个月没有记录，因此4000重复了两次，导致它使用了第4个月的数据.由于第6-12个月没有值，因此还会重复执行99，因此它使用第6个月的值而不是返回0.

A screenshot of the database values that caused the above array can be found here. As you can see, 4000 repeats itself twice as there is no record for the 5th month, causing it to use the 4th month's data. 99 is also repeated, as there are no values for the 6th-12th month, therefore it uses the 6th month's value instead of returning a 0.

如果循环进行时该月没有TimeRecord，我希望它返回0，而不是假定视图号与上个月相同.

If there is no TimeRecord for that month when the loop goes through, I want it to return 0, not assume that the view number is the same as the previous month.

推荐答案

问题是您多次执行相同的查询，尽管日期有所更改.该查询旨在提供几个月的结果，但您只能使用结果行之一.由于没有order by子句，可能是您以意外的顺序获得了行.同样，您在循环中更改开始日期的方式也很奇怪:它在时间上向后移动.

The problem is that you perform the same query multiple times, albeit with a changing date. The query is designed to give a result for several months, yet you only use one of the result rows. As there is no order by clause, it could be that you get the rows in an unexpected order. Also the way you change the start date in the loop is strange: it moves backwards in time.

最好只执行一次查询，然后将结果存储到以月-年为键的准备好的数组中.

It would be better to execute the query only once, and then store the results into a prepared array that is keyed by month-year.

请注意，您可以使用COALESCE在SQL查询本身中执行空检查.

Note that you can perform the null check in the SQL query itself with COALESCE.

代码变成这样:

$startYearMonth = date('Ym', strtotime("2015-01-01")); // set this date as you wish
$endYearMonth = date('Ym', strtotime("now"));

// Prepare result array: one entry per month, with all values set to 0
for ($yearMonth = $startYearMonth; $yearMonth <= $endYearMonth; 
                                   $yearMonth += ($yearMonth % 100 == 12 ? 89 : 1)) {
    $months[$yearMonth] = 0;
}

// improved query
$result = mysqli_query($db, "
    SELECT   COALESCE(SUM(Views), 0) AS NumberFinalViews, 
             EXTRACT(YEAR_MONTH FROM TimeRecord) AS YearMonth 
    FROM     BlogViews 
    WHERE    EXTRACT(YEAR_MONTH FROM TimeRecord) 
                   BETWEEN '$startYearMonth' AND '$endYearMonth'
    GROUP BY YearMonth");

// Featch and store each result in their proper year-month slot
while ($ViewsThisMonth = mysqli_fetch_object($result)) {
    $months[$ViewsThisMonth->YearMonth] = $ViewsThisMonth->NumberFinalViews;
}

// output the result
print_r($months);

这篇关于数组循环假设没有数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！