Web Analytics Made Easy -
StatCounter question about sql - CodingForum


No announcement yet.

question about sql

  • Filter
  • Time
  • Show
Clear All
new posts

  • question about sql


    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
    image01='foo' OR
    image02='foo' OR
    image03='foo' OR
    image04='foo' OR
    image05='foo' OR
    image06='foo' OR

    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.


  • #2
    Are you using MySQL?
    CodingForum Supreme Overlord
    All Hail Spookster


    • #3
      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.
      Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html