ADDing decimal when CREATING the table

[phppup]

Can someone five me the correct code or format to use that will allow my TINYINT columns to carry TWO decimal places when creating my table?  I have a script for creating the table and columns, but only recently learned that I need to ADD the TWO decimal place FUNCTION in order to get whole dollar values to display WITH the trailing zeros (ie.:2.00).  I don't expect to need any values greater than xxx.xx in my columns, I'm just not clear on how to get the desired script when running the initial CREAT script.

 

Thanks

[litebearer]

use decimal or float for your column/field type

[Psycho]

Well, you need to decide if you will EVER need to store non whole-dollar values in the database. If so, then you shoudl definitely use a float/decimal type in the database. But, even then you should probably force the format when it is displayed to be what you want.

 

If you will ONLY ever have whole-dollar amounts you can stick with an int.

 

Some options for formatting the value:

 

If you are going to stay with ints, you could simply append '.00' to the value as you display it. It would work, but seems hokey to me.

 

Use number_format(). This would work with any type of value that can be interpreted as a number. This is probably the easiest implementation.

 

Use money_format(). This is very useful if you ever want to account for localization.

[phppup]

The field will ONLY be holding numeric values that are either whole values (with trailing zeros desired) 1.00, 2.00, 3.00, etc

Or havles: 1.50, 2.50, 3.50 etc.

 

But when queries are used, I want the values to line up evenly (the zeros on the whole numbers resolve this)

1.00

1.50

2.00

2.50 etc.

 

for that, I just NEED THE CODE format that goes into the CREATE TABLE function.

 

If there are alternative solutions, I will have to c onsider them.

Is there a way to edit the results of a statement that asks  SQL= SELECT qty FROM mytable

so that the results are not

  1

1.5

  2

2.5 etc.?

 

Note: there are too many columns to use VARCHAR (which would provide the desired result)

[Psycho]

Your data storage should NOT be used to determine the format of the data in the output. The data storage should simply be the correct type based upon the value to be stored. You previously stated that you were storing an int, but now you state that the values may or may not have 50 cents on them. So, I'm really not understanding what you really want/need.

 

For monetary values you should be using a "numeric" or "decimal" type: http://dev.mysql.com/doc/refman/4.1/en/fixed-point-types.html

 

But, you should then add the necessary PHP code to force the format for whatever purposes you need in the output. Again. number_format() will do this for you.

 

$value = "1.5";
echo number_format($value, 2); //Output: 1.50

[phppup]

Psycho... thanks for all the help!

 

In reality, I will probably ONLY occasionally require ONE decimal place.  And realistically, it will present itself when the INPUT contains the halved-value.

 

In those instances where the .5 is present, it sort of takes care of itself.

 

So is the number_format still recommended?  If so, why?

(i AM learning!!)

[Psycho]

If you want your numbers to be consistently formatted then, yes, number_format() is what I would recommend. That is the whole point of that function to ensure the format of the value is precisely what you want it to be.

 

Although, I'm still trying to wrap my head around how you have tinyint values that have .5 remainders.

[phppup]

I am recording orders for the sale of items.

People can order cases by the WHOLE or HALF quntity.

Thus, 1.0, 1.5,2.0, 2.5 etc.

 

I didn't initially realize that DECIMAL and FLOAT were COLUMN TYPES in their own right, I mistakenly thought they were OPTIONS for the INT values.

 

So, at this point, I suppose I can use EITHER the DECIMAL (or FLOAT for this instance, although still shakey as I've heard accuracy is an issue) or TINYINT with number_formatting to get the result I want and still be following the proper protocols.

[Psycho]

Again, how do you store a 1/2 value with an INT? Makes no sense unless you are doing some "hack" such as "15" really means 1.5, "20" means 2.0, etc. That's a really poor solution. You don't have to worry about accuracy issues if you use a decimal type with a fixed precision of n.1. The n stands for the TOTAL number of digits you want to allow (before and after the decimal), and the 1 stands for the number of digits to allow after the decimal.

 

Do it right and you will avoid many potential problems.

[phppup]

YES. I'm going to use DECIMAL with 4,1.

 

But in testing, I used INT and input the decimalled values and it worked.

The only "issue" I had was that they had no zeros for the whole numbers.

This was unlike the values that I had in the VARCHAR fields which DID add the zeros, but require too much disk space.

I suppose that this trail of making ALL the right moves for ALL the wrong reasons has ultimately lead me to the CORRECT answer (for whatever reasons).

Thanks for all the help.