Link to home
Start Free TrialLog in
Avatar of SniperCode Sheva
SniperCode Sheva

asked on

How to count in a table in php

Hello, I have a table where I have 5 columns:
Table: Game
Columns: Id,Name, Email, Week, Participation.

So, in my HTML table, I want to show the participants grouped by Email so that if they play many times it will be showed only once. And then, show the number of times that the participant plays using the weeks.
For example:
Mister A plays 5 times for the week 1
and Mister A plays 6 times for the week 2
I want to show in my table :

Name- Email-Participation(Week)
Mister A- mistera@xxx.com- 2(number of weeks)
 <table width="100%" border="0" align="center" cellspacing="5" cellpadding="0" class="CadreBlanc">
        <tr>
          <td align="left" valign="top"><table width="100%" border="0" align="center" cellpadding="0" cellspacing="0" id="TableauJaquery3" class="Tableau">
            <thead>
              <tr>
                <td align="center"><strong>Name</strong></td>
                <td width="300" align="center"><strong>Email</strong></td>                       
                <td width="100" align="center"><strong>Participation(Week)</strong></td>
              </tr>
            </thead>
            <tbody>
              <?php $Stat = $pdo->query("SELECT * FROM table GROUP BY Email, Week"); while($data = $Stat->fetch(PDO::FETCH_ASSOC)){ ?>
              <?php $nRowsP = $pdo->query('SELECT Week, count(1) FROM table GROUP BY Email = "'.$data['Email'].'" ' )->fetchColumn(); ?>
              <tr>
                <td align="left" valign="middle"><?php echo ucwords($data['Name']); ?></td>
                <td align="left" valign="middle"><?php echo $data['Email']; ?></td>               
                <td align="center" valign="middle"><?php echo $nRowsP; ?></td>
              </tr>
              <?php ;} ?>
            </tbody>
          </table></td>
        </tr>
      </table>

Open in new window

But it is not working
It is showing to me:
Mister A- mistera@xxx.com - 1
Mister A- mistera@xxx.com - 2
Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image

You need a COUNT in your query
SELECT COUNT(Week) AS totalWeeks, Name, Email FROM table GROUP BY Name, Email

Open in new window

You don't need a second query - here is your updated code
<table width="100%" border="0" align="center" cellspacing="5" cellpadding="0" class="CadreBlanc">
  <tr>
    <td align="left" valign="top">
      <table width="100%" border="0" align="center" cellpadding="0" cellspacing="0" id="TableauJaquery3" class="Tableau">
        <thead>
          <tr>
            <td align="center"><strong>Name</strong></td>
            <td width="300" align="center"><strong>Email</strong></td>                       
            <td width="100" align="center"><strong>Participation(Week)</strong></td>
          </tr>
        </thead>
        <tbody>
<?php 
  $Stat = $pdo->query("SELECT COUNT(Week) AS totalWeeks, Name, Email FROM table GROUP BY Name, Email"); 
  while($data = $Stat->fetch(PDO::FETCH_ASSOC)){ 
?>
    <tr>
      <td align="left" valign="middle"><?php echo ucwords($data['Name']); ?></td>
      <td align="left" valign="middle"><?php echo $data['Email']; ?></td>               
      <td align="center" valign="middle"><?php echo $data['totalWeeks']; ?></td>
    </tr>
<?php ;} ?>
        </tbody>
      </table>
    </td>
  </tr>
</table>

Open in new window

Hint: Avoid using tables for layout - the inner table is fine - but you are enclosing that in a table which is not a good idea.
Avatar of SniperCode Sheva
SniperCode Sheva

ASKER

With your code, it is showing me the number of time he played which is in my example ;


Mister A- mistera@xxx.com- 6(Week 1)
Mister A- mistera@xxx.com- 5(Week 2)
Please provide me the output of below-

SELECT Id, Name, Email , participation , week FROM table

Open in new window

Id=1
Name=Mister A
Email=mistera@xxx.com
Participation =1
week=1

Id=2
Name=Mister A
Email=mistera@xxx.com
Participation =1
week=1



Id=3
Name=Mister A
Email=mistera@xxx.com
Participation =1
week=1


Id=4
Name=Mister A
Email=mistera@xxx.com
Participation =1
week=1

Id=5
Name=Mister A
Email=mistera@xxx.com
Participation =1
week=1

Id=6
Name=Mister A
Email=mistera@xxx.com
Participation =1
week=1

id=7
Name=Mister A
Email=mistera@xxx.com
Participation=1
week=2


id=8
Name=Mister A
Email=mistera@xxx.com
Participation=1
week=2

id=9
Name=Mister A
Email=mistera@xxx.com
Participation=1
week=2

id=10
Name=Mister A
Email=mistera@xxx.com
Participation=1
week=2

id=11
Name=Mister A
Email=mistera@xxx.com
Participation=1
week=2

id=12
Name=Mister A
Email=mistera@xxx.com
Participation=1
week=2
Hi,

Please try this..

SELECT Name, Email , week, COUNT(*) cnts FROM table
GROUP BY Name ,Email,Week

Open in new window


Hope it helps!
it is not working...
What u r getting?
the same thing
When you receive an answer and it does not work - please can you post more than a message "It is not working". We cannot see your code so to help you you need to help us.

