asked on

html format - currency, qty, date

I have a SQL application that sends email in html. My problem is the format:

currency - I need it with dollar sign, two decimal places, right justified. It currently has no dollar sign, no decimal places unless the value has them and is left justified.

qty - I need it right justified. It currently is left justified

date - I need it in mm/dd/yy format. Currently the month and day are one or two characters and the year is four characters.

Here is my code:

{BEGIN*HTML}
<table class='mytable'>
      <thead>
            <tr class='mytable'>
                  <th class='mytable'>Line</th>
                  <th class='mytable'>Delivery</th>
                  <th class='mytable'>Qty</th>
                  <th class='mytable'>Part Number</th>
                  <th class='mytable'>Description</th>
                  <th class='mytable'>Unit Price</th>
                  <th class='mytable'>Ent'd Price</th>
                  <th class='mytable'>Due Date</th>
            </tr>
      </thead>
      <tbody>
            {BEGIN*REPEAT}
            <tr class='mytable'>
                  <td class='mytable'>{Line}</td>
                  <td class='mytable'>{Delivery}</td>
                  <td class='mytable'>{Current_Qty}</td>
                  <td class='mytable'>{Part_Number}</td>
                  <td class='mytable'>{Description}</td>
                  <td class='mytable'>{Unit_Price}</td>
                  <td class='mytable'>{Extended_Price}</td>
                  <td class='mytable'>{Due_Date}</td>
            </tr>
            {END*REPEAT}
      </tbody>
</table>
{END*HTML}

ASKER CERTIFIED SOLUTION

hielo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ginsburg7

ASKER

That helps a little, but it seems every value is treated as text. How do I tell it what is a number or date?

hielo

>> How do I tell it what is a number or date?
You need to use the appropriate function. Assuming you have:
$total=3;

then
echo number_format($total, 2);//will change 3 to 3.00
http://us2.php.net/manual/en/function.number-format.php

For dates, you can use strtotime() to convert a string date to an actual date
$date="2009-07-12 12:00:00";

$actualDate=strtotime($date);

then use the date() function to reformat the actual date:
$purchaseDate = date('m/d/y',$actualDate);

You can of course combine date() and strtotime()::
$date="2009-07-12 12:00:00";
$purchaseDate = date('m/d/y', strtotime($date) )

hielo

In case you need other formats, refer to:
date:
http://us2.php.net/manual/en/function.date.php

ginsburg7

ASKER

In the database, the numbers fields are numbers and date fields are dates. It's just when the html code is run it seems to convert numbers and dates to text.

hielo

I don't know what db you are using, but most dbs have formatting functions. For example, if you are currently doing:
SELECT * FROM table...

you need to STOP that practice ( of using * instead of the field names) and actually name the fields:
SELECT price, quantity, total,... FROM Table...

So if in your db price is set to 1 but you want 1.00

then in mysql, you would use:
SELECT FORMAT(price,2) as price, quantity, total,... FROM Table...

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_format

Similarly, there is a formatting function for dates - date_format()
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format

If you are not using mysql, you just need to lookup the corresponding functions for your db.