Iterate through returned SELECT COUNT in PHP

Question

I have a SQL table called 'incidents' which holds information on behavioural incidents in a school.

I have the following SQL statement which returns the number of occurrences of each 'type'

SELECT `type`, count(*) FROM `incidents` WHERE `studentID` = :id GROUP BY `type`

Basically, I'm looking to create a chart to show the number of occurrences of each incident type in the table 'incidents'

I var_dumped what it returns and I get this

array(2) { 
    [0]=> array(4) { ["type"]=> string(9) "Classwork" [0]=> string(9) "Classwork" ["count(*)"]=> string(1) "3" [1]=> string(1) "3" } 
    [1]=> array(4) { ["type"]=> string(8) "Bullying" [0]=> string(8) "Bullying" ["count(*)"]=> string(1) "1" [1]=> string(1) "1" } 
}

I'm really struggling to parse this to create meaningful output. How would I loop through this array and output the number of each incident type? For example, to output:

3 classwork incident
1 bullying incident

I've tried the following to test the output with a foreach but it just says I cannot offset the array by "type"

function orderByType($id){
    include_once 'includes/db_connection.php';
    $dbconn = OpenCon();
    $dbconn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
    $sqlstmnt2 = 'SELECT `type`, count(*) FROM `incidents` WHERE `studentID` = :studentID GROUP BY `type`';
    $stmtUsr2 = $dbconn -> prepare($sqlstmnt2);
    $intid = intval($id);
    $stmtUsr2 -> bindValue(':studentID', $intid);
    $stmtUsr2 -> execute();
    $rows = $stmtUsr2->fetchAll();
    //list found pupils
    if(is_null($rows))
            {
                //list found pupils
                return 0;
            }
    //return $rows;
    foreach($rows as $item){
        echo($item[0]["type"]);
    }
    var_dump($rows);
}

Have you tried something? Could you update the question with your code? — Felippe Duarte, Sep 24 '20 at 18:43
Just loop through the returned value and echo the indexes you want from each row. — Barmar, Sep 24 '20 at 18:47
I have updated the question with my attempt. The issue is I don't know how to iterate — TerryHaggerty, Sep 24 '20 at 18:48
can you store all data into a table and the fetch whatever you want `SELECT `type`, `studentID` , count(*) cnt FROM `incidents` GROUP BY `studentID`, `type`` — Koushik Roy, Sep 24 '20 at 18:50
possibly but I don't know how to do that with only that information — TerryHaggerty, Sep 24 '20 at 18:56
USE alias `count(*) as Total` and then `$item['Total']` or `$item->Total` — Ashok, Sep 24 '20 at 19:10

score 0 · Answer 1 · answered Sep 24 '20 at 19:04

0

foreach($rows as $item){
        echo($item["count(*)"]);
    }

answered Sep 24 '20 at 19:04

TerryHaggerty

17
2

score 0 · Answer 2 · answered Sep 24 '20 at 19:43

I'd recommend starting by providing an alias for "count(*)". eg: cnt

SELECT `type`, count(*) as cnt FROM `incidents` WHERE `studentID` = :studentID GROUP BY `type`

Then switch your $item and $rows around:

foreach($rows as $item){
    echo($item["cnt"]);
}

Also you can try using a while loop using fetch(PDO::FETCH_ASSOC) so it only returns the values once with associative names. Use \n instead of <br> after "incident" if HTML isn't your desired output.

while ($row = $stmtUsr2->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_NEXT)) {
    echo $row['cnt'] . " " . $row['type'] . " incident<br>";
}

This way a var_dump of $row will look like this:

array(2) { 
    [0]=> array(2) { ["type"]=> string(9) "Classwork" ["cnt"]=> string(1) "3" } 
    [1]=> array(2) { ["type"]=> string(8) "Bullying" ["cnt"]=> string(1) "1" } 
}

And the loop will output as requested:

3 classwork incident
1 bullying incident

Mattia Monari · Answer 3 · 2020-09-24T19:28:27.880

-1

Since $rows is an array which contains other arrays (matrix) you probably need 2 for loops. Try this:

for(int $i=0;$i < count($rows); $i++){
 for(int $j=0; $j< count($rows[i]); $j++).  {
     echo $rows[$i][$j];
   }
echo $rows[$i]["type"];
echo $rows[$i]["count*"];

}

edited Sep 24 '20 at 19:28

answered Sep 24 '20 at 19:16

Mattia Monari

158
1
3
9

This won't work because `$rows[$i]` is a mix of named and numbered elements. Also you're missing `$` on most of your variables. – Barmar Sep 24 '20 at 19:22
You're right @Barmar. I'm editing – Mattia Monari Sep 24 '20 at 19:26
And if you print all the array elements you'll get duplicate data, because the OP apparently used `PDO::FETCH_BOTH` – Barmar Sep 24 '20 at 19:28
Ok, so how would u do that? – Mattia Monari Sep 24 '20 at 19:30
There's no need for the inner loop. Just print the columns that are desired. `echo $rows[$i]['count(*)'] . " " . $rows[$i]['type'];` – Barmar Sep 24 '20 at 19:37

Iterate through returned SELECT COUNT in PHP

3 Answers3