1

I need to output data from a DB table that selects records between 3 (not 2) date/time ranges

E.g. start time : 2019-09-07 18.00 end time : 2019-09-07 20.00

so the user should be able to see the record 25 minutes before the start date-time (6.p.m - 18.00), during the event but not after the end date-time (8.p.m -20.00).

I've tried

db->query = "SELECT o_id, schedule, date, start_time, end_time FROM working_schedule WHERE o_id = '".$user_id."'
AND (start_time <= '".date('Y-m-d\TH:i:s', strtotime("-25 minutes"))."' AND start_time >= '".date('Y-m-d\TH:i:s')."') 
AND end_time >= '".date('Y-m-d\TH:i:s')."'";

but the result is NULL.

For reference HERE'S a sql fiddle.

Thanks in advance for pointing me in the right direction.

Someone33
  • 568
  • 2
  • 8
  • 25
  • Simplify the issue, is it PHP related? You can do the date/math differential in mysql. https://stackoverflow.com/questions/3401551/mysql-interval-mins – user3783243 Sep 07 '19 at 17:42
  • What does your query's value(s) show as opposed to what is in your database? – Funk Forty Niner Sep 07 '19 at 17:55
  • @FunkFortyNiner current output is NULL if this is what you want to know, otherwise i didn't get your question. – Someone33 Sep 07 '19 at 17:57
  • @Someone33 what I meant was, if you "echo" the query out and/or `var_dump()` it, what does it show exactly? Seeing that, it might help you to figure out what isn't working the way you want it to. Another thing; if you're storing dates as plain text, MySQL has built-in features made just for this, which makes querying a lot easier. – Funk Forty Niner Sep 07 '19 at 18:01
  • @FunkFortyNiner Oh ok, so i was correct; If i do a "var_dump($this->db->get_row($query));" , the result is NULL, so the result is nothing. That's what confuses me. – Someone33 Sep 07 '19 at 18:05
  • Your code is probably vulnerable to SQL injection. You should use prepared statements. – Dharman Sep 07 '19 at 18:06
  • Sounds like something failed. Enable error reporting for both the PHP and MySQL. – Funk Forty Niner Sep 07 '19 at 18:07
  • @Dharman Thanks, all data is escaped correctly, i just simplified the code here. Btw, my question wasn't about sql vulnerability. – Someone33 Sep 07 '19 at 18:12
  • 1
    That is the problem. You should not escape anything. Use prepared statements. – Dharman Sep 07 '19 at 18:20
  • @FunkFortyNiner I've made a quick sql fiddle, there are 0 returned records, so result is NULL : http://sqlfiddle.com/#!9/aa57ce/3 – Someone33 Sep 07 '19 at 18:31
  • Try to randomly change `<=` and `>=` until you get the correct result. – Paul Spiegel Sep 07 '19 at 18:41

1 Answers1

1

Do you need this ?

 select * from working_schedule
 where
 NOW() BETWEEN DATE_SUB(start_time,INTERVAL 25 MINUTE)  AND end_time
Oto Shavadze
  • 40,603
  • 55
  • 152
  • 236