Jump to content

Error inserting data-Incorrect date value: '' for column 'date_of_birth' at row1


benjamin_boothe

Recommended Posts

This is the msg I get when I try submit my form data to my db.

My character type for this field is 'DATE'.

YYYY-MM-DD is format for DATE and that I am typing in the form input field.

HTML Code for this field is :

[code]<TR>
    <TD vAlign=top align=left>Date Of Birth </TD>
    <TD vAlign=top align=left><INPUT TYPE="text" name="date_of_birth" VALUE="<? echo $dob ?>"/>
    <BR></TD></TR>[/code]

Where am I going wrong?
Link to comment
Share on other sites

It's just one field that isn't printing into the database.

What do you mean but echoing the query instead of running it?

This is the query I am running:

[code]
if (!mysqli_query($link, $insert)) {
      $msg = "Error inserting data - " .mysqli_error($link);
    } else {
      $msg = "Record succesfully added";
      // set vars to "" for next form input
      $t = $fn = $sn = $dob = $uk = $ms = $d = $es = $ad1 = $a = $tn = $c = $pc = $tw = $th =
      $fax = $em = "";
    }
    mysqli_close($link);
  }
[/code]
Link to comment
Share on other sites

[quote author=benjamin_boothe link=topic=105083.msg419600#msg419600 date=1156187287]
What do you mean but echoing the query instead of running it?
[/quote]

echo $insert; // show the actual query

Is your test value for $dob>1969-12-31??
Link to comment
Share on other sites

No AndyB I am  using 1985-02-11 as my test data.

This is what happens when I use echo $insert:

[code]
INSERT INTO policy_holder (id, title, first_name, surname, date_of_birth, uk_residency, marital_status, disability, employment_status, address_line_1, area, town, county, post_code, telephone_work, telephone_home, fax_number, email) VALUES ( NULL, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '')
Error inserting data - Incorrect date value: '' for column 'date_of_birth' at row 1
[/code]
Link to comment
Share on other sites

If that's what you get as a query then it isn't getting any data at all from your form!

I assume the default value for date in your database definition is 0000-00-00 which is why a query with a single blank space is the wrong format.

I'd also suggest making id an auto-increment column.
Link to comment
Share on other sites

[quote author=benjamin_boothe link=topic=105083.msg419630#msg419630 date=1156190133]
What is meant by a query with a single blank space?
[/quote]

I thought that the query you quoted in an earlier post had ' ' as the value to be applied to the date field.  Must be time for a nap.
Link to comment
Share on other sites

Now I get this:
[quote]Error inserting data - Column count doesn't match value count at row 1[/quote]

I tried to add 'NULL' or '0' before instead of '' in VALUES but I get this message instead:
[quote]Error inserting data - Column count doesn't match value count at row 1[/quote]

Do I need to add a variable for 'id' in this part of the query statement:
[quote]// set vars to "" for next form input
      $t = $fn = $sn = $dob = $uk = $ms = $d = $es = $ad1 = $a = $tn = $c = $pc = $tw = $th = $fax = $em = "";[/quote]

I'm very grateful for your time, I've spent hours and hours at the same point!!

Link to comment
Share on other sites

This is the query used:

[quote]// if all data is there, build query
  if ($msg=="") {
    $insert = "INSERT INTO policy_holder
    (id, title, first_name, surname, date_of_birth, uk_residency, marital_status,
    disability, employment_status, address_line_1, area, town, county, post_code,
    telephone_work, telephone_home, fax_number, email)
    VALUES ('', '$t', '$fn', '$sn', '$dob', '$uk', '$ms', '$d', '$es', '$ad1', '$a',
    '$tn', '$c', '$pc', '$tw', '$th', '$fax', '$em')";

    // open db connection
    include 'includes/db_conn.txt';

    // execute query and check for success
    if (!mysqli_query($link, $insert)) {
      $msg = "Error inserting data - "  .mysqli_error($link);
    } else {
      $msg = "Record succesfully added";
      // set vars to "" for next form input
      $t = $fn = $sn = $dob = $uk = $ms = $d = $es = $ad1 = $a = $tn = $c = $pc = $tw = $th =
      $fax = $em = "";
    }
    mysqli_close($link);
  }
[/quote]

But it produces this error:
[quote]Error inserting data - Out of range value adjusted for column 'id' at row 1[/quote]

What can I do to get this form working correctly??
Link to comment
Share on other sites

How strange.

Can you do two more things:

First change this line:
[code]  $msg = "Error inserting data - "  .mysqli_error($link);[/code]

to this, so that the actual query string appears with the error message:

[code]  $msg = "Error inserting data - "  .mysqli_error($link). " with query ". $insert;[/code]

And also post your database table field definitions.

Surely, we can get to the bottom of what ought to be a really straightforward issue.
Link to comment
Share on other sites

Here is the error message I get now with echoing the query in the error:

[quote]
Error inserting data - Incorrect date value: '' for column 'date_of_birth' at row 1 with query INSERT INTO policy_holder (id, title, first_name, surname, date_of_birth, uk_residency, marital_status, disability, employment_status, address_line_1, area, town, county, post_code, telephone_work, telephone_home, fax_number, email) VALUES (NULL, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '')
[/quote]

I typed in NULL for my id auto_increment value because it worked when I entered a record via SQL command line client.

Here are my table definitions, may be one day we'll get to the bottom of this:
[img]http://localhost/policy_def.gif[/img]
Link to comment
Share on other sites

This is what works error-free for me using a MySQL database:

[code]<?php
// make database connection
$db_host = "localhost";
$db_login = "****"; // your value
$db_pass = "****"; // your value
$db_name = "****"; // your value

mysql_connect($db_host, $db_login, $db_pass) or die ("Can't connect!");
mysql_select_db($db_name) or die ("Can't open database!");

$query = "
INSERT INTO policy_holder VALUES ('', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '')";

echo $query. "<br><br>";

$result = mysql_query($query) or die("Error: ". mysql_error(). "<br>with query ". $query);
?>[/code]

And the database structure is as below (an SQL query you can execute if you want to create a new table)

[code]DROP TABLE IF EXISTS policy_holder;
CREATE TABLE policy_holder (
  id smallint(11) NOT NULL auto_increment,
  title varchar(16) NOT NULL default '',
  first_name varchar(32) NOT NULL default '',
  surname varchar(64) NOT NULL default '',
  date_of_birth date NOT NULL default '0000-00-00',
  uk_residency enum('Yes','No') NOT NULL default 'Yes',
  marital_status enum('Married','Single','Divorced','Widow/er') NOT NULL default 'Married',
  disability varchar(64) NOT NULL default '',
  employment_status enum('Employed','Unemployed') NOT NULL default 'Employed',
  area varchar(64) NOT NULL default '',
  town varchar(32) NOT NULL default '',
  county varchar(32) NOT NULL default '',
  post_code varchar(12) NOT NULL default '',
  telephone_work varchar(16) NOT NULL default '',
  telephone_home varchar(16) NOT NULL default '',
  fax_number varchar(16) NOT NULL default '',
  email varchar(64) NOT NULL default '',
  KEY id (id)
) TYPE=MyISAM;[/code]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.