CODEWITHSUNDEEP
phpmysql
Satish Saini
Satish Saini

Reputation: 2968

IF in MYSQL Query

I have a column named "image_path" in a table "tb_users". I have saved the image name for users who are not from facebook and image path who are registered from facebook like as shown below :

**image_path**
https://graph.facebook.com/100916463/picture
user1.png
user2.png

Now I want to concat the base path in "select query" for those users whose "image_path" doesn't contain http else return as it is.

For e.g if image_path is https://graph.facebook.com/100916463/picture, and it contains http then it should be returned by select query as it is but if image_path is user1.png then concat a basepath like http://www.example.com/images/ so that select query should return http://www.example.com/images/user1.png. ? How can I do this with a single select query using IF ? OR any other operator ?

Upvotes: 3

Views: 117

Answers (4)

Steven Moseley
Steven Moseley

Reputation: 16355

The ideal solution would handle null and empty-string values (using a default image in their place), and would use LEFT for optimal performance.

SELECT
    CASE 
        WHEN `image_path` IS NULL OR `image_path` = '' 
            THEN 'http://www.example.com/default.jpg'
        WHEN LEFT(`image_path`, 4) = 'http' 
            THEN `image_path`
        ELSE CONCAT('http://www.example.com/images/', `image_path`)
    END AS `image_path`
FROM `your_table`

Upvotes: 1

D Mac
D Mac

Reputation: 3809

It would be more direct, and would allow for additional cases if/when you need to add functionality to your application, if you coded the type of image_path when you store it. Change your data structure to:

image_path_type_id int,
image_path varchar(250),

and make image_path_type_id point to a table with the possible image types. Then your logic can be deterministic, it will expand easily for other image types and/or conditions, and it won't break if there's a typo in your image_path (well, the logic won't break).

Your select becomes:

select case 
    when image_path_type_id = 1
    then image_path
    when image_path_type_id = 2
    then concat('http://staticpath', image_path
end

Adding another case won't require a change to the schema; you would just add another line to the list of types in the type table.

Upvotes: 0

RiaD
RiaD

Reputation: 47658

 SELECT IF(SUBSTR(image_path, 0, 7) == "http://" OR SUBSTR(image_path, 0, 8) == "https://", image_path, CONCAT("http://www.example.com/images/", image_path)) FROM ...

Upvotes: 4

aleroot
aleroot

Reputation: 72676

With a case and LIKE you can achieve what you want ...

   SELECT CASE WHEN image_path LIKE 'http%' THEN 'something' ELSE 'somethingelse' END

Upvotes: 2

Related Questions