[RESOLVED] Sorting by manually inserted date

Ryan_Sanders

Ok. I am coding something where you have to manually insert the dates.

Such as... You have a patient. And you want to add a date for the last time this patient visited.

The way it will work.. You won't be adding this to the database the same day that the patient visited. A worker will have forms and will insert this stuff into the database when it gets to he/she. So the only way to get the correct date is to type it in manually.

Now, when I am listing the dates, I want to list the last 10 times this patient visited.

I originally had it so that it would show the last ten entered into the database, but I figure that if someone adds an older date to the database AFTER a new date has been added, then it will show the older date first because it was inserted into the database last.

I'm trying to figure out a way to order the dates in the correct order, no matter when or what order they were put into the database.

The format of the dates are

MM/DD/YYYY.

Now.. For single number months (1-9), there will NOT be a zero in front of it. Just want to make sure that is known.

I am just kind of lost on what to do here.

Any help is greatly appreciated.

dagon

if you insert the dates in the format the db uses in a date field then ORDER BY date, is all you need

Ryan_Sanders

When I go with ORDER BY date... it orders date by the starting number of the date..
If you had 6/23/2001 and 2/12/2009

It would show the 2001 first.. even though 2009 is the most recent.

My field name is date... there isn't a prefix or anything to make the the query knows that your wanting to sort the field "date" by using the mysql date format or whatever?

dagon

what's the database field type?

Ryan_Sanders

Text

dagon

Ryan Sanders;10908027 wrote:
Text

thats why it wont sort as expected, it needs to be one of the date fields

Ryan_Sanders

The problem is the DATE format is 0000-00-00... I've tried changing that but don't know what to do.

Maybe str functions to move the year on mine from the back to the front or something.. Eh.

Or... hmm. I got an idea.

I could have 2 fields. One for the regular user written date, and one for the mysql format date. So when they insert a date, it will not only add their input to the date field.. but I can do a couple functions so that the date is also entered into another field in the mysql format.

Then when I call to order the dates.. I will order them by dates_mysql (the mysql format field).. but will echo the regular date field.

bradgrafelman

Two date fields storing the same data? Nonsense.

Insert the dates as YYYY-MM-DD, since that's a standard time format (and for good reason - it's easier to sort). If you don't want to display it in that format, then don't - use MySQL's DATE_FORMAT() function in your SELECT queries.