Post the code that you have tried along with the output you received.
I told you before what I get...
<table width="100%" border="0" align="center" cellspacing="5" cellpadding="0" class="CadreBlanc">
        <tr>
          <td align="left" valign="top"><table width="100%" border="0" align="center" cellpadding="0" cellspacing="0" id="TableauJaquery3" class="Tableau">
            <thead>
              <tr>
                <td align="center"><strong>Nom et prénoms</strong></td>
                <td width="300" align="center"><strong>Email</strong></td>                
                <td width="200" align="center"><strong>Date</strong></td>
                <td width="100" align="center"><strong>Participations</strong></td>
              </tr>
            </thead>
            <tbody>
              <?php $Stat = $pdo->query("SELECT Count(Semaine) AS totalWeeks,Pseudo,Email,DateParticipation FROM tb_jeu_du_jeudi_reponses GROUP BY Semaine,Pseudo, Email");
		  while($data = $Stat->fetch(PDO::FETCH_ASSOC)){ 
?>
              <tr>
                <td align="left" valign="middle"><?php echo ucwords($data['Pseudo']); ?></td>
                <td align="left" valign="middle"><?php echo $data['Email']; ?></td>
                <td align="center" valign="middle"><?php echo ucwords(strftime("%d %B %Y à %H:%M:%S", strtotime($data['DateParticipation']))); ?></td>
                <td align="center" valign="middle"><?php echo $data['totalWeeks']; ?></td>
              </tr>
              <?php ;} ?>
            </tbody>
          </table></td>
        </tr>
      </table>

Open in new window

What output you get from my query?
With your code, it is showing me the number of time he played which is in my example ;


Mister A- mistera@xxx.com- 6(Week 1)
Mister A- mistera@xxx.com- 5(Week 2)

Same as for Julian
With your example u should 6 for both the weeks. Could u please give e screen shot of how u r doing this?
But it is not what I want... What I want is to show this:
Mister A- mistera@xxx.com- 2(showing the two weeks)
Here is a working sample - please let us know if this is what you are looking for
<?php
require_once("dbconfig.php");
$dsn = "mysql:dbname={$db};host={$host}";
$pdo = new PDO($dsn, $user, $password);
$query = <<< QUERY
SELECT 
  COUNT(*) AS totalWeeks, 
  `Name`, 
  `Email`, 
  `Week` 
FROM 
  `28996320` 
GROUP BY 
  `Name`, `Email`, `Week`;  
QUERY;
$Stat = $pdo->query($query); 
?>
  <table width="100%" border="0" align="center" cellspacing="5" cellpadding="0" class="CadreBlanc">
    <thead>
      <tr>
        <th>Name</th>
        <th><strong>Email</th>
        <th>Participation(Week)</th>
      </tr>
    </thead>
    <tbody>
<?php 
  while($data = $Stat->fetch(PDO::FETCH_ASSOC)){ 
?>
    <tr>
      <td align="left" valign="middle"><?php echo ucwords($data['Name']); ?></td>
      <td align="left" valign="middle"><?php echo $data['Email']; ?></td>               
      <td align="center" valign="middle"><?php echo $data['totalWeeks']; ?></td>
    </tr>
<?php ;} ?>
        </tbody>
</table>

Open in new window


Working sample here
this is what I don't want to show.
What I want to show is:



Name                              Email                                          Participation(Week)
Mister A                     mistera@xxx.com                                                2

It is 2 because he played for week 1 and week 2
Updated to included week labels
<?php
require_once("dbconfig.php");
$dsn = "mysql:dbname={$db};host={$host}";
$pdo = new PDO($dsn, $user, $password);
$query = <<< QUERY
SELECT 
  COUNT(*) AS totalWeeks, 
  `Name`, 
  `Email`, 
  `Week` 
FROM 
  `28996320` 
GROUP BY 
  `Name`, `Email`, `Week`;  
QUERY;
$Stat = $pdo->query($query); 
?>
 <table width="100%" border="0" align="center" cellspacing="5" cellpadding="0" class="CadreBlanc">
    <thead>
      <tr>
        <th>Name</th>
        <th><strong>Email</th>
        <th>Participation(Week)</th>
      </tr>
    </thead>
    <tbody>
<?php 
  while($data = $Stat->fetch(PDO::FETCH_ASSOC)){ 
?>
    <tr>
      <td align="left" valign="middle"><?php echo ucwords($data['Name']); ?></td>
      <td align="left" valign="middle"><?php echo $data['Email']; ?></td>               
      <td align="center" valign="middle"><?php echo $data['totalWeeks'] . " (Week {$data['Week']})" ?></td>
    </tr>
<?php ;} ?>
        </tbody>
</table>

Open in new window

Updated sample here
Update question:

Participation(Week) must be the total of week that he played.
Missed your last post - that makes it clearer

Here is the query you need
SELECT 
  COUNT(*) AS totalWeeks, 
  `Name`, 
  `Email` 
FROM (
    SELECT DISTINCT 
      `Name`, 
      `Email`, 
      `Week` FROM `table`) A
GROUP BY 
  A.Name, A.Email;

Open in new window


EDIT: Added field name to COUNT
ASKER CERTIFIED SOLUTION
Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you very much !!!!!!!!!!!!!!!!!! You saved my day ! All my respect !
You are welcome.