PHP+MySQL question

From: Date: Wed, 16 Feb 2011 16:37:09 +0000
Subject: PHP+MySQL question
Groups: php.general 
Request: Send a blank email to php-general+get-311381@lists.php.net to get a copy of this message
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. Daniel. -- Use ROT26 for best security

« previous php.general (#311381) next »