Select by schedule time

Question

Good afternoon, Folks!

I’m building a TV grid app. There is a table in the database that contains information from (Program name, description and time). In my application, the system sorts this schedule table based on the current time of Brasilia, and displays the program that is playing live, and the schedule that will pass next.

The problem is that in my select currently is this:

"SELECT *, TIME(horario) as horario FROM Programacao where horario >= '$gh_data' order by horario"

Where the variable $gh_data is the current system time and "time" is the program time in the database "schedule" table.

Example of the problem happening:

An A program starts at 14:00 and the B program starts at 15:00. When the system time is 14:01 it shows that the current program is B, instead of A.

How to solve this problem?

Answer 1

I believe that doing a subquery to get the first programming smaller than the current time can solve.

For example, the first programming less than or equal to 14:01 is the 14:00, correct? Then the select below will return the programming of 14:00:

SELECT horario AS a
FROM Programacao
WHERE horario <= '$gh_data'
ORDER BY horario DESC LIMIT 1

Now, by placing this in a subquery you will be able to condition the WHERE to return records whose times are greater than or equal to the time of the select above, i.e. 14:00:

SELECT *, TIME(horario) as horario
FROM Programacao,
(
   SELECT horario AS a
   FROM Programacao
   WHERE horario <= '$gh_data'
   ORDER BY horario DESC LIMIT 1
) AS b
WHERE horario >= a ORDER BY horario