MySQL Forums
Forum List  »  Full-Text Search

Searching a field to check if multiple images are in use
Posted by: Stephen Renton
Date: September 03, 2009 02:57PM

I am working on a content management system where the user can upload images to an image folder on the server. The details of each uploaded image are NOT stored in the MySQL database. The user can then select an image to be included in a content field, using a rich text editor.

What I want to do, within the content management system, is display a list of the images that the user has uploaded, and next to each image, display a flag that indicates whether the image is in use or not. This is relatively simple to do, I just use the following code for each image:

select count(pageid) from page where webpagecontent like '%imagename1.jpg%';

The problem with this is that if the user has uploaded 100 images, then this query will be run 100 times, once for each image, whenever the user displays their list of images, and that is not very efficient! How can I combine all of this into a single query that returns a list of the images that are in use?

I can do this with the following code:
select 'imagename1.jpg' from page where webpagecontent like '%imagename1.jpg%'
union
select 'imagename2.jpg' from page where webpagecontent like '%imagename2.jpg%'
union
select 'imagename3.jpg' from page where webpagecontent like '%imagename3.jpg%'
union
select 'imagename4.jpg' from page where webpagecontent like '%imagename4.jpg%';
etc

This returns a list of the images that are in use, but if I have 100 images I am still having to run 100 queries, albeit combined into one big query. Is this any more efficient than having 100 separate queries? Any suggestions for the best way to do this?

Thanks.

Options: ReplyQuote


Subject
Views
Written By
Posted
Searching a field to check if multiple images are in use
4084
September 03, 2009 02:57PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.