#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    98
    Rep Power
    2

    MySQL error "Incorrect decimal value: for column rentprice at row 1"


    I have an add inventory page where if user clicks rent checkbox, he must enter rentprice - same with sell.

    However, I keep getting MySQL error "Incorrect decimal value: for column rentprice at row 1"

    MySQL defined as
    Column: rentprice DECIMAL(8,2) - Default of NULL
    Column: sellprice DECIMAL(8,2) - Default of NULL

    Here is my code:
    PHP Code:
        if ((empty($_POST['rent'])) && (empty($_POST['sell']))) {

    $errors[] = 'You must select either Rentable or Sellable checkbox.'
    }
    if (
    $_POST['rent']) {
    $rent 1;
         if (
    $_POST['rentprice']) {
              
    $rentprice $_POST['rentprice'];
        } else { 
    $errors[] = 'If you select rentable, you must enter a rental price.';
    $rentprice NULL;
    }
    } else {
    $rent 0;     }
    if (
    $_POST['sell']) {
    $sell 1;
    if (
    $_POST['sellprice']) {
    $sellprice $_POST['sellprice'];
    } else { 
    $errors[] = 'If you select sellable, you must enter a sale price.';
    $sellprice NULL
    }
    } else { 
    $sell 0;
    }
    // Make Query
    $queryInsert "INSERT INTO page-mdl (man_key, cls_key, sub_cls_key, model, rent, sell, rentprice, sellprice) VALUES ('$man_key', '$cls_key', '$sub_cls_key', '$model', '$rent', '$sell', '$rentprice', '$sellprice')";
    $resultInsert = @mysql_query ($queryInsert); // Run Query 

    Oh yeah, and I get this error on the field that is NOT being entered. So If I only enter a Sale Price - It says incorrect decimal on rentprice and vice versa? So I think it has to do with the }else{ clause and the price = NULL or the original if ($_POST['rentprice']) { then }
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2007
    Location
    Purley, Surrey
    Posts
    248
    Rep Power
    36
    echo out $queryInsert so you can see what the query it is trying to run actually is.
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    98
    Rep Power
    2
    Originally Posted by TonyF123
    echo out $queryInsert so you can see what the query it is trying to run actually is.
    Code:
    INSERT INTO model (man_key, cls_key, sub_cls_key, model, rent, sell, rentprice, sellprice, description, video) VALUES ('13', '24', '30', 'A-Test', '1', '0', '69.99', '', 'thisdescrip', 'vidlink')
    Ok - This makes me believe that the $sellprice=NULL variable isn't even being reached. It is inside an incorrect if condition - which is blowing my mind right now.

    It's only 2 Dimensions deep, but I cannot figure it out.
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    98
    Rep Power
    2
    Ok I added another variable after the main else clause that defines sellprice and rentprice as 0 instead of NULL (For some reason I cannot insert NULL, but I can 0.

    It does work now, but inserts a 0.00 into database for the suppose to be NULL. I Guess I can make my querys read if $row[sellprice] = 0.00 instead of (empty($row[sellprice]))
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    98
    Rep Power
    2
    I researched jscript to make rentprice and sellprice input text boxes only appear when the rent or sell checkbox is marked, therefor bypassing the entire need to create those secondary if condition checks and null defines.

    PHP Code:
        <p>Rentable? <input type="checkbox" name="rent" value="1" onclick="showBox(this)">This item is available for Rental</p>
    <
    p><input type='text' name='rentprice' id='rentprice' value='Rental price per day' style="display:none" />
    <
    script type="text/javascript">
    function 
    showBox(elem){
    document.getElementById("rentprice").style.display=elem.checked?"block":"none";
    }
    </script></p>
    <p>Sellable? <input type="checkbox" name="sell" value="1" onclick="showBox2(this)">This item is available for Sale</p>
    <p><input type='text' name='sellprice' id='sellprice' value='Sale price' style="display:none"/>
    <script type="text/javascript">
    function showBox2(elem){
    document.getElementById("sellprice").style.display=elem.checked?"block":"none";
    }
    </script></p> 
    Elegance born by initial failure.
  10. #6
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1045
    Hi,

    your misconception is that a PHP null will become an SQL NULL. That's not the case. When you put a PHP null into a query string, it gets casted to an empty string (because that's how PHP works). If you want to insert an SQL NULL, you need to explicitly insert the string "NULL" (without the single quotes around it).

    You also need to work on the security of your scripts. This one is completely open to SQL injections, allowing everybody to manipulate the query. The mysql_functions are generally obsolete. Choose one of the contemporary extensions.



    Originally Posted by BitZoid
    I researched jscript to make rentprice and sellprice input text boxes only appear when the rent or sell checkbox is marked, therefor bypassing the entire need to create those secondary if condition checks and null defines.
    Client-side validations never suffice, because they can simply be bypassed.

    Comments on this post

    • E-Oreo agrees : beat me to it
    • npl disagrees
  12. #7
  13. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    When you cast a NULL value to a string it becomes an empty string (not the PHP string 'NULL'). Thus when you are building a SQL query in PHP, if you want to send a NULL value to MySQL you have to write the PHP string 'NULL' into the query.
    Last edited by E-Oreo; November 13th, 2012 at 08:33 PM.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  14. #8
  15. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,846
    Rep Power
    6351
    Originally Posted by E-Oreo
    When you cast a NULL value to a string it becomes an empty string (not the string 'NULL'). Thus when you are building a SQL query in PHP, if you want to send a NULL value to MySQL you have to write the string 'NULL' into the query.
    Make sure you don't quote NULL values, 'NULL' is the string 'NULL', 4 characters. NULL by itself, unquotes, is the value null.

    Comments on this post

    • E-Oreo agrees : Good point... let me update that to indicate that the value is a PHP string
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  16. #9
  17. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1045
    Already said all that above. But I guess ManiacDan has me on his ignore list?

    Comments on this post

    • npl disagrees : Good for him
  18. #10
  19. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    No, he was correcting my post because the way I worded it made it sound like you had to enclose the word NULL in single quotes inside the MySQL query in order to send a NULL value to the database.

    You did already say all of that in your post, but I had the thread sitting open for a long time and didn't see that you had posted before I did.
    Last edited by E-Oreo; November 13th, 2012 at 08:54 PM.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  20. #11
  21. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    98
    Rep Power
    2
    Originally Posted by Jacques1
    Hi,

    your misconception is that a PHP null will become an SQL NULL. That's not the case. When you put a PHP null into a query string, it gets casted to an empty string (because that's how PHP works). If you want to insert an SQL NULL, you need to explicitly insert the string "NULL" (without the single quotes around it).

    You also need to work on the security of your scripts. This one is completely open to SQL injections, allowing everybody to manipulate the query. The mysql_functions are generally obsolete. Choose one of the contemporary extensions.

    This application will be on an intranet, with only a few trusted users able to access it. But I would like to have security as well, so I could use trim($_POST, to prevent injection attempts right?

    I have the Jscript handling the problem of data integrity Is this ok? Should I just add trim to var's?

IMN logo majestic logo threadwatch logo seochat tools logo