I have two tables. "members" is list of all members, "stats" is a list of dates worked. The shared field is memberID. I need a COUNT of the number of days each person worked and I want everyone listed in the output table, even if they have not yet had a work day.
Simplified database structure is:
**members** **stats**
memberID lname fname memberID date statsID
1 Mertz Fred 1 2017-12-31 1
2 Doe Jane 3 2017-12-31 2
3 Smith Frank 4 2017-12-31 3
4 Ricardo Lucy 2 2018-12-31 4
5 Starr Ringo 4 2018-12-31 5
2 2019-05-05 6
3 2019-05-05 7
Output desired is:
memberID lname fname Total Days
2 Doe Jane 2
1 Mertz Fred 1
4 Ricardo Lucy 2
3 Smith Frank 2
5 Starr Ringo 0 OR blank
Ringo has not yet worked any days and does NOT appear on the output table.
My code is:
$sql = "SELECT u.*,
COUNT(s.memberID)as tot_days
FROM members u
LEFT JOIN stats s
ON s.memberID = u.memberID
GROUP BY s.memberID
ORDER BY lname,fname";
$members = mysqli_query($dbc,$sql) or die(mysqli_error());
while ($row = mysqli_fetch_array($members)){
$row = array_map('htmlspecialchars', $row);
echo <<< HTML etc.
This does everything I want it to do EXCEPT include those members who have not yet worked a day. JOIN, LEFT JOIN, LEFT OUTER JOIN, RIGHT JOIN, RIGHT OUTER JOIN all produce the same result. I tried LEFT and RIGHT INNER JOIN, if those even exist, which produced error Warning: mysqli_error() expects exactly 1 parameter, 0 given
.
Someone suggested using COALESCE (COUNT(s.memberID),0) as tot_days
but that just produces the same error as above.
I've been at this for days and am getting just a teensy bit frustrated!