Create array from mysql query

Question

I have something like this in my MySQL DB.

 Date            product 
2015-01-01           1
2015-01-01           2
2015-02-03           3
2015-02-04           1  
2015-02-04           1
2015-02-04           3

What i need in PHP is to know which product was how often sold on which day. So: 01.01.2015 Product 1 one time, product 2 one time, on 04.02.2015 product 1 two times, product 3 one time ...

Like this:

Date                product 1  product 2 product 3 
2015-01-01           1           1       0     //how many times 
2015-02-03           0           0       1
2015-02-04           2           0       1

So i did a normal query: SELECT date from table order by date.

I get the object as stdClass in a array back, but now i am stuck with how to get the information out i need.

I tried something like

$array=array();

foreach ($result as $key => $value) {
    $time = ($result[$key]->date);

    $temp[] = array(
            'date' => $time, 
            'product 1' => '2', //equals times
            'product 2' =>  '3', 
            'product 3' => '4',
            'product 4' => '4' 

    );
    $arrayBig[] = $temp;

}

And also used array_count_values to filter the days to know which days appears, but i can not find out how to connect the product to the days.

EDIT: DWright's solution:

 SELECT product, date, COUNT(*)
 FROM tablename
 GROUP BY product, date.

Date product count(*) 2015-01-01 1 1 2015-01-01 2 2 2015-02-03 3 1

Worked fine, now i have in each row which product was sold in which date how often.

The problem i encounter now is that if i want use this data to populate google stacked charts as seen below each row in the results represents on column in the google charts graph. So for the example above i will have two entries on 01.01.2015 in my charts (one bar for product 1 and one for product 2) but i want the amount of products on each day to be stacked. So there should be only one entry for 01.01.2015 where the amount of product 1 sold on that day and the amount of product 2 sold that day is stacked onto each other,

$result = $sql statement

foreach ($result as $key => $value ) {
$typeOfProduct = $value->product;
$amount = $value->anzahl;
$time = $value->Date;
    switch ($typeOfProduct) {
                case 1: 
                    $produt1 = $amount;
                    break;
                case 2: //twitter
                    $product2 = $amount;
                    break;
                case 3:
                    $product3 = $amount;
                    break;
                default:

                    break;
            }
    $rows = array();
            $table = array();
            $table['cols'] = array(

                array('label' => 'Datum', 'type' => 'date'),
                array('label' => 'product 1', 'type' => 'number'),
                array('label' => 'product 2', 'type' => 'number'),
                array('label' => 'product 3', 'type' => 'number')

            );

    $day = date('d', strtotime( $time ) );
    $month = date('m', strtotime( $time ) );
    $monthNew  = $month - 1;
    $year = date('Y', strtotime( $time ) );


            $temp[] = array('v' => "Date( $year, $monthNew, $day )");
            $temp[] = array('v' => $product1 );
            $temp[] = array('v' => $product2);
            $temp[] = array('v' => $product3 );

            $rows[] = array('c' => $temp);
            $table['rows'] = $rows;
}

This would result in something like this:https://jsfiddle.net/api/post/library/pure/

But i would need the values to be stacked onto each other , like this: https://jsfiddle.net/api/post/library/pure/

I will need a json object, which i want to extract from the array so that i can dynamically populate google stacked charts — tkoatl
– tkoatl, Commented Apr 30, 2015 at 8:29

DWright · Accepted Answer · 2015-04-30 04:22:23Z

3

This will be a lot easier for you to do in SQL. Something like:

 SELECT product, date, COUNT(*)
 FROM tablename
 GROUP BY product, date.

Then each row is for one product by date, with the count sold that date.

answered Apr 30, 2015 at 4:22

DWright

9,5594 gold badges39 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Strawberry Over a year ago

And if a product is missing from a given date, that means none of that product was sold on that date.

Collectives™ on Stack Overflow

Create array from mysql query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related