database query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

database query

Post by gurjit »

Hi all,

I have a varchar field which stores dates like "22/05/2004" UK format. When i asc or desc the order, the order i choose to display is not correct. How can i make the order display correct in the query.

This my query. I want the data to order by date using "Y-m-d" format from this field.

Code: Select all

<?php
select * from studentsexp,tbl_school,tbl_location where studentsexp.SCHOOL_CODE = tbl_school.sc_code and tbl_location.lid = tbl_school.frn_lid and  tbl_location.frn_cid = $frn_cid order by studentsexp.SCHOOL_CODE,studentsexp.DOB_DD_MM_ desc
?>
User avatar
Calimero
Forum Contributor
Posts: 310
Joined: Thu Jan 22, 2004 6:54 pm
Location: Milky Way

Post by Calimero »

If you are not using DATE or TIME types, why don't use 3 columns - one for Year, one for Month and one for Date.

Much easier to search and sort.

Then just sort them by those fields ASC or DESC - however do you like.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

classic case of using the wrong type of field to store your data....you really shouldn't be storing dates as strings, instead, store the timestamp...trust me, it will make you life a whole lot easier
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

Post by gurjit »

I always do store dates with data type date but i was given raw data in csv format woth DOB for students in UK date format.

I created another field called DOB and wrote a function to store the date in the correct format and then manipulated my data in the appropriate tables.

Thanks for your help people, i took your advise Calimero.
Post Reply