CODEWITHSUNDEEP
phpmysqlsqldate
Mohamedali Jinna
Mohamedali Jinna

Reputation: 11

get nearest next one date records in mysql

i am having an event table.and i show events for today using.
SELECT * FROM `special_banner_tbl` WHERE `SPLBANNER_pubsdate` = current_date

if there is no events for today, it have to display the future day record which is more near to current date.
if the table like..
------------------
future_date
------------------
30-11-2013
30-11-2013
02-12-2013
03-12-2013
03-12-2013
----------------------
it will display the next nearest date 30-11-2013 records.. like.

SELECT * FROM `special_banner_tbl` WHERE `SPLBANNER_pubsdate` = nest_nearest_date_records

only one nearest future date records without any limit. like
--------------- --------------
events          future-dates
--------------- ----------------
30th-events   30-11-2013
30th-events   30-11-2013
------------------------------

Upvotes: 0

Views: 1989

Answers (6)

Jpsy
Jpsy

Reputation: 20902

SELECT *
FROM `special_banner_tbl`
WHERE `SPLBANNER_pubsdate` >= CURDATE()
ORDERBY `SPLBANNER_pubsdate` asc
LIMIT 1

If your field SPLBANNER_pubsdate would contain full datetime values (instead of date values), you would have to change the where clause a bit, to isolate the date:

SELECT *
FROM `special_banner_tbl`
WHERE DATE(`SPLBANNER_pubsdate`) >= CURDATE()
ORDERBY `SPLBANNER_pubsdate` asc
LIMIT 1

Upvotes: 0

Ashish Patel
Ashish Patel

Reputation: 1041

SELECT * FROM `jsproduct_company`
WHERE future_date > NOW()
LIMIT 1;

You can find exact next date record with above query.

Upvotes: 0

user2768948
user2768948

Reputation:

Anyway I would write two separate queries for this case but this also works fine:

SELECT 
    *
FROM special_banner_tbl
WHERE SPLBANNER_pubsdate = CURRENT_DATE

UNION ALL 
SELECT 
    *
FROM special_banner_tbl
WHERE 
    (SELECT COUNT(*) 
    FROM special_banner_tbl 
    WHERE SPLBANNER_pubsdate=CURRENT_DATE) = 0 
    AND 
    SPLBANNER_pubsdate = (
        SELECT MIN(SPLBANNER_pubsdate) 
        FROM special_banner_tbl 
        WHERE SPLBANNER_pubsdate > CURRENT_DATE 
        ORDER BY SPLBANNER_pubsdate
        LIMIT 1
    )

Upvotes: 2

Mahmood Rehman
Mahmood Rehman

Reputation: 4331

Try this :

SELECT 
  * 
FROM 
  `special_banner_tbl`
ORDER BY 
  ABS(DATEDIFF(NOW(), `SPLBANNER_pubsdate`))
LIMIT 10

Upvotes: 0

Pantelis Natsiavas
Pantelis Natsiavas

Reputation: 5369

Actually what you have to do is use the difference between today and the future. Then you would have to order your data by this difference ascending.

Hope I helped!

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

You need or SPLBANNER_pubsdate > current_date for the future dates and the order by SPLBANNER_pubsdate ASC so if no events found for current date it will have future events if found then current events are listed first 

SELECT * FROM `special_banner_tbl` WHERE `SPLBANNER_pubsdate` = current_date
OR `SPLBANNER_pubsdate` > current_date ORDER BY `SPLBANNER_pubsdate` ASC
 LIMIT 0 , 30

Upvotes: 0

Related Questions