Barand

I gave you the code for using your query with Google charts two days ago http://forums.phpfreaks.com/topic/297975-php-encode-to-json-help-with-morrisjs-please/?do=findComment&comment=1520002

http://forums.phpfreaks.com/topic/298020-expects-parameter-1-to-be-mysqli-result/?do=findComment&comment=1520121

That must be the most frequently asked question in the forums. I am surprised that the search you made before posting did not find any results

$loadSession will contain either a valid result object or, if the query failed, the boolean value "false" Your query failed - check the value given by $con->error

You probably get 0000-00-00 because 3/15/2014 is m/d/y and you are trying to convert from d/m/y, so getting invalid dates. Also, you left the "/"s out of your formatting string

You store data for efficiency, not for their appearance to the user. You are confusing relational database tables with spreadsheets. You have your code in between the db and the user, so you can present it any way you want, totally independent of the way it is stored. Use relational joins to get data from several tables with a single query.

I'd suggest this schema +------------+ +-------------+ +-------------+ | category | | form | | user | +------------+ +-------------+ +-------------+ +----| catid (PK) | +--| formid (PK) | +---| userid (PK) | | | catname | | | year | | | name | | +----+-------+ | | quarter | | +-------------+ | | +-------------+ | | | | | | | | | | | +----------+ | +--------------+ | | | question | | | score | | | +----------+ | +--------------+ | | | qid (PK) |-------------+ | | scoreid (PK) | | | | qtext | | +-------------------<| formid | | +---<| catid | | | userid |>--+ | points | +--------------------------<| qid | +----------+ | points | +--------------+

Snap!

A principle of database design is do not store derived data, or the same data in more than one location. You can find an items location by querying the eqmove table, so why store it again in the other table?

In your other thread you asked for which is what the query is doing. If you want total monthly sales for the current year, then $query = "SELECT SUM(cust_order_total) AS label , MONTH(due_date) AS y FROM orders WHERE YEAR(due_date) = YEAR(CURDATE()) GROUP BY MONTH(due_date) ORDER BY y"; $rows = []; $result = mysqli_query($connection,$query); $rows = mysqli_fetch_all($result, MYSQLI_ASSOC); echo json_encode($rows);

It's the first of the month - have you got data for the current month yet? I get [{"label":"22.20","y":"2015-09-11"},{"label":"134.08","y":"2015-09-06"}, {"label":"149.06","y":"2015-09-22"},{"label":"161.96","y":"2015-09-09"}, {"label":"163.54","y":"2015-09-05"},{"label":"189.61","y":"2015-09-16"}, {"label":"207.37","y":"2015-09-04"},{"label":"241.12","y":"2015-09-29"}, {"label":"272.21","y":"2015-09-10"},{"label":"280.70","y":"2015-09-15"}, {"label":"287.92","y":"2015-09-02"},{"label":"315.81","y":"2015-09-21"}, {"label":"399.32","y":"2015-09-25"},{"label":"449.35","y":"2015-09-01"}, {"label":"537.45","y":"2015-09-13"},{"label":"574.64","y":"2015-09-08"}, {"label":"620.90","y":"2015-09-03"},{"label":"644.95","y":"2015-09-23"}, {"label":"650.72","y":"2015-09-26"},{"label":"744.04","y":"2015-09-07"}, {"label":"804.14","y":"2015-09-28"},{"label":"805.65","y":"2015-09-18"}, {"label":"860.45","y":"2015-09-20"},{"label":"869.85","y":"2015-09-19"}, {"label":"887.80","y":"2015-09-17"},{"label":"909.03","y":"2015-09-12"}, {"label":"910.61","y":"2015-09-24"},{"label":"918.88","y":"2015-09-14"}, {"label":"936.32","y":"2015-09-27"}] If your php version is < 5.4, change $rows = []; to $rows = array();

see my edit to last post

Are you sure you don't want the total as the y value? Anyway, this should give what you are asking for $query = "SELECT SUM(cust_order_total) AS label , due_date AS y FROM orders WHERE YEAR(due_date) = YEAR(CURDATE()) AND MONTH(due_date) = MONTH(CURDATE()) GROUP BY due_date ORDER BY label"; $rows = []; $result = mysqli_query($connection,$query); $rows = mysqli_fetch_all($result, MYSQLI_ASSOC); echo json_encode($rows);

