How do edit this mySQL query based on value of column without using PHP?

Question

I want to edit my existing mySQL query based on whether the column show_only_on has a value or not.

If it has a value, then the correct query is the below

Select *
FROM alerts_data
WHERE id = 'abc' AND running_status='idle'
AND FIND_IN_SET("https://www.example.com/pricing", show_only_on)

And if it does not have a value, I just leave out the

AND FIND_IN_SET("https://www.example.com/pricing", show_only_on)

or make the FIND_IN_SET return true.

How can I do this using only this query and not any PHP ?

Might help you: stackoverflow.com/questions/13887616/…

Russ J
– Russ J

2019-03-05 22:55:28 +00:00
Commented Mar 5, 2019 at 22:55 — Russ J
– Russ J, Commented Mar 5, 2019 at 22:55
Otherwise see meta.stackoverflow.com/questions/333952/…

Strawberry
– Strawberry

2019-03-05 23:22:33 +00:00
Commented Mar 5, 2019 at 23:22 — Strawberry
– Strawberry, Commented Mar 5, 2019 at 23:22

Daniel G · Accepted Answer · 2019-03-05 22:55:11Z

3

How about running it through an OR/AND statement?:

Select *
FROM alerts_data
WHERE id = 'abc' AND running_status='idle'
AND (
  show_only_on IS NULL /* Whatever check you do to ensure it's not set */
  OR FIND_IN_SET("https://www.example.com/pricing", show_only_on)
)

answered Mar 5, 2019 at 22:55

Daniel G

5474 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

David · Accepted Answer · 2019-03-05 22:56:25Z

2

If I understand you correctly, you want to return all records where wither show_only_on is NULL or where show_only_on matches the FIND_IN_SET logic?

Sounds like this:

WHERE
    id = 'abc' AND
    running_status='idle' AND
    (
        FIND_IN_SET("https://www.example.com/pricing", show_only_on) OR
        show_only_on IS NULL
    )

answered Mar 5, 2019 at 22:56

David

221k42 gold badges247 silver badges338 bronze badges

Comments

e_i_pi · Accepted Answer · 2019-03-06 00:19:04Z

0

Another option, one that I tend to use in parameterised queries:

WHERE id = 'abc'
AND running_status='idle'
AND COALESCE(show_only_on, "https://www.example.com/pricing") = "https://www.example.com/pricing"

Parameterised:

WHERE id = 'abc'
AND running_status = 'idle'
AND COALESCE(show_only_on, :url) = :url

Note this only works where show_only_on is NULL if and only if the value is considered empty/missing.

answered Mar 6, 2019 at 0:19

e_i_pi

4,8605 gold badges32 silver badges47 bronze badges

Collectives™ on Stack Overflow

How do edit this mySQL query based on value of column without using PHP?

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related