4

I've got a table with a bunch of decimal(12,2) fields that are supposed to hold money amounts. For some reason which I can't figure out one of them is trimming the value when either inserted or updated to a whole number. So if I enter in 2.2 into the PHP web form all that is saved is 2.00. It's not rounding because if I put in 2.9 it still saves 2.00.

All the columns are updated with a single update statement to the table, and this is the only one with a problem. For all the values all I'm doing is grabbing the value via $_POST['n'] and sticking the value into an array which I'm then passing to the WordPress $wpdb->query via $wpdb->prepare as shown below.

$wpdb->query(
    $wpdb->prepare($sql,$params)
);

If I go into phpmyadmin I can change the value and it'll take it.

The only difference between this column and the other columns is that this one was added to the table after the table was created, so it's farther to the right and was added via an ALTER TABLE command instead of being part of the CREATE table command.

If I dump the array from $params I can see the correct value, so it's being passed into the the database engine correctly (best I can tell).

I've tried changing the data type to decimal(12,3) and back but that didn't help any. Anyone seen this before or have an idea where to look?

UPDATE: As requested, the code to create the table.

        $sql = "CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}timesheet` (
            timesheet_id bigint(20) NOT NULL AUTO_INCREMENT,
            user_id bigint(20) NOT NULL,
            start_date datetime NOT NULL,
            entered_date datetime NOT NULL,
            client_name mediumtext NOT NULL,
            project_name mediumtext NULL,
            monday_hours numeric(12,2) NOT NULL,
            tuesday_hours numeric(12,2) NOT NULL,
            wednesday_hours numeric(12,2) NOT NULL,
            thursday_hours numeric(12,2) NOT NULL,
            friday_hours numeric(12,2) NOT NULL,
            saturday_hours numeric(12,2) NOT NULL,
            sunday_hours numeric(12,2) NOT NULL,
            total_hours numeric(12,2) NOT NULL,
            monday_desc mediumtext NULL,
            tuesday_desc mediumtext NULL,
            wednesday_desc mediumtext NULL,
            thursday_desc mediumtext NULL,
            friday_desc mediumtext NULL,
            saturday_desc mediumtext NULL,
            sunday_desc mediumtext NULL,
            per_diem_days numeric(6,2) NULL,
            hotel_charges numeric(12,2) NULL,
            rental_car_charges numeric(12,2) NULL,
            tolls numeric(12,2) NULL,
            other_expenses numeric(12,2) NULL,
            other_expenses_notes longtext NULL,
            week_complete tinyint(1) NOT NULL,
            marked_complete_by bigint(20) NULL,
            marked_complete_date datetime NULL,
            approved tinyint(1) NOT NULL,
            approved_by bigint(20) NULL,
            approved_date datetime NULL,
            invoiced tinyint(1) NOT NULL,
            invoiced_by bigint(20) NULL,
            invoiced_date datetime NULL,
            invoiceid bigint(20) NULL,
            ClientId bigint(20) NULL,
            mileage bigint(20) NULL,
            EmbargoPendingProjectClose tinyint(1) NULL,
            project_complete tinyint(1) NULL,
            ProjectId bigint(20),
            PRIMARY KEY (timesheet_id),
            INDEX IX_user_id_start_date (user_id, start_date),
            INDEX IX_approved (approved),
            INDEX IX_invoiced (invoiced)
            ) $charset_collate";
        $wpdb->query($sql);

Then later on the new problem column was added.

        $sql = "ALTER TABLE `{$wpdb->prefix}timesheet`
            ADD COLUMN flight_cost decimal(12,2)";
        $wpdb->query($sql);
| improve this question | | | | |
  • What is the base class of $wpdb? – Rick James Apr 10 '16 at 5:54
  • 1
    Sorry, been slammed and haven't had a chance to look back at this. @MaxVernon it's a WordPress plugin that I built myself. I'll update the question with the create table code that I used for the initial table creation. – mrdenny Apr 13 '16 at 23:00
  • Sounds like something in the plugin. – Max Vernon Apr 13 '16 at 23:04
  • When I dump the values from the POST the values are correct. The INSERT statement is just grabbing the values from the POST and sticking them into an array, which is passed to the function which uses the code in the original section to run the query. The query is pretty simple. When I var_dump the array it has the decimal data in the value. – mrdenny Apr 13 '16 at 23:05
1

Sounds like your PHP code is converting the string to a integer and chopping off the decimal place. Possibly in the prepare statement. You could try binding the param as a string, or possibly a cast as a (float).

Example PHP:

  $number='2.90';
  echo (int)$number;

Will output 2

$number='2.90';
echo (float)$number;

Will output 2.9

I would just leave it as a %s string.

| improve this answer | | | | |
0

I'm still not sure why it was happening, but I was able to fix it by changing the parameter to be passed in as a string (%s) instead of a number (%d).

| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.