question about sql

[sgalmeida]

Greetings,

I have a question about SQL, but I d'nt even know If it is possible to do what I want in the way that I want

Here is the scenario:

My system allow users to add news and promotions, each one can contain up to 7 images. For this I have a page addnews.php and addpromotion.php, where I have several fields for user input (some are the 7 <input type=file>).
So I have two tables: table "NEWS" and table "PROMOTION", each one have the fields image01, image02, image03, image04, image05, image06, image07 (these fields are varchar and when they don't hold an image reference, default value is ''). These fields can contain a reference to the table "IMAGES" that is the table for several image information about the images used on news and on promotions.
I also have a page - adminimages.php - where it is possible to delete images from database and disk drive. My problem is to know the SQL statement to do the update of the reference of the deleted image on the tables "NEWS" and "PROMOTION", when I delete de image (I must set the value '' on the field where previously was the value of the image id). What I want is something like:
O que eu quer é algo do género

UPDATE <name_of_table> SET
image01='' OR
image02='' OR
image03='' OR
image04='' OR
image05='' OR
image06='' OR
image07=''
WHERE
image01='foo' OR
image02='foo' OR
image03='foo' OR
image04='foo' OR
image05='foo' OR
image06='foo' OR
image07='foo'


and the sql statement affects only the field that, in fact, holds the valyue 'foo'.

Is there any way to do this, or must I build queries to each field on the table?? In this case that would be 7 queries, that can slow a bit the database proccessing (

Anyone has ideas???

Thank you very much.

Sérgio

[Spookster]

Are you using MySQL?

[raf]

Your db-design is wrong and that is what creating your problem. You should have set up your db in such a way that the images all have their own record. So not these 7 column, but a table with the newsID and then a reference to the image. If one newsitem had 4 images, then you would have four records here. Like

newsID | image

1| test.gif
1| test2.gif
1| test3.gif
1| test4.gif

If you then delete the image, you also delete the row in this table (or you keep it and set a status-column to 'deleted' or whatever) with a simple
DELETE FROM newsimage WHERE image = 'test3.gif'

It wouldn't even matter if you used the same image for 50 newsitems ... ==> stick by the rules, and all problems disappear.


About you current situation : run a select-query to get the records that hold the image-ref in one of there 7 columns. So

SELECT image01, image02, image03, image04, image05, image06, image07 FROM <name_of_table> WHERE WHERE image01='foo' OR
image02='foo' OR image03='foo' OR image04='foo' OR image05='foo' OR image06='foo' OR image07='foo'

then loop through the recordset and check on each of the 7 fields if they contain 'foo' and then run the update.