Data truncated for column 'sale_price' at row 1 - The MySQL Relational Database

Posted by Deeda (Deeda), 30 October 2006

I am having a problem with a form I use to add a record. I have created several fields, but I run into a problem if I leave a float or a date field blank. For example I have a field called sale_price. If I leave it blank, I'll get the following message "Data truncated for column 'sale_price at row 1. " If I put a number in there is no problem. How can I set it up so I have the choice of leaving it blank if so desired?

Here is the script I wrote to create the table:

<?php
$mysqli = mysqli_connect("localhost", "kelly", "ki", "lytledb");

if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
} else {
$sql = "CREATE TABLE closing(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, date_added datetime, date_modified datetime, representation varchar (50), property_type varchar (25), closing_office varchar (75), source_bus_closings varchar (50), source_bus_seller_rep varchar (50), date_contract date, date_of_closing date, buyer_settlement_agent varchar (50), seller_settlement_agent varchar (50), lender varchar (75), listing_real_estate_agent_l_name varchar (50), listing_real_estate_agent_f_name varchar (50), selling_real_estate_agent_l_name varchar (50), selling_real_estate_agent_f_name varchar (50), closing_rep_buyer_l_name varchar (50), closing_rep_buyer_f_name varchar (50), closing_rep_seller_l_name varchar (50), closing_rep_seller_f_name varchar (50), closer_seller_l_name varchar (50), closer_seller_f_name varchar (50),closer_buyer_l_name varchar (50), closer_buyer_f_name varchar (50), processor_l_name varchar (50), processor_f_name varchar (50), post_closer_l_name varchar (50), post_closer_f_name varchar (50), sale_price float, buyer_settlement_fee float, buyer_attorney_fee float, buyer_binder_fee float, buyer_title_search_fee float, other_buyer_fees float, seller_settlement_fee float, seller_attorney_fee float, seller_deed_prep float, seller_courier_fees float, other_seller_fees float, client_l_name varchar (50), client_f_name varchar (50), client_address varchar (150), client_city varchar (50), client_state char (2), client_zipcode varchar (10))";
$res = mysqli_query($mysqli, $sql);

if ($res === TRUE) {
echo "closing successfully created.";
} else {
printf("Could not create table: %s\n", mysqli_error($mysqli));
}

mysqli_close($mysqli);
}
?>

Thanks Deeda

Posted by admin (Graham Ellis), 30 October 2006

