Page 1 of 1

checking for double id

Posted: Sun Feb 29, 2004 3:10 am
by pelegk2
i have a Db wih the first column which is an id
i have few id's that are more then once in the table
which means i have diffrent rows of data with same id!
how can i make a select file that will give me all the rws with the id number that is more then once?
thanks in advance
peleg

Posted: Sun Feb 29, 2004 4:00 am
by McGruff
Something like: "SELECT ... WHERE col1=col2 OR ..etc"

You might want to take a look at this tutorial.

but its not a diffrent col!

Posted: Sun Feb 29, 2004 4:05 am
by pelegk2
its all in the same col

Posted: Sun Feb 29, 2004 4:36 am
by McGruff
You could write a script that compares adjacent rows as you loop over a result set.

Add an auto-incrementing col with primary key to the table first. That lets you identify rows but carries no other "meaning" - see the normalisation stuff above.

You could create a report something like this. I didn't test, I'm very tired, but it might give you some ideas.

Code: Select all

<?php

$dupes = array();
$temp = false;

// pid is the new column (auto-incrementing integer & primary key)
// id is the col where you have duplicate values
$mysql = "SELECT pid, id FROM table ORDER BY id";
$query = mysql_query($mysql);

while($result = mysql_fetch_array($query))
{
    $id = $result['id'];

    if($id === $temp)
    {
        $dupes[$id][] = $result['pid'];
    
    } else {
    
        $temp = $id;
    }
}
echo '<pre>';
print_r($dupes);
echo '</pre>';

?>

what

Posted: Sun Feb 29, 2004 5:26 am
by pelegk2
wat execlly does this mean :

Code: Select all

$dupes&#1111;$id]&#1111;]

Posted: Sun Feb 29, 2004 11:41 am
by penguinboy
That code is adding a new element to the array;
kinda like:

Code: Select all

$dupes = array($id=>array(/* the new element would go here */));

Posted: Sun Feb 29, 2004 5:52 pm
by timvw
select *
from table
group by id_column
having count(id_column) > 1

Posted: Thu Mar 11, 2004 9:06 am
by pelegk2
thanks alot timvw
so simple answer:)