创建按周和用户分组的多维数组，同时显示零值

debugcn 发表于 Dev

克雷格豪威尔

这是我到目前为止所拥有的：

$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]); 
}

这将输出以下多维数组：

[["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]]

这个结果有两个我无法纠正的问题。

1.如果计数为0，我需要计数返回0。我尝试了以下方法无济于事：

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

2.我需要内部数组由用户实际分组，如下所示：

[["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]]

我仍在学习这些更复杂的查询。

更新

感谢 Waldson Patricio，解决了一半的难题。现在数组正在正确格式化，但它没有像我希望的那样描绘 0。

这是更新后的代码：

$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
}

这输出：

[["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]]

我曾尝试将连接更改为以下，但这也不起作用：

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

我也试过合并和 ifnull 什么都没有：

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

沃尔德森·帕特里西奥

回答您的问题：

如果计数为 0，我需要计数返回 0

您正在选择jobbooktbl j和内部连接assignmentstbl a ON j.leadid=a.custid。该ON子句确保leadid必须等于a.custid。你的计数永远不会是 0。如果你想要j那些与a表没有关系的行，使用LEFT JOIN而不是INNER JOIN（为了这个工作，你也应该对另一个做同样的INNER JOIN事情）：

$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";

我需要内部数组由用户实际分组，如下所示：

在这里，我假设它u.username是唯一的（如果不是，您可以在字段列表中添加 u.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;
    //$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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。