Compare and delete rows from a table

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
gstefan
Forum Newbie
Posts: 11
Joined: Thu Aug 21, 2003 2:29 pm

Compare and delete rows from a table

Post 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?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.
User avatar
gstefan
Forum Newbie
Posts: 11
Joined: Thu Aug 21, 2003 2:29 pm

Post by gstefan »

I want to keep only one of them no matter what.
I'll try to do how you said.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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
Post Reply