You haven't shown me your "insert" statement which, I suspect, is the one that's causing you the problems. If you just insert with "values", you can't leave the columns blank .. however, if you give a list of filed names in the insert you can shoose which walues you want to enter and which ones you don't want to enter. That latter is much more flexible and robust (especially if you're likely to be chaning columns around later), but more initial coding I'm afraid

Posted by Deeda (Deeda), 1 November 2006

Graham,

Here is my insert statement:

<html>
<head>
<title> Closing Insert Record</title>
</head>
<body>

<?php
$mysqli = mysqli_connect("localhost", "kelly", "ki", "lytledb");

if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();

} else {
$sql = "INSERT INTO closing (date_added, date_modified, representation, property_type, closing_office, source_bus_closings, source_bus_seller_rep, date_contract, date_of_closing, buyer_settlement_agent, seller_settlement_agent, lender, listing_real_estate_agent_l_name,listing_real_estate_agent_f_name, selling_real_estate_agent_l_name, selling_real_estate_agent_f_name, closing_rep_buyer_l_name, closing_rep_buyer_f_name, closing_rep_seller_l_name, closing_rep_seller_f_name, closer_seller_l_name, closer_seller_f_name, closer_buyer_l_name, closer_buyer_f_name, processor_l_name, processor_f_name, post_closer_l_name, post_closer_f_name, sale_price, buyer_settlement_fee, buyer_attorney_fee, buyer_binder_fee, buyer_title_search_fee,other_buyer_fees, seller_settlement_fee, seller_attorney_fee, seller_deed_prep, seller_courier_fees, other_seller_fees, client_l_name, client_f_name, client_address, client_city, client_state, client_zipcode, underwriter_l_name, underwriter_f_name, premium) VALUES (now(), now(), '".$_POST["representation"]."','".$_POST["property_type"]."', '".$_POST["closing_office"]."','".$_POST["source_bus_closings"]."', '".$_POST["source_bus_seller_rep"]."', '".$_POST["date_contract"]."', '".$_POST["date_of_closing"]."','".$_POST["buyer_settlement_agent"]."', '".$_POST["seller_settlement_agent"]."','".$_POST["lender"]."', '".$_POST["listing_real_estate_agent_l_name"]."','".$_POST["listing_real_estate_agent_f_name"]."', '".$_POST["selling_real_estate_agent_l_name"]."', '".$_POST["selling_real_estate_agent_f_name"]."', '".$_POST["closing_rep_buyer_l_name"]."','".$_POST["closing_rep_buyer_f_name"]."','".$_POST["closing_rep_seller_l_name"]."','".$_POST["closing_rep_seller_f_name"]."', '".$_POST["closer_seller_l_name"]."','".$_POST["closer_seller_f_name"]."', '".$_POST["closer_buyer_l_name"]."', '".$_POST["closer_buyer_f_name"]."', '".$_POST["processor_l_name"]."','".$_POST["processor_f_name"]."', '".$_POST["post_closer_l_name"]."','".$_POST["post_closer_f_name"]."', '".$_POST["sale_price"]."','".$_POST["buyer_settlement_fee"]."', '".$_POST["buyer_attorney_fee"]."', '".$_POST["buyer_binder_fee"]."', '".$_POST["buyer_title_search_fee"]."','".$_POST["other_buyer_fees"]."','".$_POST["seller_settlement_fee"]."','".$_POST["seller_attorney_fee"]."', '".$_POST["seller_deed_prep"]."','".$_POST["seller_courier_fees"]."', '".$_POST["other_seller_fees"]."','".$_POST["client_l_name"]."', '".$_POST["client_f_name"]."', '".$_POST["client_address"]."', '".$_POST["client_city"]."','".$_POST["client_state"]."','".$_POST["client_zipcode"]."','".$_POST["underwriter_l_name"]."','".$_POST["underwriter_f_name"]."','".$_POST["premium"]."')";
$res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));

mysqli_close($mysqli);

}
?>
<form method= "POST" action="closing_insert_form.php">
<input type="submit" value="Insert Another Record">
</form>
<form method= "POST" action="closing_interface.php">
<input type="submit" value="Closing Interface">
</form>
</body>
</html>

Posted by admin (Graham Ellis), 1 November 2006

Take a copy of your database .... then simplify your code inserting fewer and fewer fields until you bring it down to something that it's practical to debug!

Also ... you would do far better to parameterise your code so that you have an array of fields to be filled in and a loop to make up your statement.

Posted by Deeda (Deeda), 1 November 2006

I have simplified the insert statement:

<html>
<head>
<title> Closing Insert Record</title>
</head>
<body>

<?php
$mysqli = mysqli_connect("localhost", "kelly", "ki", "lytledb");

if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();

} else {
$sql = "INSERT INTO closing (date_added, date_modified, sale_price, buyer_settlement_fee) VALUES (now(), now(), '".$_POST["sale_price"]."','".$_POST["buyer_settlement_fee"]."')";

$res = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));

mysqli_close($mysqli);

}
?>
<form method= "POST" action="closing_insert_form.php">
<input type="submit" value="Insert Another Record">
</form>
<form method= "POST" action="closing_interface.php">
<input type="submit" value="Closing Interface">
</form>
</body>
</html>

I don't understand what you mean about the following statement, "Also ... you would do far better to parameterise your code so that you have an array of fields to be filled in and a loop to make up your statement." Please give an example.

You mentioned earlier that I couldn't leave a column blank if I inserted with "values." Does this apply to all column types or just float and date? It works with varchar. What else could I use instead of insert with "values?"

Thanks,
Deeda

Posted by admin (Graham Ellis), 2 November 2006

It looks like your immediate problem is that you're quoting a float value - numeric values should not be quoted.

By "Parameterising" I am suggesting that you don't write a long create or insert statement and name all the fields one by one in it. Better to have all the fields in an array and work through that as it makes the code more robust, shorter, and easier to follow. You'll find an example at the HTML end here but that could easily be extended to the MySQL aspects too. Further examples under this blog entry.

This page is a thread posted to the opentalk forum at www.opentalk.org.uk and archived here for reference. To jump to the archive index please follow this link.