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