to merge two code

[zodehala]

i can list all username in user table using following code

$query = mysql_query("SELECT * FROM user");
while($row = mysql_fetch_array($query)){
echo $row ["username"]."<br/>";
}



i can find duplicate record in array using following code

$array = array('a', 'b', 'a', 'a', 'c', 'a', 'd', 'a', 'c', 'd','e','e','e','e','e','e','e','e','e','e','e','e');
$answer = array_count_values($array);
$i=0;
foreach($array as $value){
if($answer[$array[$i]] >2){
echo $value.$answer[$array[$i]]."<br/>";
}
$i++;

}


in order to find duplicate username i have to merge this two code but i can not :confused::confused::confused::confused:

[shyam]

u don't have to merge the 2 snippets of code at all...all u need to do is change ur query and let mysql do the heavy lifting

$query = mysql_query("SELECT username, count(*) FROM user group by username having count(*) > 1");
while($row = mysql_fetch_array($query)){
echo $row ["username"]."<br/>";
}

[Apothem]

"SELECT * FROM user GROUP BY (COLUMN)"

Replace COLUMN with a column's name that has duplicate records.

Edit: Nevermind, shyam posted already.
On a sidenote, can you explain what HAVING does? MySQL's document is pretty vague.

[zodehala]

if i use following query. it lsit just all record .
SELECT username, count(*) FROM user group by username having count(*) > 1

but i want it list just duplicate record

[Fou-Lu]

"SELECT * FROM user GROUP BY (COLUMN)"

Replace COLUMN with a column's name that has duplicate records.

Edit: Nevermind, shyam posted already.
On a sidenote, can you explain what HAVING does? MySQL's document is pretty vague.

HAVING is pretty much the same as a WHERE, but HAVING is required on aggregate values (count(*) for example). This allows us to check on the count(*) attribute for each result set. Think of a where that works in combination with several results, unlike a standard where that only sees a single row. It can only be used with group by if I'm not mistaken. Basic SQL is very simple to use, but advanced SQL does take some practice.
I do find that having is poorly documented. Oracle I seem to recall did a good job with their documentation.


$sQry = "SELECT username, count(*) FROM user GROUP BY username HAVING count(*) > 1";
$qry = mysql_query($sQry) OR die(sprintf("Cannot execute query (%d): %s", mysql_errno(), mysql_error()));
while ($rows = mysql_fetch_array($qry))
{
echo "Duplicate name: " . $rows['username'] . "<br />";
}

Works for me, I only get results that are duplicates. Perhaps you just have so many duplicates it appears that it is not functioning correctly.