Storing negative values in MySQL

[eluser]megabyte[/eluser]
I am building an inventory table. It needs to store addition of inventory and substraction of inventory.

Now I realize you can store a negative number but this is something I've never done before, so I'm trying to think about pros and cons.

If I allow the user to enter a negative value in a text field, I could check that the value only contains a '-.0-9' (I'llhave to play with this because I'm not a regular expressions expert) now is their a way to make sure in a regex their is only one instance of the '-'

And has anyone had issue allowing users to store negative numbers that later need to be calculated on? This is my biggest worry of course.

[eluser]Ben Edmunds[/eluser]
Well it is impossible to have a negative amount of something. So shouldn't you just substract from the total and then put that in the database?

Or better yet don't even do that, just have the inventory taken in and the inventory consumed.

[eluser]tomcode[/eluser]

If I allow the user to enter a negative value in a text field, I could check that the value only contains a ‘-.0-9’ (I’llhave to play with this because I’m not a regular expressions expert) now is their a way to make sure in a regex their is only one instance of the ‘-’

This You should be able to solve with a validation - prep - rule, see also (php) intval(), is_numeric().

Haven't had negative values yet but as long as it's a number and the zero is taken care of I don't see a problem.
edit: Haven't had negative values from user input, I've been happily calculating and sorting tables with it

[eluser]megabyte[/eluser]
Ben,


What I am talking about is an inventory transaction table.

so yes you would have positive and negative values.

positive values would be purchases

negative values would be when you remove it.

I also have to use FIFO which is another problem, and I have a solution hopefully that works.

As for making sure I store correct nagative values, I am now using this regex in the form validation:

Code:

preg_match("/^(\d|-)?(\d|,)*\.?\d*$/", $str);

this makes sure it is only a negative or positive number with a single decimal point

[eluser]flaky[/eluser]
since myself am not a very good regex guy I'd use callback functions from form validation class
eg

Code:

public function is_negative_float($number){
    if((float)$number && $number < 0)
        return true;
    else{
        $this->form_validation->set_message('is_negative_float', 'Enter only negative float numbers!');
        
        return false;
    }
}

[eluser]CroNiX[/eluser]
You really don't need to use neg numbers for this.
You have an inventory (positive number).
You have a purchase (positive number).
Now inventory is (inventory - purchase).
Do the math in php or in the db query.