help on sql query SELECT display

[pixeltrace]

hi,

 

i need help, i have a table that got lots of duplicates.

i need a query that will display all items with duplicates on the username

order by username where in the display will be something like:

 

=========

| id | username  | email

  1    username1 | email1@email.com

  2    username1 | email2@email.com

  3    username2 | email3@email.com

  4.  username2 | email4@email.com

 

hope you could help me with this.

 

thanks!

[GingerRobot]

Hmm, im a bit rusty on more advanced mysql functions...and im fairly sure this can be achieved in a single query. But you could try something like:

 

<?php
$sql = mysql_query("SELECT `username`,COUNT(`username`) FROM `yourtable` GROUP BY `username` HAVING COUNT(`username`) > 1") or die(mysql_error());
//this will find each of the duplicated values for the username, but (i think) will only return one of the values for it - e.g, in the results, you will only find username1 once - so we then cycle through the results to pull the rest of the info from the database
while($row = mysql_fetch_assoc($sql)){
$sql2 = mysql_query("SELECT * FROM `yourtable` WHERE `username`='$row[username]'") or die mysql_error());
while($row2 = mysql_fetch_assoc($sql2)){
echo "$row2[id] || $row2[username] || $row2[email] <br />";
}
}
?>

 

Im not 100% sure this'll work - i've hardly used group and having clauses, but its worth a go. As i say, someone else will probably be able to show you a single query for doing this too.