Jump to content


Photo

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


  • Please log in to reply
16 replies to this topic

#1 benjamin_boothe

benjamin_boothe
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 21 August 2006 - 06:17 PM

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 :

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


Where am I going wrong?

#2 shocker-z

shocker-z
  • Members
  • PipPipPip
  • Advanced Member
  • 864 posts
  • LocationNottingham

Posted 21 August 2006 - 06:23 PM

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
www: www.ukchat.ws | irc: irc.ukchat.ws chan: #blufudge

#3 benjamin_boothe

benjamin_boothe
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 21 August 2006 - 07:08 PM

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:

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);
  }


#4 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 21 August 2006 - 07:26 PM

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


echo $insert; // show the actual query

Is your test value for $dob>1969-12-31??
Legend has it that reading the manual never killed anyone.
My site

#5 benjamin_boothe

benjamin_boothe
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 21 August 2006 - 07:35 PM

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

This is what happens when I use echo $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 ( NULL, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '') 
Error inserting data - Incorrect date value: '' for column 'date_of_birth' at row 1


#6 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 21 August 2006 - 07:50 PM

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.
Legend has it that reading the manual never killed anyone.
My site

#7 benjamin_boothe

benjamin_boothe
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 21 August 2006 - 07:55 PM


The id column is in auto_increment.

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

#8 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 21 August 2006 - 08:04 PM

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


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.
Legend has it that reading the manual never killed anyone.
My site

#9 benjamin_boothe

benjamin_boothe
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 21 August 2006 - 09:40 PM

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

As I still keep getting the same message.

#10 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 21 August 2006 - 10:25 PM

$query = "INSERT into your_table_name (id, ...... ) VALUES ('', .....)

Put nothing in and it'll auto-increment.
Legend has it that reading the manual never killed anyone.
My site

#11 benjamin_boothe

benjamin_boothe
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 21 August 2006 - 11:08 PM

Now I get this:

Error inserting data - Column count doesn't match value count at row 1


I tried to add 'NULL' or '0' before instead of '' in VALUES but I get this message instead:

Error inserting data - Column count doesn't match value count at row 1


Do I need to add a variable for 'id' in this part of the query statement:

// set vars to "" for next form input
      $t = $fn = $sn = $dob = $uk = $ms = $d = $es = $ad1 = $a = $tn = $c = $pc = $tw = $th = $fax = $em = "";



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



#12 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 21 August 2006 - 11:27 PM

Column count doesn't match value count


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.
Legend has it that reading the manual never killed anyone.
My site

#13 benjamin_boothe

benjamin_boothe
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 21 August 2006 - 11:50 PM

This is the query used:

// 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);
  }


But it produces this error:

Error inserting data - Out of range value adjusted for column 'id' at row 1


What can I do to get this form working correctly??

#14 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 22 August 2006 - 12:24 AM

How strange.

Can you do two more things:

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

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

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

And also post your database table field definitions.

Surely, we can get to the bottom of what ought to be a really straightforward issue.
Legend has it that reading the manual never killed anyone.
My site

#15 benjamin_boothe

benjamin_boothe
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 22 August 2006 - 10:02 AM

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

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, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '')


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:
Posted Image


#16 benjamin_boothe

benjamin_boothe
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 22 August 2006 - 05:00 PM

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

#17 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 22 August 2006 - 11:59 PM

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

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

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

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;

Legend has it that reading the manual never killed anyone.
My site




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users