Delete duplicates and faulty entries MySQL database
Remove ‘duplicate’ entries:
We have an hotelsite were we promote different merchants. For this we automatically load productfeeds of every merchant into our MySQL database (databasename is ‘actiehotels’ and table is ‘affiliSt_products1′) every night using PHP. With 1 specific feed for the merchant “Weekendjeweg” (column ‘merchant’) we have ‘duplicate’ entries. There are entries with the same productid (column ‘merchantProdID’), one with an higher price (column ‘prodPrice’) (hotel for 2 nights) then the other (hotel for 1 night). The duplicate entries with the highest price for this merchant should be deleted.
Half the price:
Then for the same merchant “Weekendjeweg” with the remaining entries the price should halved (divided by two). So for example when the price is 75.00 it should become 37.50.
Remove ‘faulty’ entries:
Then the faulty entries of all merchants should be deleted. The entry is faulty when:
– When entries contain letters (a-z, A-Z) in the column ‘merchantProdID’, except for the merchants “Fletcher” and “Hotelmoment” (they have an ID like ‘F01′, ‘F02′, etc.)
– When entries equal ‘1′ in the column ‘merchantProdID’. So when an entry with merchantProdID is 1 it should be deleted (entries with merchantProdID for example ’11’ or ‘101′ should not be deleted).
PHP / MySQL
We want to have the above assignment programmed in PHP (with MySQL) so we can run this as a cronjob after the productfeeds are updated. We’re using PHP 5.2.3 on Apache 2.0 and MySQL 5.0.46 on a linux server. An export of the database (table ‘affiliSt_products1′ only) is attached. If there are any questions please let me know.
