MySql datatype versus PHP formatting whilst not losing numeric sorting capabilities -

see http://pdga-europe.com/standings-2015/

it's simplest question you've seen in while, i'm stumped.

i importing xls sheet mysql database, , data integers, (really, some) fractions (at exact .5)

data type gets set decimal (4,1) phpmyadmin, , math correct (i'm sorting data desc (for ones "0" totaled, i'm using non-visible column "totaled" sort by).

what want see following:

all integers in particular column (et3) display integers (and not xxx.0), , others yyy.5?

varchar @ least display right, prevent me sorting data in way other alphabetical (where 9 of sudden comes before 88).

what road should take right?

should find correct data type, or should formatting in php?

currently data gets generated way:

<..query bla bla ..> printf(" <tr> <td>%s</td> </tr>\n", $row["et3"]); } 

thank you.

you should not modify data type storing these values, because dealing matter of display logic. internally, long of them have decimal part, should stored decimal part numeric type, rather varchar type.

so should handle display logic in php, , can done many different ways. retrieving values mysql, have string rather php float value in $row['et3'], making easy handle basic string operations like substr(). may test if last 2 characters of ".0" , truncate intval(). example:

// intval truncates .0 echo intval('9.0'); // 9 

using ternary perform switch in 1 line:

echo substr($row["et3"], -2) == ".0" ? intval($row["et3"]) : $row["et3"]; 

that expression stuffed existing printf() works as:

printf(" <tr> <td>%s</td> </tr>\n", (substr($row["et3"], -2) == ".0" ? intval($row["et3"]) : $row["et3"])); 

which can expressed in expanded form this, though ternary above more compact in existing printf() context.

if (substr($row["et3"], -2) == ".0") {   // truncate integer   // intval($row["et3"]) } else {   // return full original value (n.5)   // $row["et3"]; } 

finally, if want use in more 1 place, consider wrapping in function:

function truncint($value) {  return substr($value, -2) == ".0" ? intval($value) : $value;  } 

then use truncint($row["et3"]) in original printf()...