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 :
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:)