Justafriend Posted January 15, 2016 Share Posted January 15, 2016 $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 Quote Link to comment Share on other sites More sharing options...
benanamen Posted January 15, 2016 Share Posted January 15, 2016 (edited) echo "<tr><td>{$row['username']} {$row['COUNT(*)'] }</td></tr>"; @Jaques1 will tell you about escaping the output. I am just giving you the quick and dirty. Edited January 15, 2016 by benanamen Quote Link to comment Share on other sites More sharing options...
Barand Posted January 15, 2016 Share Posted January 15, 2016 Your output is missing "<table>..</table> tags. You should have a structure like below, where all the <tr>..</tr> rows are inside a table <table> <tr> <td>User1</td> <td>5</td> </tr> <tr> <td>User2</td> <td>3</td> </tr> </table> Better to learn HTML before embarking on PHP Quote Link to comment Share on other sites More sharing options...
Psycho Posted January 15, 2016 Share Posted January 15, 2016 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> Quote Link to comment Share on other sites More sharing options...
Justafriend Posted January 15, 2016 Author Share Posted January 15, 2016 (edited) 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 January 15, 2016 by Justafriend Quote Link to comment Share on other sites More sharing options...
Barand Posted January 15, 2016 Share Posted January 15, 2016 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 Quote Link to comment Share on other sites More sharing options...
benanamen Posted January 15, 2016 Share Posted January 15, 2016 @Psycho, why did you start with an empty $output? Quote Link to comment Share on other sites More sharing options...
Barand Posted January 15, 2016 Share Posted January 15, 2016 If you don't, the first time you try to append to it (as it does in the loop) you will get an "Undefined variable". Plus you want to make sure it is empty before you start appending. Quote Link to comment Share on other sites More sharing options...
Justafriend Posted January 16, 2016 Author Share Posted January 16, 2016 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 Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted January 16, 2016 Solution Share Posted January 16, 2016 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) Quote Link to comment Share on other sites More sharing options...
Justafriend Posted January 19, 2016 Author Share Posted January 19, 2016 i emptied the table now its working properly ty for your help Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.