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

[benjamin_boothe]

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?

[shocker-z]

try echoing your query instead of running it and just double check that it is actualy showing the query you want also if still not working maby paste your php as the HTML is totaly useless really


Regards
Liam

[benjamin_boothe]

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]

[AndyB]

[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??

[benjamin_boothe]

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]

[AndyB]

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.

[benjamin_boothe]

The id column is in auto_increment.

What is meant by a query with a single blank space?

[AndyB]

[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.

[benjamin_boothe]

What VALUE can I use for my id column that is declared as auto_increment.

As I still keep getting the same message.

[AndyB]

[code]$query = "INSERT into your_table_name (id, ...... ) VALUES ('', .....)[/code]

Put nothing in and it'll auto-increment.

[benjamin_boothe]

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!!

[AndyB]

[quote]Column count doesn't match value count[/quote]

The number of columns named in the INSERT query is not the same as the number of values in the VALUES group.

Post the exact query you use that produces that error.

[benjamin_boothe]

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??

[AndyB]

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.

[benjamin_boothe]

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]

[benjamin_boothe]

Does anybody have any ideas on where I'm going wrong???

[AndyB]

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]