CODEWITHSUNDEEP
phpmysqlsql
alt
alt

Reputation: 13907

Select MySQL row where column is closest to current date

I have a database with a bunch of rows. One of the columns is "date" which has a date for each row in the format: MM/DD/YYYY H:MM PM/AM

How can I write PHP that selects the row where the date is the most recent date that has passed. Meaning if you have a date of tomorrow, a date of today, and a date of yesterday, it picks the row where the date is of yesterday.

I've already connected to the database:

mysql_connect('localhost','username','pass');
mysql_select_db('db_name');

How do I write a query to grab this?

Upvotes: 0

Views: 2633

Answers (2)

alecwhardy
alecwhardy

Reputation: 2718

When you query the database, ORDER BY date DESC LIMIT 1

This will only return the most recent result, and thus the result that is closest to the current date. (This only works if you don't put entries that are dated in the future)

Upvotes: 0

lc.
lc.

Reputation: 116448

I would do the filtering in SQL rather than PHP, using a variation of the following query:

SELECT *
FROM myTable
WHERE theDate < CURDATE()
ORDER BY theDate DESC
LIMIT 1

This selects all the rows in the past (theDate < CURDATE()), sorts them in reverse chronological order (ORDER BY theDate DESC), then takes the first record (LIMIT 1).

Upvotes: 2

Related Questions