SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Decimal Question

    Imagine a field with just two numerals - 540 and 2.8. How would you designate the field if you want them to display just like that, not 540.0 but 540?

    First, I tried Decimal 3,1. 540 displayed as 540, but all the fractions were rounded off. Thus, 2.8 became 3.

    So I changed the field to Char 3, with the same results. I went back to my spreadsheet and changed all the cells to TEXT before exporting to a CSV file. But I still get the same results.

    I checked out my table in phpMyAdmin, and it looks perfect - 540 and 2.8. So why doesn't it display like that???

    Below is the script I'm using to display it. The dates are in the fields YearBegan and YearEnded.

    Thanks.

    PHP Code:
      echo '<table class="sortphp" id="tab_geotime" style="font-size: 85%; line-height: 130%;">
               <tbody>'
    ;
      
    //<!-- BeginDynamicTable -->
      
    while ($row mysql_fetch_array ($res)) {

    // and add this in your while loop:
    $YearBegan_arr[] = $row['YearBegan'];
         echo 
    "<tr class=\""$row['IDParentTime'] ."\" id=\""$row['IDTime'] ."\"><"$_SERVER['PHP_SELF'] .'?id='$row['IDTime'] ."><td class='tdname'>"$row['NameTime'] ." "$row['TimeType'] ."</td>
          <td class=\"date\">" 
    number_format($row['YearBegan']) . "-" number_format($row['YearEnded']) . "</td>
          <td class=\"border\"></td></tr>\n"
    ;
           
    $nameTypes[]=$row['NameTime'];
      }
      }
      
    ?>
          </tr>
          </tbody>
          </table> 
    Thanks.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    use DECIMAL(9,2)

    truncate the display of the decimal points preferably in your application code, if necessary in the query

    Code:
    select replace(cast(numericfld as varchar(11))
           ,'.00', '') as displayfld
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    use DECIMAL(9,2)

    truncate the display of the decimal points preferably in your application code, if necessary in the query

    Code:
    select replace(cast(numericfld as varchar(11))
           ,'.00', '') as displayfld
    Thanks. I'm not sure what you mean by "application code," but I changed my query to this:

    PHP Code:
      $res mysql_query('SELECT * FROM timetype as TT, timefamarea as TF
        WHERE TT.IDType = TF.IDType AND TF.IDTime is not null
        ORDER BY NT2
        REPLACE(cast(numericfld as varchar(11)),'
    .00', '') as displayfld'); 
    I must have done it wrong, because I get an "unexpected T_DNUMBER" error.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    well, you wouldn't use "numericfld" as the column name, unless that were the actual name of your column, and you'd put that REPLACE expression into the SELECT list along with all the other columns you're selecting, not after the ORDER BY



    "application code" in this instance would be your php script

    it might be faster to return the number as a number and convert it to a string with php before displaying it, rather than converting the number to a string in sql and returning that (in addition, instead of rather than, if you persist in using the "select star" technique, which is not recommended)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    well, you wouldn't use "numericfld" as the column name, unless that were the actual name of your column, and you'd put that REPLACE expression into the SELECT list along with all the other columns you're selecting, not after the ORDER BY
    I see...numericfld = "numeric field." Am I also supposed to substitute the name of a field for "display field"? I tried this...

    PHP Code:
      $res mysql_query('SELECT * FROM timetype as TT, timefamarea as TF
        REPLACE(cast(YearBegan as varchar(11)),'
    .00', '') as displayfld
        WHERE TT.IDType = TF.IDType AND TF.IDTime is not null
        ORDER BY NT2'
    ); 
    and this...

    PHP Code:
      $res mysql_query('SELECT * FROM timetype as TT, timefamarea as TF
        REPLACE(cast(YearBegan as varchar(11)),'
    .00', '') as YearBegan
        WHERE TT.IDType = TF.IDType AND TF.IDTime is not null
        ORDER BY NT2'
    ); 
    ...but I still get the same error message.

    it might be faster to return the number as a number and convert it to a string with php before displaying it, rather than converting the number to a string in sql and returning that (in addition, instead of rather than, if you persist in using the "select star" technique, which is not recommended)
    You lost me there, but I use * because I'm displaying nearly ever field in the table. Are you saying I should specify each field instead?

    Thanks.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the alias that you assign to the REPLACE expression can be any name of your choosing, as you will need that name to reference that column in your php

    yes, i'm saying you should list every field that you want to use

    and the list of columns in the SELECT clause ends just before the FROM keyword, so you will have to move the REPLACE expression once again -- it belongs in the SELECT clause, because it creates another column in the result set

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •