8 Replies - 266 Views - Last Post: 23 April 2020 - 04:12 AM Rate Topic: -----

#1 pythonhelp   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 20-January 19

Help with Count Query - Only showing one row

Posted 23 April 2020 - 01:51 AM

Hello,

There are three classes in the database, the count query shows only the present and absent counts for one of the classes.
The query should output three rows, but does only 1.

Any help appreciated.


<?php

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

?>



<?php

//Connection for database


$sql = "SELECT 
    Form_Group,
    COUNT(IF(Presence = '/', 1, NULL)) 'Present',
    COUNT(IF(Presence = 'N', 1, NULL)) 'Absent' FROM attendance GROUP BY Form_Group ORDER BY Form_Group";
?>

<!doctype html>
<html>
<body>
<h1 align="center">Summary</h1>
<table border="1" align="center" style="line-height:25px;">
<tr>
<th>Form</th>
<th>Present</th>
<th>Absent</th>
</tr>
<?php
//Fetch Data form database
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_object($result);



?>

<tr>
<td><?php echo $row->Form_Group; ?></td>
<td><?php echo $row->Present; ?></td>
<td><?php echo $row->Absent; ?></td>
</tr>

<?php

mysqli_close($conn);

?>

</table>
</body>
</html>




Is This A Good Question/Topic? 0
  • +

Replies To: Help with Count Query - Only showing one row

#2 Ornstein   User is offline

  • D.I.C Head

Reputation: 105
  • View blog
  • Posts: 216
  • Joined: 13-May 15

Re: Help with Count Query - Only showing one row

Posted 23 April 2020 - 02:09 AM

I'm not sure what you mean exactly when you talk about row counts, but my first impression is that you may benefit from using SUM instead of COUNT?
Was This Post Helpful? 0
  • +
  • -

#3 pythonhelp   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 20-January 19

Re: Help with Count Query - Only showing one row

Posted 23 April 2020 - 02:14 AM

The final output should show:


Class | Present | Absent

Class 1 | 3 | 0
Class 2 | 1 | 0
Class 3 | 1 | 0


The query is outputting Class 1 correctly, but Class 2 and Class 3 are missing.
Was This Post Helpful? 0
  • +
  • -

#4 Ornstein   User is offline

  • D.I.C Head

Reputation: 105
  • View blog
  • Posts: 216
  • Joined: 13-May 15

Re: Help with Count Query - Only showing one row

Posted 23 April 2020 - 02:19 AM

Oh yeah. If that PHP is your complete code, calling mysqli_fetch_object only once will only get one row from the result. ;)

I suspect you'll still need to use SUM instead of COUNT though. I could be wrong.
Was This Post Helpful? 0
  • +
  • -

#5 pythonhelp   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 20-January 19

Re: Help with Count Query - Only showing one row

Posted 23 April 2020 - 02:27 AM

As I am a newbie, can you guide me to a tutorial to help me put the mysqli_fetch_object into a loop.

Thank you.

This post has been edited by pythonhelp: 23 April 2020 - 02:28 AM

Was This Post Helpful? 0
  • +
  • -

#6 Ornstein   User is offline

  • D.I.C Head

Reputation: 105
  • View blog
  • Posts: 216
  • Joined: 13-May 15

Re: Help with Count Query - Only showing one row

Posted 23 April 2020 - 02:30 AM

You can figure that out for yourself. I believe in you.
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw   User is offline

  • palpable absurdity
  • member icon

Reputation: 6905
  • View blog
  • Posts: 28,565
  • Joined: 12-December 12

Re: Help with Count Query - Only showing one row

Posted 23 April 2020 - 02:52 AM

A search of "mysqli_fetch_object" which quickly lead to the docs and examples.
Was This Post Helpful? 1
  • +
  • -

#8 pythonhelp   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 24
  • Joined: 20-January 19

Re: Help with Count Query - Only showing one row

Posted 23 April 2020 - 03:06 AM

View PostOrnstein, on 23 April 2020 - 02:30 AM, said:

You can figure that out for yourself. I believe in you.

Thank you.
I used this tutorial to help me: https://www.phptpoin...i-fetch-object/

But now I am not sure how to display the data into cells :(
Was This Post Helpful? 0
  • +
  • -

#9 andrewsw   User is offline

  • palpable absurdity
  • member icon

Reputation: 6905
  • View blog
  • Posts: 28,565
  • Joined: 12-December 12

Re: Help with Count Query - Only showing one row

Posted 23 April 2020 - 04:12 AM

You already know how to show one row's data, so that code needs to appear within the loop that iterates all of the rows. Give it a go.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1