I am simply trying to get the code below to recognize when the count is zero and return 0.
Setting the date variables and week numbers table
//set first of the year and today
$year = date("Y");
$start = "01/01/".$year;
$today = date("Y-m-d");
$first = $year."-01-01";
$start = $year."-01-01";
$date1 = new DateTime($first);
$date2 = new DateTime($today);
$interval = $date1->diff($date2);
$weeks = floor(($interval->days) / 7);
//create weeks table
$sql = "DROP TEMPORARY TABLE IF EXISTS weekstbl" ;
mysqli_query ($db, $sql ) or ( "Error " . mysqli_error () ) ;
$weekstbl = "
CREATE TEMPORARY TABLE weekstbl (
`weekNo` int NOT NULL,
`weekStart` Date,
`weekEnd` Date,
PRIMARY KEY(weekNo)
)
";
mysqli_query($db, $weekstbl) or die ("Sql error : ".mysqli_error());
for($i = 1; $i <= $weeks; $i++){
$week = $date1->format("W");
$date1->add(new DateInterval('P6D'));
$date1->format('Y-m-d');
$newDate1 = $date1->format('Y-m-d');
$statement = $db->prepare("INSERT INTO weekstbl (weekNo,weekStart,weekEnd) VALUES (?,?,?)");
$statement->bind_param('iss', $week,$start,$newDate1);
$statement->execute();
$date1->add(new DateInterval('P1D'));
$start = $date1->format('Y-m-d');
}
This code outputs the all of the week numbers, start date for the week and end date for the week for the current year through the current day. Looks like the following:
------------------------------------
| weekNo | weekStart | weekEnd |
------------------------------------
| 52 | 2017-01-01 | 2017-01-07 |
| 1 | 2017-01-08 | 2017-01-14 |
| 2 | 2017-01-15 | 2017-01-21 |
This continues on through the current date the system is being accessed. This will then be used to join with another few other tables with conditions. This is what I expect to be returned but in all of my trials listed below, I cannot accomplish this. I ran this when there were 12 weeks in the current Year, which means there should be 12 numbers following the name.
[["Ryan Balcom",3,30,3,1,10,0,1,2,5,1,3,3],["Jared Beckwith",40,8,13,8,13,7,5,3,11,5,3,1],["Jim Roberts",0,32,8,7,2,9,4,2,8,4,4,10],["Jim Kelly",44,24,12,14,14,16,10,25,12,8,7,6],["Josh Bell",34,10,10,5,9,8,5,23,7,6,6,14],["Rick Zuniga",0,0,0,0,0,3,1,1,0,0,0,0],["Mike Horton",37,20,7,10,16,5,6,4,5,3,5,1],["Paul Schilthuis",31,11,9,2,8,6,4,0,5,9,8,4],["TJ Sutton",1,10,0,0,0,0,0,0,0,0,0,0]]
Trial 1
$assignments = "
SELECT COUNT(j.leadid) AS leadcount, u.username
FROM weekstbl wk
LEFT JOIN jobbooktbl j
ON wk.weekNo=WEEK(j.leadcreated)
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";
$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 the above but does not inlcude the 0 weeks:
[["Ryan Balcom",3,30,3,1,10,1,2,5,1,3,3],["Jared Beckwith",40,8,13,8,13,7,5,3,11,5,3,1],["Jim Roberts",32,8,7,2,9,4,2,8,4,4,10],["Jim Kelly",44,24,12,14,14,16,10,25,12,8,7,6],["Josh Bell",34,10,10,5,9,8,5,23,7,6,6,14],["Rick Zuniga",3,1,1],["Mike Horton",37,20,7,10,16,5,6,4,5,3,5,1],["Paul Schilthuis",31,11,9,2,8,6,4,5,9,8,4],["TJ Sutton",1,10]]
Trial 2
$assignments = "
SELECT COUNT(*) AS leadcount, u.username
FROM weekstbl wk
LEFT OUTER JOIN jobbooktbl j
ON (wk.weekNo=WEEK(j.leadcreated)) AND j.leadcreated >= '".$first."' AND j.leadcreated <= '".$today."' AND YEAR(j.leadcreated) = '".$year."'
LEFT JOIN assignmentstbl a
ON j.leadid=a.custid
LEFT JOIN usertbl u
ON a.userid=u.userid
GROUP BY wk.weekNo, a.userid";
This outputs the following...not really sure what this is outputting with the null name:
[[null,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1],["Ryan Balcom",3,30,3,1,10,1,2,5,1,3,3],["Jared Beckwith",40,8,13,8,13,7,5,3,11,5,3,1],["Jim Roberts",32,8,7,2,9,4,2,8,4,4,10],["Jim Kelly",44,24,12,14,14,16,10,25,12,8,7,6],["Josh Bell",34,10,10,5,9,8,5,23,7,6,6,14],["Rick Zuniga",3,1,1],["Mike Horton",37,20,7,10,16,5,6,4,5,3,5,1],["Paul Schilthuis",31,11,9,2,8,6,4,5,9,8,4],["TJ Sutton",1,10]]
With both of these methods I have tried to include COALESCE and IFNULL but neither changed the result for either query:
COALESCE(COUNT(j.leadid),0) AS leadcount
IFNULL(COUNT(j.leadid),0) AS leadcount
I have been trying to figure this out for a month and nothing I throw at it seems to work. Any assistance or direction would be greatly appreciated!