Looking for php code may need COUNT, JOIN FOR EACH

mbeauc636

I am not sure where to start with this coding so am looking for help. Here's my question. Hope it's detailed enough.

comp
id name
1 good
2 bad
3 denied
4 cancel

esp
id espname
1 flickr
2 chicophoto
3 picasa
4 google

report
id espid comp
1 1 1
2 1 2
3 2 1
4 1 4
5 1 2
6 2 1
7 3 1

Looking for the php/mysql code to list the espnames looping through each ESPNAME to gather the sum or count of each 'comp' = 1, 2, 3 and 4

sample output table would look like this:

espname | Good | Bad | Denied | Cancel|
flickr | 1 | 2 | 0 | 1 |
chicophoto| 2 | 0 | 0 | 0 |
picasa | 1 | 0 | 0 | 0 |
google | 0 | 0 | 0 | 0 |

THanks for taking the time to read my question !

cretaceous

What have you tried?
You know the correct terms : SUM and COUNT - should be all doable in MySql

mbeauc636

I didn't think of SUM. That's a start for me to search further.

I've managed to calculate the number of reports for each COMP using for example:

if ($result = $mysqli->query("SELECT FROM as_subpoenas WHERE compliance = 3")) {
/ determine number of rows result set */
$row_cnt3 = $result->num_rows;
echo $row_cnt;

I've managed to calculate total no. of reports for each esp but not the sum of each comp

<table>
$res = $MySQLiconn->query("SELECT esp.espname, COUNT( report.comp ) AS total
FROM report
INNER JOIN esp ON esp.id =report.espid
GROUP BY esp.espname
while($row=$res->fetch_array())
{
?>
<tr>
<td><?php echo $row['espname']; ?></td><td><?php echo $row['total']; ?></td>
</tr>
<?php
}
?>
</table>

mbeauc636

I managed with this somewhat the query wanted just not the placement of fields in the right column... Also would be nice to have 0 values when no result exist for comp=value

<table>
<tr><td>ESPID</td><td>Good</td><td>Bad</td><td>Cancelled</td><td>DNR</td></tr>
<?php
$resall = $MySQLiconn->query("

SELECT report.espid, SUM(report.comp) AS total1
FROM report
Where Comp = 1

UNION
SELECT report.espid, SUM(report.comp) AS total2
FROM report
Where Comp = 2

UNION
SELECT report.espid, SUM(report.comp) AS total3
FROM report
Where Comp = 3

UNION
SELECT report.espid, SUM(report.comp) AS total4
FROM report
Where Comp = 4

GROUP BY report.espid
ORDER BY report.espid
");

while($rowallesp=$resall->fetch_array())
{
?>
<tr>
<td><?php echo $rowallesp['report.espid']; ?></td>
<td><?php echo $rowallesp['total1']; ?></td>
<td><?php echo $rowallesp['total2']; ?></td>
<td><?php echo $rowallesp['total3']; ?></td>
<td><?php echo $rowallesp['total4']; ?></td> </tr>
<?php
}
?>
</table>

Returns in a list not a column:

espid total1
1 1
1 2
2 2
3 1
2 1

Preferred
espid total1 total2 total3 total4
1 1 2 0 0
2 2 0 3 0
3 1 0 0 0

cretaceous

Are you sure the code you show is what it really is ?
I can't see why it would be a list and not a column.

I wonder if there is a problem with having
'SELECT report.espid' on every UNION statement
Are you certain they are all the same 'espid' ?
You could alias each one:

SELECT report.espid AS espid1, SUM(report.comp) AS total1

But hard to tell without seeing your data structure.
You should use the web forum PHP tags on your code to make it easier to read!

mbeauc636

Such a noob I am sorry. Thanks for your patience cretaceous.

How about this...I can probably figure out my tables naming if I can just get the simple SQL code/logic.

Let me re-phrase maybe.

I need the sum of how many 1's, 2's, 3's and 4's each person has.

joe-----1
joe-----1
joe -----1
joe -----1
joe -----1
john ----1
bob------1
bob------1
bob------2
joe------2
bob-----3
joe-----4

Want the output to show SUM of each time user has a 1, a 2, a 3 or a 4:

User | 1's | 2's | 3's | 4's

Joe | 5 | 1 | 0 | 1

Bob | 2 | 1 | 1 | 0
John | 1 | 0 | 0 | 0

Weedpacket

It's not a SUM, but a COUNT; the values don't matter since they're just IDs (what does "bad"+"denied" equal?).

As an intermediate step

SELECT id,comp,count(*) FROM report GROUP BY id,comp

That gives you a list of names (and picasa,flickr, etc. turned into joe,bob, etc? Try and be consistent when you refer to things.), comps, and the number of times each name/comp pair appeared. That can be used to put together an array or HTML table arranged how you want.

And please use the forum's formatting tags described in the FAQs to format things. There's no point going to all the trouble of drawing lines in your tables if you don't.