Double update in one query

Databases
1

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?

2

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?

3

I just see that the title has nothing to do with the question. That’s a long story :slight_smile: 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:


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:


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

4

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 :slight_smile:

anyhow, try this –

INSERT 
  INTO property_photos
     ( property_id
     , thumbnail
     , featured
     , photo 
     , [COLOR="Blue"]isActive [/COLOR])
[COLOR="blue"]SELECT [/COLOR]#session.product#
     , '#cffile.ServerFileName#.#cffile.ServerFileExt#'
     , '#cffile.ServerFileName#.#cffile.ServerFileExt#'
     , '#cffile.ServerFileName#.#cffile.ServerFileExt#'
     , [COLOR="blue"]CASE WHEN COUNT(*) = 0
            THEN 1 
            ELSE 0 END[/COLOR]
  FROM property_photos
 WHERE property_id = #session.product#
5

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 :slight_smile: 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

6

I was just to late to edit :rolleyes:

This works great Rudy. Thanks a lot :tup:

7

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


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


UPDATE 
    product_photos
SET 
    isActive = 0
WHERE
    photo_id =  firstQueryName.photo_id

And do anoyher update based on the selected radio button after that.


UPDATE 
    product_photos
SET 
    isActive = 0
WHERE
    photo_id =  form.photo_id

This is working, but I’m always looking for improvement.

8

your last query should be setting isActive to 1 (your explanation is fine)

i would combine all three of your steps into one –


UPDATE product_photos
   SET isActive =
       CASE WHEN photo_id = #form.photo_id#
            THEN 1
            ELSE 0 END
 WHERE product_id = #Url.product#
9

:rofl: Oh so easy. I was near though, This is what I had:


UPDATE 
     product_photos
SET
     CASE WHEN 
     [B]isActive[/B] = #Form.photo_id#
     THEN 1 
     ELSE 0 END