(sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in) is there a syntax error that I’m missing?

I need to get a report based on a datetime column which is actually the average of another column for each hour of the day , so i wrote a query and put it in a for loop so i can change the hour variable for comparison, and after that i wanna echo it so i can use it to create a line chart !

i have something similar in the same page and it’s working fine but this one ain’t .

I’m using PHP 7 with sql Server 2016

here’s the code :

for ($i = 1; $i <= $date_hour ; $i++)
{
    $j = $i-1;
    $query = Query("SELECT AVG(sp_efficiency_r) FROM sp_data_records WHERE sp_detail_id_r =$key_id and sp_sent_time_r >= '2018- $date_month - $date_day   $j  :00:00.000' and sp_sent_time_r <= '2018-$date_month-$date_day   $i:00:00.000'");
    $row = sqlsrv_fetch_array($query,SQLSRV_FETCH_NUMERIC);
    echo $row[0].",";
    if ($flag1 = true) {
        $flag1 = false;
        $date_day++;
    }
    elseif ($flag = true) {
        $date_day = 1;
        $flag = false;
    }
}

I used anything i could find on the internet about concatenating, things like ‘{$data}‘ and . $data . are showing the same error to me .

I’m a rookie in this so i’d be glad if you help me with as much details as possible . TNX

Answer

Error "sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given" means that sqlsrv_query() returns false instead of statement resource. In your case I think that the reason is wrong SQL statement.

The problem might be the date columns. You need to pass string values as dates using unambiguous datetime format, in your case '2010-01-27T15:33:13.343'. I also suppose that $date_month, $date_day and $key_id are defined and are numbers.

So, you can try with this:

...
for ($i = 1; $i <= $date_hour ; $i++) {
    $j = $i-1;
    $sql = 
        "SELECT AVG(sp_efficiency_r) 
        FROM sp_data_records 
        WHERE 
            (sp_detail_id_r = ".$key_id.") AND 
            (sp_sent_time_r >= '".sprintf("2018-%02d-%02dT%02d:00:00.000", $date_month, $date_day, $j)."') AND 
            (sp_sent_time_r <= '".sprintf("2018-%02d-%02dT%02d:00:00.000", $date_month, $date_day, $i)."')
        ";
    $query = Query($sql);
    # Next 2 lines are just for test
    echo "Possible error message: ";
    print_r(sqlsrv_errors(), true);
    $row = sqlsrv_fetch_array($query, SQLSRV_FETCH_NUMERIC);
    echo $row[0].",";
}
...