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)
It is showing to me:
Mister A- mistera@xxx.com - 1
Mister A- mistera@xxx.com - 2
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>
But it is not workingIt is showing to me:
Mister A- mistera@xxx.com - 1
Mister A- mistera@xxx.com - 2
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)
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
ASKER
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
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..
Hope it helps!
Please try this..
SELECT Name, Email , week, COUNT(*) cnts FROM table
GROUP BY Name ,Email,Week
Hope it helps!
ASKER
it is not working...
What u r getting?
ASKER
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.
Post the code that you have tried along with the output you received.
ASKER
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>
What output you get from my query?
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)
Same as for Julian
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?
ASKER
But it is not what I want... What I want is to show this:
Mister A- mistera@xxx.com- 2(showing the two weeks)
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
Working sample here
<?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>
Working sample here
ASKER
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
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>
Updated sample here
ASKER
Update question:
Participation(Week) must be the total of week that he played.
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
EDIT: Added field name to COUNT
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;
EDIT: Added field name to COUNT
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much !!!!!!!!!!!!!!!!!! You saved my day ! All my respect !
You are welcome.
Open in new window
You don't need a second query - here is your updated codeOpen 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.