CODEWITHSUNDEEP
phparraysmultidimensional-arraymysqli
Craig Howell
Craig Howell

Reputation: 1184

Create Multidimensional Array grouped by week and user while showing zero values

Here is what I have so far:

$year = date("Y");
$start = "01/01/".$year;
$today = date("Y-m-d");
$first = $year."-05-14";

$volumeYTDsm = [];

$assignments = "
SELECT COUNT(j.leadid) AS leadcount, DATE_ADD(j.leadcreated, INTERVAL(1-DAYOFWEEK(j.leadcreated)) DAY) AS weeks,
  DATE_ADD(j.leadcreated, INTERVAL(7-DAYOFWEEK(j.leadcreated)) DAY), u.username
  FROM jobbooktbl j
  INNER JOIN assignmentstbl a
    ON j.leadid=a.custid
  INNER JOIN usertbl u
    ON a.userid=u.userid
  WHERE j.leadcreated >= '".$first."' AND j.leadcreated <= '".$today."' AND YEAR(j.leadcreated) = '".$year."'
  GROUP BY WEEK(j.leadcreated), a.userid";
$assignmentsqry = mysqli_query($db,$assignments);
while ($row = mysqli_fetch_array($assignmentsqry)) {
  $sm = $row['username'];
  $float = floatval($row['leadcount']);
  $date = $row["weeks"];
  $newdate = date("n/j/Y", strtotime($date));
  array_push($volumeYTDsm, [$sm,$float]); 
}

This outputs the following multidimensional array:

[["Ryan Balcom",10],["Ryan Balcom",1],["Jared Beckwith",13],["Jared Beckwith",7],["Jared Beckwith",5],["Jared Beckwith",2],["Jim Roberts",9],["Jim Roberts",4],["Jim Roberts",2],["Jim Roberts",4],["Jim Kelly",14],["Jim Kelly",16],["Jim Kelly",10],["Jim Kelly",6],["Josh Bell",9],["Josh Bell",8],["Josh Bell",5],["Josh Bell",10],["Mike Horton",16],["Mike Horton",5],["Mike Horton",5],["Mike Horton",2],["Paul Schilthuis",7],["Paul Schilthuis",6],["Paul Schilthuis",4]]

There are two problems with this outcome that I am having trouble rectifying.

1. I need the count to return 0 if the count is 0. I have tried the following to no avail:

COALESCE(COUNT(j.leadid),0) AS leadcount
IFNULL(COUNT(j.leadid),0) AS leadcount

2. I need the inner arrays to be actually grouped by the users like the following:

[["Ryan Balcom",0,0,10,1],["Jared Beckwith",13,7,5,2],["Jim Roberts",9,4,2,4],["Jim Kelly",14,16,10,6],["Josh Bell",9,8,5,10],["Mike Horton",16,5,5,2],["Paul Schilthuis",0,7,6,4]]

I am still learning these more complex queries.

Update

Thanks to Waldson Patricio, half of the puzzle is solved. Now the array is formatting properly but it is not portraying 0s as I would like them to.

Here is the updated code:

$assignments = "
  SELECT COUNT(j.leadid) AS leadcount, DATE_ADD(j.leadcreated, INTERVAL(1-DAYOFWEEK(j.leadcreated)) DAY) AS weeks,
    DATE_ADD(j.leadcreated, INTERVAL(7-DAYOFWEEK(j.leadcreated)) DAY), u.username
  FROM jobbooktbl j
  LEFT JOIN assignmentstbl a
    ON j.leadid=a.custid
  LEFT JOIN usertbl u
    ON a.userid=u.userid
  WHERE j.leadcreated >= '".$first."' AND j.leadcreated <= '".$today."' AND YEAR(j.leadcreated) = '".$year."' AND u.salesman = 1
  GROUP BY WEEK(j.leadcreated), a.userid";
$assignmentsqry = mysqli_query($db,$assignments);
while ($row = mysqli_fetch_array($assignmentsqry)) {
  $key = $row['username']; //unnecessary variable for demonstration purposes
  if (!isset($volumeYTDsm[$key])) {
    $volumeYTDsm[$key] = [$row['username']];
  }
  $float = floatval($row['leadcount']);
    $volumeYTDsm[$key][] = $float;
  }
  $volumeYTDsm = array_values($volumeYTDsm);//removing keys
}

This outputs:

[["Ryan Balcom",10,1],["Jared Beckwith",13,7,5,2],["Jim Roberts",9,4,2,4],["Jim Kelly",14,16,10,6],["Josh Bell",9,8,5,10],["Mike Horton",16,5,5,2],["Paul Schilthuis",7,6,4]]

I have tried changing the joins to below but that did not work either:

FROM usertbl u
LEFT JOIN assignmentstbl a
  ON u.userid=a.userid
LEFT JOIN jobbooktbl j
  ON a.custid=j.leadid

I have also tried coalesce and ifnull and nothing:

COALESCE(COUNT(j.leadid),0) AS leadcount
IFNULL(COUNT(j.leadid),0) AS leadcount

Upvotes: 1

Views: 33

Answers (1)

Waldson Patricio
Waldson Patricio

Reputation: 1529

Answering your questions:

  1. I need the count to return 0 if the count is 0

You're selecting from jobbooktbl j and inner joining with assignmentstbl a ON j.leadid=a.custid. This ON clause ensures that leadid must be equal to a.custid. Your count will never be 0. If you want rows from j that doesn't have relations with a table, use LEFT JOIN instead of INNER JOIN (for this to work, you should do the same with the other INNER JOIN too):

$assignments = "
SELECT COUNT(j.leadid) AS leadcount, DATE_ADD(j.leadcreated, INTERVAL(1-DAYOFWEEK(j.leadcreated)) DAY) AS weeks,
  DATE_ADD(j.leadcreated, INTERVAL(7-DAYOFWEEK(j.leadcreated)) DAY), u.username
  FROM jobbooktbl j
  LEFT JOIN assignmentstbl a
    ON j.leadid=a.custid
  LEFT JOIN usertbl u
    ON a.userid=u.userid
  WHERE j.leadcreated >= '".$first."' AND j.leadcreated <= '".$today."' AND YEAR(j.leadcreated) = '".$year."'
  GROUP BY WEEK(j.leadcreated), a.userid";
  1. I need the inner arrays to be actually grouped by the users like the following:

Here, I'm assuming that u.username is unique (if it isn't, you could add u.userid in your field list and use it as key instead of username):

$assignmentsqry = mysqli_query($db,$assignments);
while ($row = mysqli_fetch_array($assignmentsqry)) {
    $key = $row['username']; //unnecessary variable for demonstration purposes

    if (!isset($volumeYTDsm[$key])) {
        $volumeYTDsm[$key] = [$row['username']];
    }

    $float = floatval($row['leadcount']);
    $volumeYTDsm[$key][] = $float;
    //$date = $row["weeks"]; // You're not using it
    //$newdate = date("n/j/Y", strtotime($date)); // You're not using it
}
$volumeYTDsm = array_values($volumeYTDsm);//removing keys

Upvotes: 1

Related Questions