SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Hybrid View

  1. #1
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Double update in one query

    Is it possible, when using a session to upload pictures, to set the initial (int) value for a field for the first image to 1 while for the rest of the images the value for this field will be set to 0?
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    is this really a mysql question? i don't understand the part about loading multiple images

    what language are you using, coldfusion? php? can we see the INSERT statement?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I just see that the title has nothing to do with the question. That's a long story Anyway. I'm not sure either if I should have asked this question here or in the Coldfusion forum (the language I use)

    First of all here is the table in question:
    Code MySQL:
    CREATE TABLE IF NOT EXISTS `product_photos` (
      `photo_id` smallint(4) unsigned NOT NULL auto_increment,
      `product_id` smallint(4) unsigned NOT NULL,
      `thumbnail` varchar(64) default NULL,
      `featured` varchar(64) default NULL,
      `photo` varchar(64) default NULL,
      `isActive` tinyint(1) NOT NULL default '0',
      PRIMARY KEY  (`photo_id`),
      KEY `product_id` (`product_id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
    The table is related to the table products. A product can have multiple photos mainly used on the product detail page. I have the field isActive which need to represent the the image which is visible on the product listing page. When set to 1. I have build a functionality where the site manager later can choose which picture should be the active one. But initial that should be the first picture uploaded for a product (That's how they would like to have it)

    This is the query:
    Code MySQL:
    INSERT INTO 
        property_photos
        ( 
          property_id
        , thumbnail
        , featured
        , photo
        )
    VALUES
        (
          #session.product#
        , '#cffile.ServerFileName#.#cffile.ServerFileExt#'
        , '#cffile.ServerFileName#.#cffile.ServerFileExt#'
        , '#cffile.ServerFileName#.#cffile.ServerFileExt#'
        )

    As you can see, I didn't use an insert for the isActive field. (Default value is set to 0) My question is, if there is a way to set the Value for the first uploaded picture related to a product to isActive = 1 while the photos uploaded after that one should remain to have the default 0 value
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i'm sure that you made a copy/paste error when you showed the same CFFILE property being used to populate the thumbnail, featured, and photo columns with the exact same value

    anyhow, try this --
    Code:
    INSERT 
      INTO property_photos
         ( property_id
         , thumbnail
         , featured
         , photo 
         , isActive )
    SELECT #session.product#
         , '#cffile.ServerFileName#.#cffile.ServerFileExt#'
         , '#cffile.ServerFileName#.#cffile.ServerFileExt#'
         , '#cffile.ServerFileName#.#cffile.ServerFileExt#'
         , CASE WHEN COUNT(*) = 0
                THEN 1 
                ELSE 0 END
      FROM property_photos
     WHERE property_id = #session.product#
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i'm sure that you made a copy/paste error when you showed the same CFFILE property being used to populate the thumbnail, featured, and photo columns with the exact same value
    I use cfimage to get different dimensions from the same photo. Used in different parts of the website. So actually it is the same image.

    Edit: But now that I think about it. This is overdone isn't it I need just one field name and on the different locations I just drag it from one of the different folders(they are categorized in three different folders)

    I gonna give your idea a try, it looks promising. I let you know
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  6. #6
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i'm sure that you made a copy/paste error when you showed the same CFFILE property being used to populate the thumbnail, featured, and photo columns with the exact same value

    anyhow, try this --
    Code:
    INSERT 
      INTO property_photos
         ( property_id
         , thumbnail
         , featured
         , photo 
         , isActive )
    SELECT #session.product#
         , '#cffile.ServerFileName#.#cffile.ServerFileExt#'
         , '#cffile.ServerFileName#.#cffile.ServerFileExt#'
         , '#cffile.ServerFileName#.#cffile.ServerFileExt#'
         , CASE WHEN COUNT(*) = 0
                THEN 1 
                ELSE 0 END
      FROM property_photos
     WHERE property_id = #session.product#
    I was just to late to edit

    This works great Rudy. Thanks a lot
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  7. #7
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy. I have one last question about this case method. I was wondering if this would work in a UPDATE statement as well? Like I said, I built a functionality where the site administrator is able to change the active photo. For this I use radio buttons(photo_id). I use three queries to do so. One to check if there are any photos for that product where isActive = 1
    Code MySQL:
    SELECT
        photo_id 
      , thumbnail
    FROM
        product_photos
    WHERE  
        product_id = #Url.product#
    AND 
        isActive =  1
    Then I use a recordcount(Coldfusion) to see if this is true or false. If this turns out to be true I update the isActive status for this photo to 0
    Code MySQL:
    UPDATE 
        product_photos
    SET 
        isActive = 0
    WHERE
        photo_id =  firstQueryName.photo_id
    And do anoyher update based on the selected radio button after that.
    Code MySQL:
    UPDATE 
        product_photos
    SET 
        isActive = 0
    WHERE
        photo_id =  form.photo_id
    This is working, but I'm always looking for improvement.
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    your last query should be setting isActive to 1 (your explanation is fine)

    i would combine all three of your steps into one --
    Code:
    UPDATE product_photos
       SET isActive =
           CASE WHEN photo_id = #form.photo_id#
                THEN 1
                ELSE 0 END
     WHERE product_id = #Url.product#
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    your last query should be setting isActive to 1 (your explanation is fine)

    i would combine all three of your steps into one --
    Code:
    UPDATE product_photos
       SET isActive =
           CASE WHEN photo_id = #form.photo_id#
                THEN 1
                ELSE 0 END
     WHERE product_id = #Url.product#
    Oh so easy. I was near though, This is what I had:
    Code MySQL:
    UPDATE 
         product_photos
    SET
         CASE WHEN 
         [B]isActive[/B] = #Form.photo_id#
         THEN 1 
         ELSE 0 END
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •