Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • Last Modified:

Need to add a forward slash "/" to all image fields in MySQL table?

Hi,
I need to add a forward slash "/" to all image fields in MySQL table only where they don't already exits.

Is there an easy way to do this?

Thanks in advance for your help.

CREATE TABLE IF NOT EXISTS `items_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `image1` varchar(60) DEFAULT '/uploads/nopic.jpg',
  `image2` varchar(60) DEFAULT '/uploads/nopic.jpg',
  `image3` varchar(60) DEFAULT '/uploads/nopic.jpg',
  `image4` varchar(60) DEFAULT '/uploads/nopic.jpg',
  `image5` varchar(250) DEFAULT '/uploads/nopic.jpg',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1151 ;



INSERT INTO `items_test` (`id`, `image1`, `image2`, `image3`, `image4`, `image5`) VALUES
(802, 'uploads/item802_1.jpg', 'uploads/nopic.jpg', 'uploads/nopic.jpg', 'uploads/nopic.jpg', 'uploads/nopic.jpg'),
(802, '/uploads/products_good.jpg', '/uploads/products_good2.jpg', 'uploads/nopic.jpg', 'uploads/nopic.jpg', 'uploads/nopic.jpg'),
(891, 'uploads/new_image_1.jpg', 'uploads/nopic.jpg', 'uploads/nopic.jpg', 'uploads/nopic.jpg', 'uploads/nopic.jpg');

Open in new window

0
sabecs
Asked:
sabecs
  • 2
1 Solution
 
GaryCommented:
UPDATE items_test
SET
image1=CONCAT('/',image1)
WHERE
!(SUBSTR(image1, 1, 1)='/')

Open in new window

0
 
GaryCommented:
Forget that, didn't see multiple fields

UPDATE
  items_test
SET
  image1 = IF (!(SUBSTR(image1, 1, 1)='/'), CONCAT('/',image1),image1),
  image2 = IF (!(SUBSTR(image2, 1, 1)='/'), CONCAT('/',image2),image2),
  image3 = IF (!(SUBSTR(image3, 1, 1)='/'), CONCAT('/',image3),image3),
  image4 = IF (!(SUBSTR(image4, 1, 1)='/'), CONCAT('/',image4),image4),
  image5 = IF (!(SUBSTR(image5, 1, 1)='/'), CONCAT('/',image5),image5)

Open in new window

0
 
sabecsAuthor Commented:
Thanks Gary, very much appreciated.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now