MYSQL query code to check if statement and get row values-CodePudding

so I was trying to use if statement on MySQL. I have a table contains tp1 column and in this column I have two different string values L1_ON or L1_OFF, I want to get L1_ON if there is one on tp1 column but if there is no L1_ON just get L1_OFF. this is what wrote and i know it's not correct just for extra explanation.

$query1 = sprintf('SELECT id,dateandtime,tp1 FROM plate WHERE AND  IF(tp1 LIKE "L1_ON") else (tp1 LIKE "L1_OFF") ORDER BY id ASC LIMIT 1');

CodePudding user response：

If "L1_ON" and "L1_OFF" are the only possible values, then you could just sort by tp1 column to ensure "L1_ON" columns come up first:

SELECT 
  id,dateandtime,tp1
FROM plate
ORDER BY tp1 DESC, id ASC
LIMIT 1

If there could be more than those two values, and you're using this in a script, honestly I would probably just run two queries. One trying to get "L1_ON" values, then a second getting all other values.

SELECT 
  id,dateandtime,tp1
FROM plate
WHERE tp1 = "L1_ON"
ORDER BY id ASC
LIMIT 1

Finally, if you really want to do this in one query, you could union two queries, one for all "L1_ON" values, followed by one for any that are NOT "L1_ON"

SELECT * 
  FROM (
  SELECT 
    id,
    dateandtime,
    tp1
  FROM plate
  WHERE tp1 = "L1_ON"
  ORDER BY id ASC
) AS tp1_on

UNION ALL

SELECT *
FROM (
  SELECT 
    id,
    dateandtime,
    tp1
  FROM plate
  WHERE tp1 != "L1_ON"
  ORDER BY id ASC
) AS tp1_off

LIMIT 1

CodePudding user response：

using if statement I found the best answer to my question like following:

$query  = sprintf('SELECT IF (tp1 REGEXP "L1_ON" ,dateandtime ,"L1_OFF") id,dateandtime,tp1 FROM plate  ORDER BY id ASC LIMIT 1 ');

so the code check if tp1 has L1_ON then give me time for that raw, if not just give any L1_OFF raw with time (dateandtime= time of L1_.. raw).