MySQL: Help with script needed

Question

CREATE TABLE `photos` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `photo` varchar(255) NOT NULL,
  `hotel_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23061 DEFAULT CHARSET=utf8

So, i have table 'photos.' Now i want ALTER this table and add column 'type'.

ALTER TABLE photos
ADD COLUMN type varchar(50) default 'jpg'

But there is one problem: column 'photo' contains path - '/foo/bar.jpg' or '/foo/bar.png' and i want to update 'type' column according to 'photo' column content. So type must be 'jpg' if photo ends with 'jpg' or type must be 'png' if photo ends with 'png'.

I have no idea how to write such script...

Answer 1

You could use a case statement for that:

update  photos
set     type = case
                   when photo like '%.jpg' then 'jpg'
                   when photo like '%.png' then 'png'
                   else null
               end

Or if the type is always the last 3 characters:

update  photos
set     type = right(photo, 3)

Answer 2

Not an answer to your question, but this doesn't really sound like a job for the database engine. I would recommend setting type when the record gets added .

Also, the file extension is no guarantee that an image is actually of the specified type. It could be a good idea to check for this - in PHP, for example, you would use getimagesize().