John Davidson

php - Order mySQL query by year with several year specifications

0 comments
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";

Rate this post

3 of 5 based on 6507 votes

Comments




© 2024 Hayatsk.info - Personal Blogs Platform. All Rights Reserved.
Create blog  |  Privacy Policy  |  Terms & Conditions  |  Contact Us