here's one solution $db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE); /************************************************** * Get the dates for headings * and for keys to the arrays * for each name ***************************************************/ $sql = "SELECT DISTINCT date FROM sales ORDER BY date"; $res = $db->query($sql); $heads = []; $newarray = []; while ($row = $res->fetch_row()) { $heads[] = date('n/j/Y', strtotime($row[0])); $newarray[$row[0]] = ''; } /************************************************** * Get the data from the table * and store in arrays * for each name ***************************************************/ $sql = "SELECT name , date , sales FROM sales"; $res = $db->query($sql); $data = []; while (list($name,$date,$sales) = $res->fetch_row()) { if (!isset($data[$name])) { $data[$name] = $newarray; // blank array for name } $data[$name][$date] = $sales; } /************************************************** * Now output the array * ***************************************************/ echo "<table border='1'>\n"; echo "<tr><th>Name</th><th>" . join('</th><th>', $heads) . "</th></tr>\n"; foreach ($data as $name => $salesdata) { echo "<tr><td>$name</td><td>" . join('</td><td>', $salesdata) . "</td></tr>\n"; } echo "</table>\n"; [edit] NOTE: assumes correct data formats +--------+--------+------------+ | name | sales | date | +--------+--------+------------+ | Liya | 200.00 | 2015-01-01 | | Faye | 100.00 | 2015-01-01 | | Faye | 300.00 | 2015-01-02 | | Jerome | 120.00 | 2015-01-01 | | Liya | 130.00 | 2015-01-03 | | Liya | 400.00 | 2015-01-04 | | Jerome | 210.00 | 2015-01-02 | | Ronna | 230.00 | 2015-01-02 | +--------+--------+------------+

If the image on the left is your db table then it needs redesigning. Sales should be DECIMAL type, without the "$" Date should be DATE type, format YYYY-MM-DD Functionally your current values are worthless. All you can do with them is display them. You can't sort or compare the dates and you can't get a total of the sales.

Why two calls to the same query?

Challenge declined One comment I would make is that it would be better to put a timestamp in the notes tables to identify the latest rather than relying on id sequence. (Treat ids purely as unique row identities used for internal relationships. A database should function the same if ids were allocated randomly instead of sequentially)

And ... ?

I hadn't heard of it before now. I had a quick look at the site and it does look impressive. With their zoom option you would only need the annual chart. You could then use the zoom feature to display the last month and last week from that.

I merely pointed out an error in your code, one which you seem to be denying exists

Google charts will accept the d/m/Y format $query = "SELECT SUM(cust_order_total) as daily_gross_sales, date_format(due_date, '%d/%m/%Y') as day FROM orders WHERE YEAR(due_date) = YEAR(CURDATE()) AND MONTH(due_date) = MONTH(CURDATE()) GROUP BY due_date"; $rows = ''; $result = mysqli_query($db,$query); $total_rows = mysqli_num_rows($result); $data = []; if($total_rows > 0) { while (list($s, $d) = mysqli_fetch_row($result)) { $data[] = "['$d',$s]"; } } $mdata = join(',', $data); ?> <html> <head> <script type="text/javascript" src="https://www.google.com/jsapi"></script> <script type="text/javascript"> google.load('visualization', '1.1', {packages: ['line']}); google.setOnLoadCallback(drawChart); function drawChart() { var data = new google.visualization.DataTable(); data.addColumn('string', 'Date'); data.addColumn('number', 'Total Sales'); data.addRows([<?=$mdata?>]); var options = { chart: { title: 'Current Month', subtitle: 'Daily sales' }, width: 800, height: 250, axes: { x: { 0: {side: 'top'} } } }; var chart = new google.charts.Line(document.getElementById('month_chart')); chart.draw(data, options); } </script> </head> <body> <div id="month_chart"></div> </body> </html>

It will update records that exist as expected. It also attempts to update those that do not exist. It is "working" but more by luck than good coding.

P.S. you can provide a dateFormat attribute in the call to Morris.Line() but that only the affects the date displayed when hovering. Axis dates remain yyyy-mm-dd eg dateFormat: function (x) { var date = new Date(x); var day = date.getDate(); var month = date.getMonth()+1; var year = date.getFullYear(); return day + "/" + month + "/" + year; }

Morris time series charts only accept the following formats for datetime values 2012 2012 Q1 2012 W1 2012-02 2012-02-24 2012-02-24 15:00 2012-02-24 15:00:00 2012-02-24 15:00:00.000 If you change them to dd/mm/yyyy in the query the x-axis dates get screwed (see attached)

Are you sure it's working? if (odbc_num_rows($query));{ $result =odbc_exec($connect,$sql); } The "if" condition and action finishes at the ";" so that last statement is always executed. if (odbc_num_rows($query));{ ^ | remove the semi-colon