hornyy hornyy - 7 days ago 5
MySQL Question

mysql select 2 tables and order by 1 of them by date (more close to the current time)

This is my problem.

table ns_leagues:



id name
1 League 1
2 League 2
3 League 3


table ns_upcoming:



upID league date
1 1 1410390000
2 2 1411990200
3 3 1412010000


I have this 2 columns and I want to select the leagues from table
ns_leagues
and order by the row from the
ns_upcoming
with the date more close to the current time.

I tried several ways but nothings is working so far.

FAIL:

SELECT id
FROM ns_leagues
WHERE id in (
SELECT league
FROM ns_upcoming
WHERE date<='".$now."'
ORDER BY date ASC

SELECT DISTINCT id
from ns_leagues a
LEFT JOIN ns_upcoming v ON a.id = v.league AND v.date<= '".$now."'
ORDER BY v.date ASC


CURRENT:

SELECT * FROM ".PREFIX."leagues ORDER BY id DESC


This is what I have right now (on the right side: PROXIMOS PARTIDOS): http://www.nuno-silva.pt/jobs/mark/index.php

"En vivo" means that date >= time(); and it's not finished.
The times are countdowns for the match.

I need to order the leagues by date (selecting for that the row with the closest date to time(); from column date of table ns_upcoming of the selected league)

Could you give me a help on this one please?

Answer

You'd need to sort by the time DIFFERENCE between "now" and the match's time, e.g.

SELECT ...
...
ORDER BY DATEDIFF(ns_upcoming.date, curdate())
Comments