Selecting only rows which have a field appearing more than once?

McMurphy

Hi ppl,

I'm trying to come up with a query that will display only the rows which their userIP field value appears more than once. My goal is to spot duplicates and values which appear even more than twice. A friend suggested using

"SELECT name, count(userIP) from sometable group by name order by count(userIP) desc";

But I get an error message saying "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result". Can someone plz help?

Cheers,

kevinsequeira

post the code you are using

reg
kevin

McMurphy

Hi Kevin,

Here it is:


$query = "SELECT name, count(userIP) from sometable group by name order by count(userIP) desc";
$usersList = mysql_query($query);
echo "<table class='list' align=center width=500>";
while($user = mysql_fetch_array($usersList)){
		echo "<tr><td class='listEntry'>".$user['id']."</td><td class='listEntry'>".$user['name']."</td><td class='listEntry'><b>".$user['userIP']."</b></td></tr>";
}
echo "</table>";

?>

I know that the query doesn't include the ID and userIP fields but still it do the while... what am I missing here?

Cheers,

kevinsequeira

try changing query to

$query = "SELECT name, count(userIP) from sometable group by name order by 2 desc";

reg
kevin

McMurphy

Hi Kevin,

Thanks but I keep getting the same erorr message :/

kevinsequeira

try running the query directly in your database. do you get any result

reg
kevin

McMurphy

Hi,

I get:
Unknown column 'userIP' in 'field list'

Thnx,

kevinsequeira

well, then you dont have a column called userIP in the table !!!
what are the columns in your table called ?

reg
kevin

McMurphy

Auch! userIP was the variable name I used in my PHP script... the name of the column is 'ip'.... it works and out puts the name but when I tried to modify it to get the IP as well as the name I got the same error again..

$query = "SELECT name,ip count(ip) from sometable group by name,ip order by 2 desc";

Can you plz explain what the query does so perhaps I can figure out myself and understand what it is I'm doing here?

Thanks a lot,