php script to remove duplicates on the basis of some field value

[system]

Hi ,
I am working on a script currently to remove duplicate records from database,
There are some conditions with this as follows:

Suppose the table looks like:
ID Name A/C No exp_date
1 xxx 168 12may2014
2 xxx 0 0
3 xxx 168 12may2014
etc…

So first i need to check if duplicate exists:
if yes
chcek if A’C no is not null or exp_date>=currentdate, if foolows the condition keep this entry and delete the othere entry.
if two same entries havve same A/CNo and exp_date then keep the max_id and delete lower1.

Hope i am able to put my query clearly.I need it urgently …Please help.

[Strider64]

If you make the column variable (what it is for A/C No I think) unique in MySQL then you wouldn’t have to worry about duplicates. Or better yet just have the unique id (using it as a order) or have an unique order/part number to prevent duplicates. Or have two separate tables one for customers and one for the orders or part numbers, that way you can just put the cust name in the invoice(order). I think the last one would be the best solution. I personally like handling things like duplicates on the database side, heck that to me is where the real power of a database comes from.

[ErnieAlex]

Strider64 started you off, but you did not reply as yet.
To add to his explaination…

First , when you query the database, you would use something like:

SELECT * FROM table_name

But, to pick out the duplicates, you must thing backwards. Select the non unique ones. Like:

SELECT * FROM table_name WHERE Name NOT UNIQUE

(Of course that is if you are talking about duplicate names and not ID numbers.)
OR: SELECT * FROM table_name WHERE Name NOT UNIQUE ORDER BY Name to sort it…

This would give you a recordset containing all of the names that have more than one entry.
Next you would have to loop thru them running your tests to see which you do not want and
delete them using the ID number.

Hope that helps simplify it some. Good luck…