Jump to content

formatting php count results


Justafriend
Go to solution Solved by Barand,

Recommended Posts

$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);  
foreach($dbh->query('SELECT username,COUNT(*)  
FROM ballot  
GROUP BY username') as $row) {  
echo "<tr>";  
echo "<td>" . $row['username'] . "</td>";  
echo "<td>" . $row['COUNT(*)'] . "</td>";  
echo "</tr>";   
}  

the result is coming like this

habs4stanley1 demo2 Habsfan4life2

 

​I need it to   put it   like this

 

​habs4stanley   1

​demo                2

​habsfan4life     2

 

​any help is greatly appreciated

 

 

Link to comment
Share on other sites

Combining the previous two suggestions

 

 

<?php
 
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
$query = 'SELECT username, COUNT(*) as number
          FROM ballot
          GROUP BY username
          ORDER BY username';
 
$output = "";
foreach($dbh->query($query) as $row)
{
    $output .= "<tr>\n";
    $output .= "<td>" . htmlspecialchars($row['username']) . "</td>\n";
    $output .= "<td> . htmlspecialchars($row['number']) . "</td>\n";
    $output .= "</tr>\n";
}
 
?>
<html>
<head></head>
 
<body>
 
<table>
<?php echo $output; ?>
</table>
 
</body>
</html>
Link to comment
Share on other sites

thank you all for your jhelp now i have one other ? the order by name is making it sort by count then name I thought            ORDER BY username'; would make it alphabetical  but its going by number of entries then alphabetical   easiest way to explain what I'm looking for  is this is how its   resulting

demo 2

Habsfan4life 2

habs4stanley 1

 

what id like is that it goes

 

demo              2

habs4stanley  1

Habsfan4life   2

Edited by Justafriend
Link to comment
Share on other sites

Your usernames appear have weird non-printing characters in them which is preventing the correct ordering.

 

Copy/pasted one of your usernames into this script

$username = '​habs4stanley ';
var_dump($username);
echo '<br>';
for ($i=0, $k=strlen($username); $i<$k; $i++) {
    printf('%2X ', ord($username[$i]));
}

and the result was

string(16) "​habs4stanley " 
E2 80 8B 68 61 62 73 34 73 74 61 6E 6C 65 79 20
Link to comment
Share on other sites

 

Your usernames appear have weird non-printing characters in them which is preventing the correct ordering.

 

Copy/pasted one of your usernames into this script

$username = '​habs4stanley ';
var_dump($username);
echo '<br>';
for ($i=0, $k=strlen($username); $i<$k; $i++) {
    printf('%2X ', ord($username[$i]));
}

and the result was

string(16) "​habs4stanley " 
E2 80 8B 68 61 62 73 34 73 74 61 6E 6C 65 79 20

 

its when I run it through the code and paste it here if I copy from the list on results page and ran it through your script I get

string(15) "​habs4stanley" 
E2 80 8B 68 61 62 73 34 73 74 61 6E 6C 65 79 
string(12) "Habsfan4life"
48 61 62 73 66 61 6E 34 6C 69 66 65
string(4) "demo"
64 65 6D 6F

but the names are still in the same order as above

Link to comment
Share on other sites

  • Solution

There is definitely something going on. If I create a test table your code is  fine

mysql> SELECT id, username FROM ballot;
+----+--------------+
| id | username     |
+----+--------------+
|  1 | habsfan4life |
|  2 | demo         |
|  3 | habs4stanley |
|  4 | habsfan4life |
|  5 | demo         |
+----+--------------+
5 rows in set (0.00 sec)

mysql> SELECT username, COUNT(*)
    -> FROM ballot
    -> GROUP BY username;
+--------------+----------+
| username     | COUNT(*) |
+--------------+----------+
| demo         |        2 |
| habs4stanley |        1 |
| habsfan4life |        2 |
+--------------+----------+
3 rows in set (0.00 sec)
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.