John Davidson

Message:

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

1. 1966, 1977, 1989


2. 1925; 1956; 1977


3. 1933, 1947, 1975, 1999

should be ordered like this:

1. 1933, 1947, 1975, 1999


2. 1966, 1977, 1989


3. 1925; 1956; 1977

(only the most recent years 1999, 1989, 1977 are relevant for the order of the entries)

---

My Answer:
"SELECT * FROM articles ORDER BY YEAR(date_published) DESC, title ASC";