MySQL Tough Questions(Atleast for me)

[Anon]

Hi All,

I have the folloing problems with MySQL.

1. For my Database application i am storing values in a table. I insert full month name(example: May) for month column. I have no use of month other than just displaying. I use the same table many times for displaying the date. Now the problem is i have to sort using that month field. One option which i have is storing 1, 2...12 for months and sort based on them. But i have to change all the programs which are used to display the date. I have to use switch statement. Is there any other option for me? How can i sort with out changing programs???

2. For one application i retrieve the old values from a table and display for editing. Now the problem is when they submit them the order in which they are stored is different. How can i store them in the same order?

Please help.

Thank You,
JAK.

[Anon]

order by case monthName
when 'January' then 1
when 'February' then 2
...
when 'December' then 12 end

I do not understand your second question. How data is stored is nothing that you can affect. You can never assume that data will be retrieved in a certain order without specifying an order by clause.

[[deleted]]

1. Storing a date as a string is never a good idea. Your best option is to convert the string to a number inside the database, and change all your programs to display the monthname, which you can get from SQL using the date_format() function
   http://www.mysql.com/doc/D/a/Date_and_time_functions.html

2. The order of records in the database is random, and of no importance at all (it is often decided by first-come-first-serve)

You should use the SQL's "ORDER BY" clause to define the order of the records when you select them:

SELECT FROM table ORDER BY date_field;
or
SELECT FROM table ORDER BY firstname;

---

A forum, a FAQ, what else do you need?

Time to YAPF!: http://www.hvt-automation.nl/yapf/