Stage Stage - 23 days ago 9
PHP Question

MSSQL datetime datetime in php

I have a problem where i think exist a couple solutions.

scenario
I have build this intranetpage where internal performance KPI's are visible for the office.
The data is extracted from a MSSQL database and shown on the webpage in combination with PHP, than the data and converted to an array so Google Chart API can create beautiful charts from there.
So far, so good!

Problem
I have a chart where the created tickets from the last 7 days are shown (in reverse order). The number of tickets are created by a MSSQL count, one number for each day (today - 7) in total.

But on some days, no tickets are created like a saterday or sunday. Thats where things go wrong.

Today is Thursday and it shows:

Day Amount
Thursday 25
Friday 10
Monday 30 (etc)
Tuesday 15 (day before)
Wednesday 20 (yesterday)
Thursday 50 (today)


instead of

Saturday 0
Sunday 0
Monday 30
Tuesday 15
Wednesday 20
Thursday 50


The problem is that there are no records (tickets) with a create-date on saturday or sunday, so MSSQL cant count them.

PHP calculates the dates (also today -7) that are shown beneath the axis in my chart, but PHP does include Saterday and Sunday, so the axis titles are not representive for the data that the axis shows.

Possible solution
One solution is that somehow MSSQL shows Saterday and Sunday in the result table. But i dont think this is possible, because the records simply dont exist.

Other solution
Retrieve the datetime used in the MSSQL query and parse them to my intranetpage so the google charts can display those dates instead of the PHP created dates. But than i get a "Convert datetime class to string" error.

Because the datetime in MSSQL is a datetime type and not a varchar type.
After trying a lot of things and searching even more on google and stackoverflow, i can succesfull convert a datetime to varchar. But than the part where i take todays date and go back 7 days, doesnt work anymore.

My PHP (and query) look as follow:




$sql = "

SELECT top 7
CAST(bb_casestartingtime AS DATE) AS DATE, COUNT(*) as TotalRows
FROM
CRM_MSCRM.dbo.FilteredIncident
WHERE
bb_casestartingtime > (CONVERT(date, GETDATE() - 7))
GROUP BY CAST(bb_casestartingtime AS DATE)
Order by date

";





$stmt = sqlsrv_query( $conn, $sql );
if( $stmt === false) {die( print_r( sqlsrv_errors(), true) );}

$x=5;
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) )

{ **$dateValue = date("Y-m-d", strtotime(" -$x day"));**
echo "[',".$row[**date**].$row['TotalRows'].",],";
$x--;
}


I broke the two pieces of code in half, but actually they are

The table looks like this (at least, the interesting part):

case id bb_casestartingtime
01 2016-11-10 15:30:45.000
02 2016-11-10 16:22:10.000
03 2016-11-09 15:02:15.000


I count the amount of occurrences by date (bb_casestartingtime) as a total.
So today i have
today 2 records
yesterday 1 record

Acutal Question
can somebody help me to extraced the date-time which is used in the query?

I only use one table called: FilteredIncident
Is there a way to convert the datetime bb_casestartingtime to a varchar (so PHP can extract it) but still count the amount of occurrences per day?

If any information is needed, please ask

Thanks in advance!

(I rewrote my entire question, because i wrote the initial post in a rush)

Answer

I solved the problem with a work arround, as i didnt need to see the days where there are 0 tickets, i didnt have to build an temporary table and do a left join.

The solution i came with is to use the datetime fields (bb_casestartingtime) and use those dates. Now the chart axis title matches the chart bar.

solution

Convert the $variable where the datetime field is put to, to a datetime field variable.

 $rows[] = "['".$row[1]->format('D j-m')."',".$row[0]."],";
                  ^               ^                ^
            The datetime row | The format | The amount of tickets