trouble Storing and retrieving hhh:mm:ss data
Posted: Thu Jan 17, 2008 7:19 pm
To start: I create/maintain a website for an online game, I am currently nearly done with a script that parses a simple html file that is output by the server with character information on it. And then retrieves it in another php file (it allows me to a] sort that data and b] select individual pieces for other projects) I have the parser and the display scripts written but am coming in trouble with managing with a field called: "Total Time Online"
The problem is that the data is stored in hhh:mm:ss format, so it could include the following:
I looked into a ereg_replace or preg_replace alternative as I dont need to store the data as time because i will not be making any calculations with it, the problem is that I do not know how many digits would be in the hour field, it could be hhh:mm:ss or h:mm:ss so i do not belive that would work when trying to convert back to hhh:mm:ss from hhhmmss.
what would be the best way to achieve this, i need to be able to store the data, then retrieve it in the hhh:mm:ss format and have it be sortable.
sorry for the long winded post, i just wanted to make sure that I explained everything, ive been searching and cannot come up with a suitable solution.
The problem is that the data is stored in hhh:mm:ss format, so it could include the following:
when i created everything I simply stored the entire string as a varchar in my mysql server. but have come to realize this is horribly inefficient as i cannot sort the data appopriately, when sorting from largest to smallest I run into the following:157:24:08
2:15:56
10:46:55
etc...
so it is obviously thinking that 8 is larger than 89, most definately because im using a string rather than an appropriate variable type. I was looking into the TIME type in MySQL but it only supports up to 838:59:59, which seems large enough but its only like 34 days and some players could possibly exceed that.8:06:39
8:02:01
89:53:51
I looked into a ereg_replace or preg_replace alternative as I dont need to store the data as time because i will not be making any calculations with it, the problem is that I do not know how many digits would be in the hour field, it could be hhh:mm:ss or h:mm:ss so i do not belive that would work when trying to convert back to hhh:mm:ss from hhhmmss.
what would be the best way to achieve this, i need to be able to store the data, then retrieve it in the hhh:mm:ss format and have it be sortable.
sorry for the long winded post, i just wanted to make sure that I explained everything, ive been searching and cannot come up with a suitable solution.