Time Format

[jlsohio]

I have a database field that I use for a time format like this:

1:49.3 (one minute, 49.3 seconds) This is common lingo in the horse racing industry.

I've been displaying it as a VARCHAR field, but now I want to sort by it.

How should I specify this field type in mysql? Or should I format in PHP? If so how?

Thank you.

jls

[Jeewhizz]

you could just store it as an integer and format it in PHP: i.e. store 1493 and then format it to have the . and the : :)

Jee

[jlsohio]

Could you show me how to write it in php?

[jlsohio]

OK. I changed mysql to an integer(4)

I put the data in like this: 1493

Here's my PHP code:

// Request the text of all the horses
$result = @mysql_query('SELECT name, age, record FROM leaders ORDER BY record');
if (!$result) {
die('<p>Error performing query: ' . mysql_error() . '</p>');
}

// Display the text of each horse in a paragraph
while ( $row = mysql_fetch_array($result) ) {

$table_row = "<tr align=\"center\" valign=\"middle\">";
$table_row .= "<td WIDTH=50%><font size=\"2\"><font face=\"arial\">$row[name]</td>";
$table_row .= "<td WIDTH=25%><font size=\"2\"><font face=\"arial\">$row[age]</td>";
$table_row .= "<td WIDTH=25%><font size=\"2\"><font face=\"arial\">$row[record]</td>";


What do i need to do to get it to look like this:

1:49.3

Thanks.

jlsohio

[mordred]

Well, you could to that with the help of regular expressions:


echo preg_replace('/(\d{2})(\d)$/', ':$1.$2', '1493');


Looks a little bit obscure I know, but does the trick.

P.S: If I were you, I would change the definition for this time column. Integer(4) seems to narrow for me. What if a horse race takes longer than 9 minutes? Then you need 5 digits. Integer fields are not expensive to store, so maybe you enlarge that value to 5 or 6 digits....

[Jeewhizz]

Try this:


$time = 1439;
$time = substr_replace($time,'.',2,0);
$time = substr_replace($time,':',1,0);
echo $time; //should give 1:43.9


That is untested. I'll move this over to the PHP forum for you now, so the real guru's can get to work by ripping my code to shreds ;)

Jee

[jlsohio]

Jeewhizz,

You almost got it!

My data is displayed like this:

1:4.39 (the decimal place is off by one)

Tried it like this:

$time = 1439;
$time = substr_replace($time,'.',3,0);
$time = substr_replace($time,':',1,0);

Still have a problem...

All my data is displayed at 1:49.3. Some of data is 1:50.1, 1:52.3 etc, etc...

Thanks.

jls

[Saj]

Thats because your $time is set to 1439...you need to set it to whatever the number will be.....

[jlsohio]

I'm not sure how to do that?

I tried it like this...

// Display the text of each horse in a paragraph
while ( $row = mysql_fetch_array($result) ) {

$time = $row["time"];
$time = substr_replace($time,'.',3,0);
$time = substr_replace($time,':',1,0);

$table_row = "<tr align=\"center\" valign=\"middle\">";
$table_row .= "<td WIDTH=40%><font size=\"2\"><font face=\"arial\">$row[name]</td>";
$table_row .= "<td WIDTH=20%><font size=\"2\"><font face=\"arial\">$row[age]</td>";
$table_row .= "<td WIDTH=20%><font size=\"2\"><font face=\"arial\">$row[sex]</td>";
$table_row .= "<td WIDTH=20%><font size=\"2\"><font face=\"arial\">$time</td>";

echo "$table_row\n";

The field is empty now.

jls

[jlsohio]

I figured it out.

My bad! I had the incorrect field name inside my brackets.

Should be this:

$time = $row["record"];
$time = substr_replace($time,'.',3,0);
$time = substr_replace($time,':',1,0);


I had it like this:

$time = $row["time"];
$time = substr_replace($time,'.',3,0);
$time = substr_replace($time,':',1,0);


Works good.

Thank you for your help. I appreciate it.

jls

[scroots]

are ll your times less than 9 mins or does the script work form right to left by going right to left you would have to make sure all times where to the same format e.g. 1.430 and 1.400

scroots

[jlsohio]

Yes, all times will be less than 2 minutes.

jls

[scroots]

thats good then otherwise all your time formatings could screw up.

scroots