On 11-02-16 11:55 AM, Robert Cummings wrote:
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.
Oops... that should have a LIMIT clause on it:
SELECT image_id FROM images WHERE image_name = '[[NAME]]' ORDER BY
image_views DESC LIMIT 3
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.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
