Thet Han Thet Han - 4 months ago 10
PHP Question

mysql retrieve data from a range of time from table

-this is the table of picture i want to retrieve datas from




i want to try two query

-the first one is to get the actionNo between firstrow of stopTime and next row of stopTime,concern with current time.

-the second query is to get the actionNo between first row of actionTime and next row of actionTime, concern with current time.

-this is eg of range to get data picture

Please help me, thanks you very much

##

Answer
SELECT MAX(id) FROM timestuff WHERE `actionTime` < CURTIME();
SELECT MAX(id) FROM timestuff WHERE `stopTime` < CURTIME();

This gets you the latest id of the action/stoptime before the current time, will work if your table always is in the order as you have shown.

To be safe you can take another step and add a inner join on the latest time before the current time and then look up the id of that data. For the actionTime it looks like this: (same for stopTime, just have to change the columns)

SELECT
    timestuff.id
FROM
    (
        SELECT
            MAX(actionTime) AS maxTime
        FROM
            timestuff
        WHERE
            `actionTime` < CURTIME()
    ) AS time
INNER JOIN timestuff ON timestuff.`actionTime` = time.maxTime;

For the second one:

SELECT
    timestuff.id
FROM
    (
        SELECT
            MAX(stopTime) AS maxTime
        FROM
            timestuff
        WHERE
            `stopTime` < CURTIME()
    ) AS time
INNER JOIN timestuff ON timestuff.`stopTime` = time.maxTime;