Re: PHP+MySQL question

On 11-02-16 11:37 AM, דניאל דנון wrote:

Hi. I have a table called "images" with 4 columns - image_id, item_name, image_url, image_views (Where image_id is UNIQUE and AUTO-INCREMENT). Sometimes, there might be many items with the same name (but not with the same url). I want to make sure that each "item name" has at most 3 images (and therefore I need to delete the rest). The problem is that I want to keep the images with the most views. I've tried to look for efficient solutions either in MySQL or in PHP, but they are mostly very resource-intensive, Such as selecting all different names in PHP (using GROUP BY), then, for each name, doing DELETE FROM images WHERE item_name = 'ITEM-NAME-HERE' ORDER BY image_views ASC LIMIT (here some sub-query with count on how many rows have the name ITEM-NAME-HERE minus 3). I'd be glad if anyone could help me or point me to the right direction.

I'd use a cron job to manage the purging process... and off the top of my head I'd probably go the following route: Get the list of images with more than 3 of the same name: SELECT item_name, SUM( 1 ) AS total FROM images HAVING total > 3; Get the 3 best images for each image returned above: SELECT image_id FROM images WHERE image_name = '[[NAME]]' ORDER BY image_views DESC. (Make sure to quote your criteria properly in the above-- this is pseudo codish). Now delete the laggards using the ID we just retrieved: DELETE FROM images WHERE image_id NOT IN ([[ID_LIST]]). That should get you to a decent solution. Cheers, Rob. -- E-Mail Disclaimer: Information contained in this message and any attached documents is considered confidential and legally protected. This message is intended solely for the addressee(s). Disclosure, copying, and distribution are prohibited unless authorized.