PDF Report Queries in MySQL

I am currently writing code for an attendance application. I need to create two reports. One that is a summarized attendance report of who showed up or not and one that provides more detail to the first.

To give you a better idea, here is a sample of the two reports:

Report 1 (Summary):

image

Report 2 (Detail):

I’ve managed to work on these a little bit and have created the following tables:

Members:

Attendance:

Absent:

The problem I’m having is coming up with the right queries to produce the right information.

The first report has this query:

"SELECT m.precinct as 'precinct', COUNT(attn.member_id) as 'delegates_present', COUNT(ab.member_id) as 'delegates_absent', COUNT(attn.member_id) + COUNT(ab.member_id) as 'total' FROM members m LEFT JOIN attendance as attn ON attn.member_id = m.id LEFT JOIN absence as ab ON ab.member_id = m.id GROUP BY m.precinct WITH ROLLUP;"

The result looks like this:

but I don’t want it to select EVERY precinct, just the ones that are shown from the attendance table.

The second report query is this:

SELECT precinct, name, residential_address, IF(at.member_id IS NULL, 0, 1) as 'present', IF(ab.member_id IS NULL, 0, 1) as 'absent' FROM members m LEFT JOIN attendance at ON at.member_id = m.name LEFT JOIN absence ab ON ab.member_id = m.name ORDER BY m.precinct, m.id

and produces errors.

Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'name' in 'field list' in C:\xampp\htdocs\ccrp\api\reports\delegates.php:44 Stack trace: #0 C:\xampp\htdocs\ccrp\api\reports\delegates.php(44): PDOStatement->execute() #1 C:\xampp\htdocs\ccrp\api\reports\delegates.php(60): Report->viewDelegates() #2 {main} thrown in C:\xampp\htdocs\ccrp\api\reports\delegates.php on line 44

So then you’ve put your tables in the wrong order (or wrong join type) in your query.

A LEFT JOIN B means “Give me every record in A, but join them with the ones in B where you can”.
If you want everything in B, regardless of whether it is in A, you want a RIGHT JOIN.
If you want only rows in which A and B can be joined, then you want an INNER JOIN. (This is also just a JOIN, because the assumed default is an inner join.)

The members table does not have a column named ‘name’. Your error kind of spells it out for you :stuck_out_tongue:

I replaced the LEFT JOIN instances with INNER JOIN and now the query is blank:

SELECT m.precinct as 'precinct', COUNT(attn.member_id) as 'delegates_present', COUNT(ab.member_id) as 'delegates_absent', COUNT(attn.member_id) + COUNT(ab.member_id) as 'total' FROM members m INNER JOIN attendance as attn ON attn.member_id = m.id INNER JOIN absence as ab ON ab.member_id = m.id GROUP BY m.precinct WITH ROLLUP

As for the second query… I work on that and get back to you.

I can’t read the image. (please post text instead)

If the change was based on post #2, then you’re saying the results are what you wanted?

My bad. I changed the query to this:

SELECT m.precinct as 'precinct'
, COUNT(attn.member_id) as 'delegates_present'
, COUNT(ab.member_id) as 'delegates_absent'
, COUNT(attn.member_id) + COUNT(ab.member_id) as 'total' 
FROM members m 
INNER JOIN attendance as attn ON attn.member_id = m.id 
INNER JOIN absence as ab ON ab.member_id = m.id 
GROUP BY m.precinct WITH ROLLUP

and no results are shown.

For now, I am testing the queries in phpMyAdmin until I get the results I need at which point I’ll place it in my code.

@m_hutley

As for the second query: I need to change the name field to a concatenated string of the last_name, first_name, middle_name and suffix columns from the members table. How would I do that?

I’ve used CONCAT() before, but it doesn’t appear to work as all of my results are NULL. I’ve heard of CONCAT_WS but I’m not sure what the difference is. Either way, none of them work…

SELECT precinct, 
CONCAT(last_name, ", ", first_name, middle_name, suffix) as full_name, 
residential_address, 
IF(at.member_id IS NULL, 0, 1) as 'present', 
IF(ab.member_id IS NULL, 0, 1) as 'absent' 
FROM members m 
INNER JOIN attendance at ON at.member_id = m.id 
INNER JOIN absence ab ON ab.member_id = m.id 
ORDER BY m.precinct, m.id