CODEWITHSUNDEEP
phpmysqldatetimeselect
Ardeus
Ardeus

Reputation: 2001

Php mysql get the nearest-latest record if a record is not exist (datetime based)

Trying to get my head around, If I select a record such as WHERE item_id='$item_id' AND date(datetime)='2012-06-25' and if that record does not exist so I want to get the nearest-latest record after that date. How can I achieve that in a query?

All I can think of the only way right now is if num_of_rows is 0 then I add 3 days period ahead to that day and search again and get the DESC datetime LIMIT 1 (in case there are multiple rows). But who knows I can do it with just a query.

The record could have multiple rows in one day. So if a particular date has no record, how to get the next nearest available data given the same $item_id?

Upvotes: 1

Views: 915

Answers (3)

Fluffeh
Fluffeh

Reputation: 33502

This will bring back the item closest to the date that you enter into the query. It won't however look for before or after, just find the closest date to what you enter in.

select 
     min(abs(DATEDIFF(date(datetime),'2012-06-25'))) as minDiff
    ,yourID
from table1 
group by yourID 
order by 1 asc;

Upvotes: 0

Omesh
Omesh

Reputation: 29071

I think this is what you are looking for:

SELECT *
FROM my_table
WHERE datetime BETWEEN '2012-06-25 00:00:00' AND 
                       DATE_ADD('2012-06-25 00:00:00', INTERVAL 3 DAY)
ORDER BY datetime ASC
LIMIT 1;

also create index on field datetime for faster performance.

Upvotes: 0

Andreas
Andreas

Reputation: 1781

SELECT *
 FROM table
 WHERE field <= '2012-06-25'
 ORDER BY field DESC
 LIMIT 1

Upvotes: 4

Related Questions