Shoutbox

I have a mysql table that stores date/time in the format of "01/02/10 07:06:33", but when listing that table using PHP, and ordering by date/time, the 2010 records appear at the end, whereas, the latest 2009 records, so from the 31st of December appear at the top. Is there anyway to get around this? or to "SELECT *", but then print in the reverse order?

You could try using a timestamp instead, which gives you a lot more flexibility when handling time and should produce your database in chronological order.

Are you just attempting to show the latest records first? If that is the case using your primary key (typically 'id') and order them from the top. If you don't have a primary key, I highly recommend using one, their vital when it comes to using relationships.

Okay thanks, I will look into using a timestamp instead.

I do have a primary key, but the unique identifier is based on a players SteamID, where no two players can have the same ID, however, these do not increase at a standard rate. For example, one player could have a SteamID of STEAM_0:1:123456, but then another player, whose record is added at a later date, could have a SteamID of STEAM_0:0:116483.

Use your own personal identifier e.g. id > steam_id > desc > time

quote:

---

Originally posted by absorbation
Use your own personal identifier e.g. id > steam_id > desc > time

---

That won't work if the timestamps don't relate to the id or steam_id. For example, I could have the lowest id (1), but the most recent time stamp (say now for example) and a steam_id of 748476

I'd show up before someone with the earliest time stamp, but with a higher id

quote:

---

Originally posted by Spunky

quote:

---

Originally posted by absorbation
Use your own personal identifier e.g. id > steam_id > desc > time

---

That won't work if the timestamps don't relate to the id or steam_id. For example, I could have the lowest id (1), but the most recent time stamp (say now for example) and a steam_id of 748476

I'd show up before someone with the earliest time stamp, but with a higher id

---

Usually, when a record is added, the unique id is set to that record, one above the last one. Thus, organizing in desc order by unique id, will give the newest records first .

Yeah, I suppose. Don't know what I was thinking actually =/

Thanks, I'll go about adding a unique ID later

Without making any changes to how you are storing your data, the easiest solution would be to use:

SELECT * FROM myTable ORDER BY STR_TO_DATE(myTimestamp, '%m/%d/%y %H:%i:%s') DESC

That should do exactly what you want, by converting your date/time string to a DATETIME value for sorting on the fly.  I doubt your tables are large enough for this to be a performance concern.

The best practice would be to redesign the table to use DATETIME for the timestamp.  You can use the STR_TO_DATE() function with the above format string to convert the data as you insert it.

Relying on an incrementing ID for this is a kludge, a logic flaw, and asking for it to bite you in the posterior.  You shouldn't rely on the assumption that your data will always be inserted in chronological order, even if you believe it will be -- that is exactly the kind of assumption that leads to hard to find bugs down the road.

Oh wow, thanks Adeptus. That works exactly how I want, and like you say, my table is only around 100 records, so that shouldnt be a performance concern, right?