Returning mysql $row data into a php array

[bambinou1980]

Hello,

 

I am confused,  you the below code not return an array of the selected data from mysql?

$query = "SELECT
  SUM(cust_order_total) AS daily_gross_sales,
  due_date AS day
FROM orders
WHERE YEAR(due_date) = YEAR(CURDATE())
AND MONTH(due_date) = MONTH(CURDATE())
GROUP BY due_date
ORDER BY day";     
$rows = '';
$result = mysqli_query($connection,$query);
while($row = mysqli_fetch_array($result))
{
    $rows = mysqli_fetch_all($result, MYSQLI_ASSOC);

} 
echo json_encode($rows); 

 

 

I would like to have the mysql data outputting as:

array('label' => $row['cust_order_total'], 'y' => $row['due_date']);

 

 

I have also tried this:

 

 

$query = "SELECT
  SUM(cust_order_total) AS daily_gross_sales,
  due_date AS day
FROM orders
WHERE YEAR(due_date) = YEAR(CURDATE())
AND MONTH(due_date) = MONTH(CURDATE())
GROUP BY due_date
ORDER BY day";     
$types = array();
$result = mysqli_query($connection,$query);
while(($row =  mysqli_fetch_assoc($result))) {
    $types[] = $row['daily_gross_sales'];
}
print_r($types); 

 

But no luck, the array returns empty even if I have data in the database.

 

 

Thank you.

Edited September 1, 2015 by bambinou1980

[Barand]

 

array('label' => $row['cust_order_total'], 'y' => $row['due_date']);

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);

Edited September 1, 2015 by Barand

[bambinou1980]

Hi Barand,

 

 

Thank you for the reply.

 

What I am trying to understand is the logic behind creating an array with aliases and without.

I have created 2 queries below, the firslt one(with the aliases) does not work(no ouput) while the other works, could you please tell me why as I am really confused on this.

<?php
$query = "SELECT
SUM(cust_order_total) AS daily_gross_sales,
due_date AS day
FROM orders
WHERE YEAR(due_date) = YEAR(CURDATE())
AND MONTH(due_date) = MONTH(CURDATE())
GROUP BY due_date
ORDER BY day";     
$types = array();
$result = mysqli_query($connection,$query);
while(($row =  mysqli_fetch_assoc($result))) {
    $types[] = array('x' => $row['day'], 'y' => $row['daily_gross_sales']);
}
    echo json_encode($types);
?>

<?php
$query = "SELECT * FROM orders
GROUP BY due_date
ORDER BY due_date";     
$types = array();
$result = mysqli_query($connection,$query);
while(($row =  mysqli_fetch_assoc($result))) {
    $types[] = array('x' => $row['due_date'], 'y' => $row['cust_name']);

}
    echo json_encode($types);
?>

Thank you,

Edited September 1, 2015 by bambinou1980

[Barand]

see my edit to last post

[bambinou1980]

Thank you Barand,

 

I was expecting your code to work but I am getting a blank array showing just  " [] " :

<?php
$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);
?>

[Barand]

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();

[bambinou1980]

I got it now.....thank you.

I understood the mysql code differently.

 

I thought the 

WHERE YEAR(due_date) = YEAR(CURDATE())
AND MONTH(due_date) = MONTH(CURDATE())

Meant "where Year is the current year

and month the current month

 

With the above code I thought I could have seen all the results for this current year (2015) month by month with all the sales SUM() if all the days together for each month.

But now with your last comment I have just understood that this code was used to output any results day by day of the current month and the current year.

 

If this is the case, I find the mysql code very confusing to read for the dates.

 

So the YEAR() and MONTH() functions in mysql always output the "current" Year and Month?

 

What about outputting the current months with time span of a full current year?

[Barand]

In your other thread you asked for

 

1 graph with the current month and daily sales(so this will correspond to days on the X axis  and prices on the Y axis).

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);

[bambinou1980]

Got it, thanks Barand.

 

By the way I found another nice graph library that is quite pretty too:

http://canvasjs.com/