CODEWITHSUNDEEP
mysqlsqlsql-order-by
RonaIdo
RonaIdo

Reputation: 81

Order dropdown by existing data SQL

I have a list of records from a MySQL database. When a record is clicked it takes you to a page where you can update these records. I'm using a HTML form to display the data dynamically.

For example, if a game was released in 2007 I'd have a dropdown box with all the different years that it could be changed to.

My current SQL for the year dropdown is as follows;

SELECT DISTINCT gameYear FROM games ORDER BY gameYear

This displays the years in order. Is there a way to have them displayed in correct order (IE 2001, 2002, 2003 and so on) but have the existing year selected to begin with?

An example, a game with a year of 2007.

When I click the record and go to edit the game, I want the dropdown box to display the current value (2007) automatically. Then, when I select the dropdown I want the years to still be in order.

Upvotes: 1

Views: 109

Answers (3)

Andrzej Reduta
Andrzej Reduta

Reputation: 767

SelectedYear is Your current Selected Year

SELECT '$SelectedYear' as gameYear
UNION
SELECT * FROM 
  (SELECT gameYear from game 
  WHERE gameYear <> $SelectedYear 
  ORDER BY gameYear)

Upvotes: 0

Cornelius
Cornelius

Reputation: 4264

You probably fill the dropdown by looping over your SQL result set and output HTML <option> elements. You need to check inside this loop if the current loop value is equal to the year of your game. If it is add the selected HTML attribute.

Upvotes: 1

rtruszk
rtruszk

Reputation: 3922

You can sort it this way:

select DISTINCT gameYear from game order by 
  case
     when gameYear = 2007 then 0
     else gameYear 
  end;

Upvotes: 1

Related Questions