What is wrong with mySQL query?

[Harm_Millaard]

My query is:

mysql_select_db($database_mysql, $mysql);

$query_Recordset1 = 'SELECT *, FORMAT(`RPI`,2) As RPI,`CPU`*`Cores` As Phys_Cores, `OS_disks`+`Project_disks`+`Output_disks` as Disks, FORMAT(`Gain`,1) As Gain FROM `Personal_data`,`Benchmark_results` WHERE Personal_data.Ref_ID = Benchmark_results.Ref_ID '.$conditions.' ORDER BY Total, RPI ASC' ;

Notice the 2 decimals in the Format(`RPI`,2) statement, but I get:

So how do I get this shown as 1.00 instead of 1?

For the site in question, look here: http://ppbm5.com/DB-PPBM5-2.php

[Rob Hecker2]

If in your table structure you define the field as TYPE decimal (7,2) then you won't need to try to define it in the query, it will always return a decimal of 7 digits in length, 2 to the right of the decimal, and your '1' will return as '1.00'.

[Harm_Millaard]

The field is now defined as FLOAT 6.2, but even after removing the higlighted FORMAT part of the query, it still shows up as 1 and not as 1.00 as I want it. Changing it to DECIMAL 6.2 still shows the same problem.

[Rob Hecker2]

Your argument should be

AS DECIMAL(7,2))

[Harm_Millaard]

Sorry for this stupid question, but where in the original query do I need to make these changes:

$query_Recordset1 = 'SELECT *, FORMAT(`RPI`,2) As RPI,`CPU`*`Cores` As Phys_Cores, `OS_disks`+`Project_disks`+`Output_disks` as Disks, FORMAT(`Gain`,1) As Gain FROM `Personal_data`,`Benchmark_results` WHERE Personal_data.Ref_ID = Benchmark_results.Ref_ID '.$conditions.' ORDER BY Total, RPI ASC' ;

Where do I put the "AS DECIMAL(7,2))" in this statement? All I get is mySQL errors in the query, so I must be doing something terribly wrong here.

[Rob Hecker2]

The structure you are using - SELECT *, FORMAT(RPI,2) AS RPI - works for me with the exception of the quotes you have around your field names. Using this structure I can return 322.2145 as 322.21 and 322 as 322.00.

[Harm_Millaard]

Rob,

I discovered it is not so much the query itself that causes this, but the Linecraft code on the page. I've asked them for help on this. Thanks for your effort, but at least now I know it is not my code problem, but Linecraft.

[Harm_Millaard]

It appears to be the problem with the statement:

dsl.setColumnType("RPI", "number");

which automatically truncates trailing zeroes, so a number 1.00 is displayed as 1.

I have looked at ways to force 2 decimals, but have not found a solution. It might be that this must be entered in the statement:

<td align="right" ...>{RPI}</td>

but I'm at a loss of how to solve this issue.

[Rob Hecker2]

Then, if it's acceptable to do it using PHP, you can do it like this:

$rpi = number_format($rpi, 2, '.','');

[bregent]

I'm not sure what Linecraft is. Aren't you using PHP and can't you use number_format() ?

[Harm_Millaard]

LineCraft is a supplier of extensions for DW and specifically I use the Spry DataUtils here. Alex gives good support, but here he was not able to help. I took Rob's advice and inserted the format_number statement in the .php code, but for some mysterious reason it did not work. Well, it is not the end of the world for me, since I'm moving over to Joomla! and the extensions there altogether, but it would have been nice to have this solved.