Page 1 of 1

Compare and delete rows from a table

Posted: Sat Sep 20, 2003 1:25 pm
by gstefan
I have a table with a lot of lines. Some of the lines are twice. All I want to do is to compare and erase some of them and to keep only one.
I use the that but my table was erased completly.

Code: Select all

<?php
mysql_connect('localhost','root','');
mysql_select_db('site');
$query="select * from funnysms where sms_cat='doublemeaning'";
$result=mysql_query($query);
$query2="select * from funnysms where sms_cat='doublemeaning'";
$result2=mysql_query($query2);
while ($row=mysql_fetch_array($result)){
while ($row2=mysql_fetch_array($result2)){
if ($row2['sms_desc']==$row['sms_desc']){
$query3="delete from funnysms where ".$row2['sms_desc']."=".$row['sms_desc'];
mysql_query($query3);
}
}
mysql_data_seek($result2,0);
}
?>
[mod_edit: added

Code: Select all

tags][/size]

So where I'm wrong? Can I do it in other way to do what I want?

Posted: Sat Sep 20, 2003 4:34 pm
by volka
since your inner loop iterates through an identical recordset it's obvious that each record matches your condition.
Can you explain in more detail which doublet you want to delete? (might be all doublets, the one beeing inserted before the other or versa vice)

In any case I'd use a join-query to select/delete the doublets.
You might also mark the description field as unique. It will prevent records with the same value for this field from beein inserted.

Posted: Sun Sep 21, 2003 12:17 am
by gstefan
I want to keep only one of them no matter what.
I'll try to do how you said.

Posted: Sun Sep 21, 2003 6:25 am
by volka

Code: Select all

select id, sms_desc, count(*) as doublets
from funnysms
group by sms_desc
having count(*) > 1
this will give you a recordset containing all doublets. id must be a unique/autoincrement field
each record will contain
id - the first occurence of a record having this sms_desc (probably the one you want to keep)
sms_desc - the description that has duplicates (that was your criteria, wasn't it?)
doublets - number of duplicates (including the first occurence)

Then you can delete all rows having that sms_desc unless it has the proper id-value