0

I am making internal dashboard with a table for top selling products. I'm using google charts table for it and I want to add a filter to display products only from this to that date. I'm trying to implement DateRange Filter control from the Google Charts Docu but I'm stuck for some time now. I get an error "Column 6 is not a date", which is understandable because it's a string. I used STR_TO_DATE in my sql query:

STR_TO_DATE(created_at, '%Y-%m-%d') AS dato

In the sql result the field seems to be of type date but when I make it into array, it's of type string again.

foreach ($dataTable as $d) {
$infoTable[] = array((int)$d['productid'], $d['product_name'], $d['brand'], $d['supplier'], (int)$d['store'], (int)$d['in stock'], $d['dato'], $d['tid'], (int)$d['quantity']);
}

How can I implement such filter? It doesn't have to be even DateRange from google chars, any suggestions how to add date filter to the table is welcome. EDIT: my javascript code for table:

function drawDashboard(tableData) {

            var data = new google.visualization.arrayToDataTable(tableData);

            var dashboard = new google.visualization.Dashboard(
            document.getElementById('dashboard_div'));

            var stringFilter = new google.visualization.ControlWrapper({
                controlType: 'StringFilter',
                containerId: 'string_filter_div',
                options: {
                    filterColumnIndex: 6
                }
            });  
            // var dateRange = new google.visualization.ControlWrapper({
            //     controlType: 'DateRangeFilter',
            //     containerId: 'daterange_div',
            //     options: {
            //         filterColumnIndex: 6
            //     }
            // });  
            

            var table = new google.visualization.ChartWrapper({
                chartType: 'Table',
                containerId: 'table_div1',
                options: {
                showRowNumber: true,
                width: '90%',
                page: 'enable',
                pageSize: 15
                }
            });

            dashboard.bind(stringFilter, table);
            dashboard.draw(data);

        }
var tableData = <?php echo json_encode($infoTable) ?>;
google.charts.setOnLoadCallback(function() {
            drawDashboard(tableData);
        });


<div id="dashboard_div">
        <div id="string_filter_div"></div>
        <div id="table_div1"></div>  
        <div id="daterange_div"></div>
    </div>
iivo
  • 47
  • 4
  • According to Google documentation about dates (https://developers.google.com/chart/interactive/docs/datesandtimes?hl=en) your date string format seems correct (ISO 8601 --> `YYYY-MM-DD`) but you might need to convert it to a JavaScript `Date()` object. For the filters, the documentation https://developers.google.com/chart/interactive/docs/gallery/controls?hl=en#create_controls shows a nice example. I don't know how your HTML and JS code looks like but I assume you are on the way to a nice result :-) – Patrick Janser Aug 23 '21 at 08:56
  • Thank you for the anwer. I did try to make it into an object but I tried in my php loop and the table wasn't displaying any date values. I'll give it a shot with the JS. – iivo Aug 23 '21 at 09:08
  • Are you generating the JS code for the data with the PHP code? If yes, you could also extact the year, month and day or a timestamp from the SQL query in order to generate the JS string to create the date. Just be carefull that the month is indexed from 0 and not 1! See doc here https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date#several_ways_to_create_a_date_object With Unix timestamp, you need to convert to milliseconds like explained here https://stackoverflow.com/questions/847185/convert-a-unix-timestamp-to-time-in-javascript – Patrick Janser Aug 23 '21 at 09:17
  • Yes I do. Updated the post with the Js code. – iivo Aug 23 '21 at 10:01
  • For your date problem, I would replace your `$d['dato']` value by a PHP DateTime object. This could be quite easily done by calling the constructor with `new DateTime($d['dato']);`. I think the `json_encode()` should be able to convert it to a JS Date object. If it doesn't work then you could add some JS code after the variable creation to loop over all the data and replace the `YYYY-MM-DD` by a Date object: `for (var i = 0; i < tableData.length; i++) { tableData[i][6] = new Date(tableData[i][6]); }`. – Patrick Janser Aug 23 '21 at 13:46
  • check these answers --> [Line chart from json response in html / javascript / php](https://stackoverflow.com/a/46564280/5090771) & [How to load JSON file with date to Google Charts](https://stackoverflow.com/a/58643199/5090771) – WhiteHat Aug 24 '21 at 16:56

0 Answers0