Adding zero values to an array

Question

I am doing the following queries on a database that holds various customers orders.

SELECT WEEKDAY(orderDateTime) Date, COUNT(clientID) totalCount FROM orders WHERE YEARWEEK(orderDateTime,1) = YEARWEEK(NOW(),1) GROUP BY DATE(orderDateTime)

SELECT WEEKDAY(orderDateTime) Date, COUNT(clientID) totalCount FROM orders WHERE YEARWEEK(orderDateTime,1) = YEARWEEK(NOW() - INTERVAL 1 WEEK,1) GROUP BY DATE(orderDateTime)

SELECT DATE_FORMAT(orderDateTime, '%Y') as 'year', DATE_FORMAT(orderDateTime, '%m') as 'month', COUNT(clientID) as 'total' FROM orders GROUP BY DATE_FORMAT(orderDateTime, '%Y%m')

I am them building these into an array with the month/ day numbers against the counted value:

[[0, 1], [1,4]...]

I am using Flot to graph these but what is happening is that any days or months that there are no orders it has no values (Obviously) so what you get is something like this:

[[0, 1], [1,4], [6,12]] which makes the graph plot wrong.

What trying to work out is how to pad it so it looks like:

[[0, 1], [1,4], [2,0], [3,0], [4,0], [5,0], [6,12]] <-- For the week days and,

[[1, 1], [2,4], [3,0], [4,0], [5,0], [6,0], [7,12], [8,0], [9,12], [10,0], [11,0], [12,0]] <-- for each month.

Im using PHP as the main grunt. Any pointers would be appreciated and sorry if I'm not that clear. Ask if you need any clarification.

PHP:

$thisWeekA = array();

$thisWeekQ = $database->query("SELECT WEEKDAY(orderDateTime) Date, COUNT(clientID) totalCount FROM orders WHERE YEARWEEK(orderDateTime,1) = YEARWEEK(NOW(),1) GROUP BY DATE(orderDateTime)")->fetchAll();

foreach($thisWeekQ as $thisweek){
    $thisWeekA[] = array( $thisweek['Date'], $thisweek['totalCount'] );
}

array(2) {
  [0]=>
  array(4) {
    ["Date"]=>
    string(1) "2"
    [0]=>
    string(1) "2"
    ["totalCount"]=>
    string(1) "3"
    [1]=>
    string(1) "3"
  }
  [1]=>
  array(4) {
    ["Date"]=>
    string(1) "3"
    [0]=>
    string(1) "3"
    ["totalCount"]=>
    string(1) "2"
    [1]=>
    string(1) "2"
  }
}

So you need your php input data to flot graph to be processed. For more understanding can you share the php code you used to prepare the graph onput data — Karthik N
– Karthik N, Commented May 1, 2016 at 17:36
on a weekly graph [0 (sunday), 12(count of orders)] am i right? — Karthik N
– Karthik N, Commented May 1, 2016 at 17:46
Sorry, I thought this was tagged javascript when I wrote my now deleted answer. — JonSG
– JonSG, Commented May 1, 2016 at 18:11

Karthik N · Accepted Answer · 2016-05-01 18:02:40Z

1

for weekly data use for loop in your php update it as, Logic only 7 days in a week of instead of using foreach from the result data we can use the for loop for seven days

i.e., similarly use for years also

$thisWeekA = array();    
$thisWeekQ = $database->query("SELECT WEEKDAY(orderDateTime) Date, COUNT(clientID) totalCount FROM orders WHERE YEARWEEK(orderDateTime,1) = YEARWEEK(NOW(),1) GROUP BY DATE(orderDateTime)")->fetchAll();

    for ($i = 0; $i <= 6; $i++) {
        if ($thisWeekQ[$i]['Date'] == $i) {
            $thisWeekA[$i][] = array($thisWeekQ[$i]['Date'], $thisWeekQ[$i]['totalCount']);
        } else {
            $thisWeekA[$i][] = array($i, 0);
        }
    }

answered May 1, 2016 at 18:02

Karthik N

9518 silver badges16 bronze badges

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

2 Comments

CallumBee Over a year ago

I have added what the output of the query looks like.

CallumBee Over a year ago

Sorry, my comment wasn't that clear! Had a fiddle about due to it looking at the array number it means it will never match with an order count. All my numbers come out as 0.

JonSG · Accepted Answer · 2016-05-01 18:08:04Z

1

You might want to think about building your arrays more like this:

var dow = [];
dow[0] = 1;
dow[1] = 4;
dow[6] = 12;

At this point you can populate your array for your chart

var chartData = [];
for (var i=0; i<7; i++){
    chartData[i] = [i, (dow[i] === undefined ? 0 : dow[i])];
}

answered May 1, 2016 at 18:08

JonSG

13.6k2 gold badges32 silver badges48 bronze badges

Comments

CallumBee · Accepted Answer · 2016-05-01 19:44:51Z

0

I managed to figure out a way of doing this. Thanks for pointing me in the direction of the For loop!

This is what I have came up with and works!

function arrayhunt($products, $field, $value){

   foreach($products as $key => $product){

        if ($product[$field] == $value){
            return $key;
        }
   }
   return false;
}


    $i = 0;
    for ($i = 0; $i <= 6; $i++) {

        $keys = arrayhunt($thisWeekQ,"Date",$i);

        if($keys !== FALSE){
            $thisWeekA[] = array($thisWeekQ[$keys]['Date'], $thisWeekQ[$keys]['totalCount']);
        } else {
            $thisWeekA[] = array($i, 0);
        }

        $keys = "";
    }

Hopefully someone finds this useful later.

answered May 1, 2016 at 19:44

CallumBee

732 silver badges16 bronze badges

Collectives™ on Stack Overflow

Adding zero values to an array

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related