I am using this query to pull out article entries from my database:
$sql = "SELECT * FROM articles WHERE author LIKE ? AND title LIKE ? AND year LIKE ? ORDER BY year DESC";
With single year specifications, the query orders the entries by year as intended:
1999 1998 1979 1969 ...
Problematic is that this older database uses several publishing years in the same table cell, looking like this:
Example: 1970, 1974, 1983
or sometimes with semicolons 1970; 1974; 1983
This makes the script order the entries only using the very first year specification (1970 in the above example). Now I am looking for a way to order the entries by the most recent year specification (1983 in the above example).
So the random entries
- 1966, 1977, 1989
- 1925; 1956; 1977
- 1933, 1947, 1975, 1999
should be ordered like this:
- 1933, 1947, 1975, 1999
- 1966, 1977, 1989
- 1925; 1956; 1977
(only the most recent years 1999, 1989, 1977 are relevant for the order of the entries)
Advertisement
Answer
Assuming the year column has at least one year, you can use
ORDER BY RIGHT(year,4) DESC