checking for double id

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
pelegk2
Forum Regular
Posts: 633
Joined: Thu Nov 27, 2003 5:02 am
Location: Israel - the best place to live in after heaven
Contact:

checking for double id

Post 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
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

Something like: "SELECT ... WHERE col1=col2 OR ..etc"

You might want to take a look at this tutorial.
User avatar
pelegk2
Forum Regular
Posts: 633
Joined: Thu Nov 27, 2003 5:02 am
Location: Israel - the best place to live in after heaven
Contact:

but its not a diffrent col!

Post by pelegk2 »

its all in the same col
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post 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>';

?>
Last edited by McGruff on Tue Aug 09, 2005 2:48 pm, edited 1 time in total.
User avatar
pelegk2
Forum Regular
Posts: 633
Joined: Thu Nov 27, 2003 5:02 am
Location: Israel - the best place to live in after heaven
Contact:

what

Post by pelegk2 »

wat execlly does this mean :

Code: Select all

$dupes&#1111;$id]&#1111;]
penguinboy
Forum Contributor
Posts: 171
Joined: Thu Nov 07, 2002 11:25 am

Post 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 */));
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

select *
from table
group by id_column
having count(id_column) > 1
User avatar
pelegk2
Forum Regular
Posts: 633
Joined: Thu Nov 27, 2003 5:02 am
Location: Israel - the best place to live in after heaven
Contact:

Post by pelegk2 »

thanks alot timvw
so simple answer:)
Post Reply