I have a few ideas rolling around in the back of my head, but what do you guys think is the best way to check for duplication in a sql db? Just check to see if there are any rows where NAME is the same. Its getting to be a decent sized database so I figured I would ask the experts so its a little more efficient.

Thanks!

SELECT primary_key_id, count(name) as total_uses FROM table_name WHERE total_uses > 1

I believe will do it for you. The use of the total_uses alias may fail -- I didn't test that, but it does give the overall idea.

almost Jeremy...
It's not where total_uses>1 but "having total_uses>1".
And often, you will need to do a "group by"

select count(name) as cnt
from tbl
having count(name)>1
group by name

Thanks Tripy. I always have a hell of a time when I alias something and need to refer to it. Never had a chance to formally study DB's and SQL in college. Have just hacked my way through MySQL (but, to be fair, I don't do bad hacking MySQL's language structure ).

EDIT: Oh, I added a primary key field so that the results can be cross-referenced.

DOUBLE-EDIT: Ah, but the primary key won't work there because of the grouping you suggest, so one would need to select the field being grouped on too, right?

The HAVING should come after the GROUP BY.

Wow good thing I asked. I was thinking of some crazy scheme using arrays and sorting the arrays and.... well it wouldve been a mess.

Thanks!

So.... what do I do next?

I have this

$result=mysql_query("select count(name) as cnt from mreleased group by name having count(name)>1") or die(mysql_error());
while($row = @mysql_fetch_array($result))
	{ 
	echo $row['name']." - ".$row['idnum'];
	}

It works fine, but it wont output anything except for the "-"

It's not showing those entries b/c you're not selecting them.

BTW: Handling errors is better than dying.

Try this:

PHP Code:

if ($result=mysql_query("select name, idnum, count(name) as cnt from mreleased group by name having count(name)>1"))
  if (mysql_num_rows($result) > 0) {
    while($row = @mysql_fetch_array($result)) { 
      echo $row['name']." - ".$row['idnum'].' had '.$row['cnt'].' entries';
    }
  }
}
if (mysql_errno() != 0) {
  echo 'Error executing query.  MySQL said "'.mysql_error().'".';
} 


Oh crap. You're grouping, so ids are probably not available. Remove references to idnum:

PHP Code:

if ($result=mysql_query("select name, count(name) as cnt from mreleased group by name having count(name)>1"))
  if (mysql_num_rows($result) > 0) {
    while($row = @mysql_fetch_array($result)) { 
      echo $row['name'].' had '.$row['cnt'].' entries';
    }
  }
}
if (mysql_errno() != 0) {
  echo 'Error executing query.  MySQL said "'.mysql_error().'".';
} 


Incredible. Thanks so much for the help. This is clearly beyond my current capabilities . Thanks again!