CODEWITHSUNDEEP
mysqlsql
medk
medk

Reputation: 9529

mySQL conditional select?

table: id, status, time

$now = time(); // current timestamp

$statuses = array (
    'ST1' => 60,  // (seconds)
    'ST2' => 120,  // (seconds)
    'ST3' => 180,  // (seconds)
);

$query = 'SELECT id FROM table WHERE status IN("ST1", "ST2", "ST3") AND time ... LIMIT 1';

Now I want to select id of 1 row from the table where:

If status is ST1: (time + 60) > $now

If status is ST2: (time + 120) > $now

If status is ST3: (time + 180) > $now

So that if the row has the column status = "ST1" it checks if the column time + 60 is greater than $now which is the current timestamp, and so on.

Upvotes: 0

Views: 525

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

Just use basic logic:

SELECT l.id
FROM leads l 
WHERE ((l.status = 'ST1' and l.time > date_sub(now(), interval 60 second)) or
       (l.status = 'ST2' and l.time > date_sub(now(), interval 120 second)) or
       (l.status = 'ST3' and l.time > date_sub(now(), interval 180 second)) 
      )
LIMIT 1;

I'm assuming that (time + xx) > $now refers to seconds. Also, I encourage you to use the database time, rather than passing it in. (You can, of course, replace now() with $now -- or better yet a parameter -- if you have good reasons for passing the time in from the application.)

To be honest, I might put this in a derived table:

SELECT l.id
FROM leads l JOIN
     (SELECT 'ST1' as status, 60 as diff UNION ALL
      SELECT 'ST2' as status, 120 as diff UNION ALL
      SELECT 'ST3' as status, 180 as diff
     ) s
     ON l.status = s.status
WHERE l.time > date_sub(now(), interval s.diff second)
LIMIT 1;

Upvotes: 3

Bart Friederichs
Bart Friederichs

Reputation: 33491

Use a CASE statement:

SELECT id FROM table WHERE 
CASE 
    WHEN status="ST1" THEN (time + INTERVAL 60 SECOND) > NOW() 
    WHEN status="ST2" THEN (time + INTERVAL 120 SECOND) > NOW() 
    WHEN status="ST3" THEN (time + INTERVAL 180 SECOND) > NOW() 
END CASE

Upvotes: 2

Related Questions