comparing mysql

viveleroi0

How would I be able to compare results from one field, with the other results from that field to find any duplicate entries? I know how to do this with two different tables, but the field needs to be compared to all others in the same table. I am using mySQL.

toma42

Not exaclty sure what you're trying to do, but check out the GROUP BY clause for sql statements...that may help you out.

viveleroi0

Let me try to explain a little better:

There is a table called Products in a mySQL Database, within this table are many fields, one which is called ProductName. I want to write a script that compares all entries in that ProductName field, to each other, noting any matches.

The difficultly comes from the fact that the two things I am comparing, are basically hundreds of rows in the same column.

Hundreds of entries, but in the same field. Say I have 400 products, I want to compare the existing 400 names of those products, to themselves to find any matches.

Does that make more sense?

laserlight

I'm not sure what is the best way to do it, but one possible solution is:

Initialize array duplicate_array
For i = 1 to i = max_rows in table
	Get ProductName for row i
	if ProductName not in duplicate_array
		Select from table where ProductName = Productname of row i
		if number of rows returned greater than 1
			store ProductName in duplicate_array

Then duplicate_array will contain the duplicates.
Might have to modify this a little if you want to know how many of each are duplicated.

laserlight

This might be a possible implementation of the pseudocode:

<?php
function getDuplicates($table, $field) {
	$dup_array = array();
	$query = mysql_query("SELECT $field FROM $table");
	while ($row = mysql_fetch_array($query)) {
		$fieldval = $row[$field];
		$key = array_search($fieldval, $dup_array);
		//Prior to PHP 4.2.0, array_search() returns NULL, not FALSE on failure
		if (!($key == NULL || $key == false)) {
			$sql = mysql_query("SELECT $field FROM $table WHERE $field='$fieldval'");
			if (mysql_num_rows($sql) > 1) {
				$dup_array[] = $fieldval;
			}
		}
	}
	return $dup_array;
}
?>

Then one would use

$my_array = getDuplicates('Products', 'ProductName');

viveleroi0

I've tried playing around with this code, but I must not be advanced enough in php yet to be able to figure out where I am going wrong. Basically no matter what I change I get parse errors all of the time. I know its connecting to the DB correctly, it's just something above aint working. Any ideas?

BlackCatt

Are you looking to try to get all the duplicates passed back in 1 SQL statement or seperate SQL statements like the example above?

To get all the duplicates passed back in a single SQL statement simply use Group By and Having like this.

Select Number
from Phone
group by Number
having count(Number) > 1;

This will give you back any Numbers that have more than 1 record in Phone. Optionally you could also return how many times each Number appears by replacing "Select Number " with "Select Number, count(Number) as cnt ". You can also add any necessary where clause.

Hope this helps.

viveleroi0

Wow that last part is so easy... that's better. Now, another question. I have the following sql query in the script:

SELECT *, Count(ProdDesc) FROM Products Group By ProdDesc Having Count(ProdDesc) > 1

Now, how can I extract the count(proddesc) value? Like say there are 3 counts returned, phpMyAdmin will show you that value. How can I get that value to display on the page along with the results?

drawmack

select a.ID from tblWhatever as a, tblWhatever as b where a.ProdDesc = b.ProdDesc and a.ID != b.ID AND a.ID > b.ID ORDER BY a.ID

there you go a nice list of all the IDs with a repeated product description, excluding the first one so in the end you have the list of ids to delete.

BlackCatt

Originally posted by viveleroi0
SELECT *, Count(ProdDesc) FROM Products Group By ProdDesc Having Count(ProdDesc) > 1

Now, how can I extract the count(proddesc) value? Like say there are 3 counts returned, phpMyAdmin will show you that value. How can I get that value to display on the page along with the results?

I assume you are wanting to reference that count # via php or something but are having trouble because there is no column name? All you have to do is aliase your count as a column by modifing your query to:

SELECT *, Count(ProdDesc) as ProdDescCount FROM Products Group By ProdDesc Having Count(ProdDesc) > 1

and reference the column by ProdDescCount in php. I just made up the name ProdDescCount but you could name it whatever you want and reference that column.

Also, not to be picky here but a little helpful hint from an optimization view, you don't want to do SELECT * unless you need every column from every table that your selecting from. In your case I would probably just do a SELECT <PrimaryKey column(s)>, ProdDesc, Count(ProdDesc) FROM Products.... or SELECT ProdDesc, Count(ProdDesc) FROM Products.... but thats just being nit picky.