MySQL error "Incorrect decimal value: for column rentprice at row 1"
Discuss MySQL error "Incorrect decimal value: for column rentprice at row 1" in the PHP Development forum on Dev Shed. MySQL error "Incorrect decimal value: for column rentprice at row 1" PHP Development forum discussing coding practices, tips on PHP, and other PHP-related topics. PHP is an open source scripting language that has taken the web development industry by storm.
Posts: 98
Time spent in forums: 1 Day 2 h 20 m 38 sec
Reputation Power: 1
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 }
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.
Posts: 98
Time spent in forums: 1 Day 2 h 20 m 38 sec
Reputation Power: 1
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]))
Posts: 98
Time spent in forums: 1 Day 2 h 20 m 38 sec
Reputation Power: 1
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>
Posts: 1,867
Time spent in forums: 1 Month 2 Weeks 1 Day 22 h 33 m 20 sec
Reputation Power: 813
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.
Quote:
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.
Posts: 7,931
Time spent in forums: 2 Months 7 h 49 m 26 sec
Reputation Power: 7053
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.
Posts: 9,805
Time spent in forums: 2 Months 3 Weeks 17 h 43 m 13 sec
Reputation Power: 6112
Quote:
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.
"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
Posts: 7,931
Time spent in forums: 2 Months 7 h 49 m 26 sec
Reputation Power: 7053
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.
Posts: 98
Time spent in forums: 1 Day 2 h 20 m 38 sec
Reputation Power: 1
Quote:
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?
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
