Make negative values into 0?

Question

I am still trying to complete this, but I have encountered a problem where I can't seem to find a way to return all negative values into 0. I am seeing negative values being added into the db which is affecting my total calculation.

For example at the bottom of the code where it shows the datediff calculation. If first payment is due on 2014-12-01, but then say I had an purchase date that's after 2014-12-01 it will show a negative value, which I want to have it to write 0 in any negative values appear so it won't affect my total calculation's coding.

Any help will be appreciated! Thank you!

//* Using Escape Variables for Security *//
$purchase_date = mysqli_real_escape_string($con, $_POST['purchase_date']);
$mature_date = mysqli_real_escape_string($con, $_POST['mature_date']);
$amount = mysqli_real_escape_string($con, $_POST['amount']);
$unit = mysqli_real_escape_string($con, $_POST['unit']);
$purchaser_first = mysqli_real_escape_string($con, $_POST['purchaser_first']);
$purchaser_last = mysqli_real_escape_string($con, $_POST['purchaser_last']);
$purchaser_id = mysqli_real_escape_string($con, $_POST['purchaser_id']);
$purchaser_no = mysqli_real_escape_string($con, $_POST['purchaser_no']);
$purchaser_add = mysqli_real_escape_string($con, $_POST['purchaser_add']);
$beneficiary_first = mysqli_real_escape_string($con, $_POST['beneficiary_first']);
$beneficiary_last = mysqli_real_escape_string($con, $_POST['beneficiary_last']);
$beneficiary_id = mysqli_real_escape_string($con, $_POST['beneficiary_id']);
$beneficiary_no = mysqli_real_escape_string($con, $_POST['beneficiary_no']);
$beneficiary_add = mysqli_real_escape_string($con, $_POST['beneficiary_add']);
$bank_acc_name = mysqli_real_escape_string($con, $_POST['bank_acc_name']);
$bank_acc_no = mysqli_real_escape_string($con, $_POST['bank_acc_no']);
$bank_swift = mysqli_real_escape_string($con, $_POST['bank_swift']);
$bank_name = mysqli_real_escape_string($con, $_POST['bank_name']);
$bank_add = mysqli_real_escape_string($con, $_POST['bank_add']);
$bank_no = mysqli_real_escape_string($con, $_POST['bank_no']);
$agent = mysqli_real_escape_string($con, $_POST['agent']);

// Query with DATEDIFF calculation
$sql="INSERT INTO `peuicustomer` (
  purchase_date, mature_date, amount, unit, 
  purchaser_first, purchaser_last, purchaser_id, purchaser_no, purchaser_add, 
  beneficiary_first, beneficiary_last, beneficiary_id, beneficiary_no, beneficiary_add,
  bank_acc_name, bank_acc_no, bank_swift, bank_name, bank_add, bank_no, agent,
  first_days, first_payment, second_days, second_payment, third_days, third_payment, fourth_days, fourth_payment
) 
VALUES (
  '$purchase_date','$mature_date','$amount','$unit','$purchaser_first','$purchaser_last',
  '$purchaser_id','$purchaser_no','$purchaser_add', 
  '$beneficiary_first','$beneficiary_last','$beneficiary_id','$beneficiary_no','$beneficiary_add',
  '$bank_acc_name','$bank_acc_no','$bank_swift','$bank_name','$bank_add','$bank_no', '$agent',
  DATEDIFF('2014-12-01','$purchase_date'),
  (DATEDIFF('2014-12-01','$purchase_date') * '$amount' / 365 * 0.12),
  DATEDIFF('2015-03-01','2014-12-01'),
  (DATEDIFF('2015-03-01','2014-12-01') * '$amount' / 365 * 0.12),
  DATEDIFF('2015-06-01','2015-03-01'),
  (DATEDIFF('2015-06-01','2015-03-01') * '$amount' / 365 * 0.12),
  DATEDIFF('2015-09-01','2015-06-01'),
  (DATEDIFF('2015-09-01','2015-06-01') * '$amount' / 365 * 0.12)
)";

// Executing and error checking of query
if (!mysqli_query($con,$sql)) {
  die('Error: ' . mysqli_error($con));
}

echo "Client record has been added to the database!  Redirecting URL initiated, please wait...";



// Close MySQL
mysqli_close($con);
?>

</body>
</html>

I'm worried about several things here - first, you should be using prepared statements, which would absolve you of the need to escape things. Second, the result of `DATEDIFF(...)` is derived information, and shouldn't be stored (almost always). This is especially true, given that you have values that don't appear to be stored, that should be (what's `2012-12-01` for?). This table should probably be broken up further, especially for scheduling payments... you probably want at least 5. Calculations are performed (and stored?) with floating point, when it should be `DECIMAL` (the `.12`). — Clockwork-Muse, Nov 28 '14 at 06:09

score 1 · Accepted Answer · answered Nov 28 '14 at 04:54

1

IF('2014-12-01' > '$purchased_date',
  DATEDIFF('2014-12-01', '$purchased_date'),
  0
) * $amount / 365 * 0.12

same for other DATEDIFFs.

answered Nov 28 '14 at 04:54

Amadan

191,408
23
240
301

William Price · Answer 2 · 2014-11-28T05:30:26.607

1

Consider using GREATEST(0, ...), replacing ... with any positive or negative value. The function will not return anything lower than zero since one of the arguments (as written) is zero.

edited Nov 28 '14 at 05:30

answered Nov 28 '14 at 04:57

William Price

4,033
1
35
54

score 0 · Answer 3 · edited May 23 '17 at 11:50

0

Try wrapping the values that could potentially be negative and that you want converted to 0 in a CASE statement as per @RedFilter's answer here:

https://stackoverflow.com/a/3389734/3264286

CASE 
    WHEN [...potentially negative value...] < 0 then 0
    ELSE [...potentially negative value...]
END

edited May 23 '17 at 11:50

Community

1
1

answered Nov 28 '14 at 04:54

Delorian

330
1
3
13

Make negative values into 0?

3 Answers3