quirky ORDER BY issue

[radar]

I have a query that is effectively:

 

SELECT * FROM tables WHERE p_id = $p_id ORDER BY months_remain ASC

 

The query gets run correctly, but the results aren't correct at all..  It happens in my code, as well as in phpmyadmin and I can't figure out why..  I'm using php 5 and mysql 5.0.91

 

the output of the query looks like:

 

Array
(
    [0] => Array
        (
            [id] => 10
            [u_id] => 2
            [p_id] => 1
            [descrip] => Car 2
            [reason] => 
            [interest] => 0.06
            [months_remain] => 196.655857568
            [month_payment] => 520
            [principal_debt] => 65000
            [real_debt] => 102261.045936
        )

    [1] => Array
        (
            [id] => 6
            [u_id] => 2
            [p_id] => 1
            [descrip] => car
            [reason] => 
            [interest] => 0.052
            [months_remain] => 25.3967600188
            [month_payment] => 100
            [principal_debt] => 2400
            [real_debt] => 2539.67600188
        )

    [2] => Array
        (
            [id] => 7
            [u_id] => 2
            [p_id] => 1
            [descrip] => house
            [reason] => house
            [interest] => 0.052
            [months_remain] => 254.07482305
            [month_payment] => 1300
            [principal_debt] => 200000
            [real_debt] => 330291.00
        )

)

 

note that in this list that it is not sorted by months_remain as it's showing that 196.65.... is less than 25.3967....

 

any ideas why this might be happening?

[PFMaBiSmAd]

What data type is your months_remain column? I'll bet it is a character type and not a numerical type?

 

Character strings are sorted and compared, character by character, starting with the left most character. Since the 1 in the 196.xxx is less the the 2 in the 25.yyy, 196.xxx comes before 25.yyy.

 

You need to save numbers using a numerical data type. This will allow your numbers to be treated as numbers and it will also reduce the amount of storage required and it will speed up all the operations on those values.

[radar]

is it varchar, but which numerical datatype to use?  decimal and int both seem to remove the decimal points.

[PFMaBiSmAd]

You would use decimal.

DECIMAL[M,D]

 

M is the total number of digits (the precision) and D is the number of digits after the decimal point (the scale). The maximum number of digits (M) for DECIMAL is 65. The maximum number of supported decimals (D) is 30. If D is omitted, the default is 0. If M is omitted, the default is 10.

 

[radar]

decimal screws it up though..  i have no way of knowing how long the strings should or will be...  and all the extra 0's seem to screw something up in the math i think...  i put type double in, which seems to allow my decimals.. and with nothing added in (65,30) it seems to work with me manually entering them in which means it should work when they are generated?

 

note: its generated from

 

$n = -log(1-($P * $i / ($M * $q))) / ($q * log(1+($i/$q)));

 

where $P is principal debt, $i is interest, $M is monthly payment and $q is 12 (12 months in a year).. then that number is multiplied by 12 to get the total number of months. so it'll always be a decimal, i just have no way of knowing how long it'll be.

[Muddy_Funster]

That formula makes absoloutly no sense to me, neither does using (65, 30) right enough.

[radar]

what that formula does, is take the numbers...  for P i M and q, and calculate how long it would take to pay off a debt in years.. then to get the months, times it by 12..

[Muddy_Funster]

intrestingly, I have never had to work out the -log or log of anything when working with date variance.

 

Also, I don't think that you are calculating the interest properly.  Interest on lones and debts are a variable percentage on the outstanding amout based on an anual percentage rate of payment, not taken against the overall outstanding amount at time of borrowing - at least in the UK it is.