better way of writing this query?

[hyster]

just wondering if there was a better way of writting this query. its the order by part im wondering about.

would it have been better to put it into a variable?

is there a way for mysql to recognise roman numerals ?

 

SELECT country, name
 FROM tanks_owned			
 WHERE player = '$player'
 group BY country, name
 order by country,field(type, 'light', 'medium', 'heavy', 'td', 'spg'), field(tier, 'I', 'II', 'III', 'IV', 'V', 'VI', 'VII', 'VIII', 'IX', 'X') asc

[Barand]

Store data that can be ordered. So have a "types" table

 

typeid | type

1 | light

2 | medium

 

etc

 

and store the ids. Same with tiers,and use joins

Edited February 25, 2013 by Barand

[Jessica]

I'd add to Barand's way to add a column specifically called 'order_by' or something, so you can easily change the order - this is vital when you ADD a new value and need it to go between two existing values. 

[hyster]

so basicly restructure the data base?

 

origanal format

tank_list

tank_id--country-type-------name----tier

1-----------ussr----light--------MS-1-----I

136--------usa-----medium--M3-------IV

 

new format

tank_list

tank_id--country-type---name--tier

1--------------1-------1-----MS-1----1

136----------2--------2------M3------4

 

country_list

country_id--country

1---------------ussr

2---------------usa

****************

 

tier_list

tier_id--tier

1----------I

*************

4---------IV

 

type_list

type_id--type

1----------light

2----------medium

****************

 

then the join query to tie them together?

[Barand]

yes. Display the names but sort on the ids

[Psycho]

As a "general" rule, if you have a value in a table that is a finite list such as the above for country, tier, and type you should typically put those values into a separate table and JOIN as needed. There are many reasons to do this aside from sorting. Here are some:

 

Keeping the values consistent. By using the values from a lookup table you know they will always be the same (e.g. "USA" vs "Usa" vs "U.S.A"). Plus, if you ever need to change the text of a value, you can do it by changing just one record.

 

It helps to prevent invalid values. Even when using a SELECT list in an HTML form, it is possible for other values to be submitted. Using a lookup table makes it much easier to prevent invalid values from being inserted into the database.

 

If you do want to create a form with a select list of "valid" values you can get that list directly from the lookup table rather than doing a DISTINCT query or hard-coding the list in the code.

 

You can relate records using the same lookup values very easily using just the ID of the value rather than the text of the value. I'm not 100% sure, but I would guess that even when indexing, that indexes on integer fields will be more efficient than indexes on text fields