PDF Report Queries in MySQL

[jmyrtle]

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):

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

[m_hutley]

@jmyrtle

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

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.)

[m_hutley]

@jmyrtle

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 colum

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

[jmyrtle]

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.

[Mittineague]

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?

[jmyrtle]

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