inserting date error

newbie_07 · May 14, 2008

Hi gurus:

I want to insert current date into database.

I have made a column named current_date and its datatype is date.

I want to insert the current date into it.

Here's my code:

$sql = "INSERT INTO invoices SET client_id='$_POST[c_id]', service_name='$_POST[s_name]',
	total_price='$_POST[t_price]', current_date='$date' , due_date='$_POST[d_date]' ";

I got the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'current_date='2008-05-14' , due_date='march, 5, 2006'' at line 3

You can see the in the current_date value is showing but its not inserting in database.

Please help

MadTechie · May 14, 2008

try this

a few fixes and changed $date to CURDATE()

$sql = "INSERT INTO invoices SET client_id='{$_POST['c_id']}', service_name='{$_POST['s_name']}', total_price='{$_POST['t_price']}', current_date='CURDATE()' , due_date='{$_POST['d_date']}' ";

if this is wrong can you post how your setting $date

paul2463 · May 14, 2008

it could be if you have set your due_date column as a datetime then it will be expecting it to come at it in the format yyyy-mm-dd, you could try

$sql = "INSERT INTO invoices SET client_id='$_POST[c_id]', service_name='$_POST[s_name]',
total_price='$_POST[t_price]', current_date='$date' , due_date=DATE_fORMAT('$_POST[d_date]' , '%Y-%m-%d')";

newbie_07 · May 14, 2008

try this

a few fixes and changed $date to CURDATE()
$sql = "INSERT INTO invoices SET client_id='{$_POST['c_id']}', service_name='{$_POST['s_name']}', total_price='{$_POST['t_price']}', current_date='CURDATE()' , due_date='{$_POST['d_date']}' ";
if this is wrong can you post how your setting $date

Still not working.

Here's my full code:


$date = date("Y-m-d");

$sql = "INSERT INTO invoices SET client_id='$_POST[c_id]', service_name='$_POST[s_name]', service_description='$_POST[s_desc]',
	comments='$_POST[comments]', init_price='$_POST[i_price]', price='$_POST[price]', adjustments='$_POST[adj]',
	total_price='$_POST[t_price]', current_date='CURDATE()' , due_date='$_POST[d_date]' ";

Error is:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'current_date='CURDATE()' , due_date='march, 5, 2006'' at line 3

MadTechie · May 14, 2008

Opps shouldn't have the quotes on CURDATE()

try

$sql = "INSERT INTO invoices SET client_id='{$_POST['c_id']}', service_name='{$_POST['s_name']}', total_price='{$_POST['t_price']}', current_date=CURDATE() , due_date='{$_POST['d_date']}' ";

conker87 · May 14, 2008

$sql = "INSERT INTO invoices SET client_id='{$_POST['c_id']}', service_name='{$_POST['s_name']}', total_price='{$_POST['t_price']}', current_date=CURDATE() , due_date='{$_POST['d_date']}' ";

$sql = "INSERT INTO invoices SET client_id='$_POST[c_id]', service_name='$_POST[s_name]', total_price='$_POST[t_price]', current_date=CURDATE() , due_date='$_POST[d_date]' ";

Will both work

And thanks MadTechie, finally discovered the BB code to make the syntax colour coded without the constant use of <?php ?>, staring at me in the face '-.-

MadTechie · May 14, 2008

$sql = "INSERT INTO invoices SET client_id='{$_POST['c_id']}', service_name='{$_POST['s_name']}', total_price='{$_POST['t_price']}', current_date=CURDATE() , due_date='{$_POST['d_date']}' ";

$sql = "INSERT INTO invoices SET client_id='$_POST[c_id]', service_name='$_POST[s_name]', total_price='$_POST[t_price]', current_date=CURDATE() , due_date='$_POST[d_date]' ";

Will both work

you should use

client_id='{$_POST['c_id']}'

or

"client_id='".$_POST['c_id']'."etc

And thanks MadTechie, finally discovered the BB code to make the syntax colour coded without the constant use of <?php ?>, staring at me in the face '-.-

Noooooooooooooooooooooooo.

newbie_07 · May 14, 2008

$sql = "INSERT INTO invoices SET client_id='{$_POST['c_id']}', service_name='{$_POST['s_name']}', total_price='{$_POST['t_price']}', current_date=CURDATE() , due_date='{$_POST['d_date']}' ";
$sql = "INSERT INTO invoices SET client_id='$_POST[c_id]', service_name='$_POST[s_name]', total_price='$_POST[t_price]', current_date=CURDATE() , due_date='$_POST[d_date]' ";
Will both work
you should use

client_id='{$_POST['c_id']}'

or

"client_id='".$_POST['c_id']'."etc

And thanks MadTechie, finally discovered the BB code to make the syntax colour coded without the constant use of <?php ?>, staring at me in the face '-.-

Noooooooooooooooooooooooo.

I have applied both, still not working.

Here's the Query:

$sql = "INSERT INTO invoices SET client_id='{$_POST['c_id']}', service_name='{$_POST['s_name']}',
service_description='{$_POST['s_desc']}', comments='{$_POST['comments']}',
init_price='{$_POST['i_price']}', price='{$_POST['price']}', adjustments='{$_POST['adj']}',
total_price='{$_POST['t_price']}', 
current_date=CURDATE() , due_date='{$_POST['d_date']}' ";

This is the result from: print $sql;

INSERT INTO invoices SET client_id='3', service_name='Fan Cleaning', service_description='Bottom to Top', 
comments='Web Development', init_price='50', price='40', adjustments='20', 
total_price='30', current_date=CURDATE() , due_date='march, 5, 2007' 
You have an error in your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use near 'current_date=CURDATE() , 
due_date='march, 5, 2007'' at line 4

MadTechie · May 14, 2008

whats the field type of due_date ?

if its date you need to reformat the date to the same at the $date

a table structure dump will help

newbie_07 · May 14, 2008

whats the field type of due_date ?

if its date you need to reformat the date to the same at the $date

a table structure dump will help

No No ...

The field type of due_date is varchar and i am simply getting a value from a textbox. It has no problem.

I want that whenever a record is inserted, the current date should be inserted into the current_date column and its datatype is date.

MadTechie · May 14, 2008

My Test works fine

Dump

-- 
-- Table structure for table `Test`
-- 

CREATE TABLE `test` (
  `uID` mediumint( NOT NULL auto_increment,
  `Date` date NOT NULL,
  PRIMARY KEY  (`uID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

My SQL statment

INSERT INTO `Test`.`Test` (
`uID` ,
`Date`
)
VALUES (
NULL , CURDATE( )
);

edit (tagged it)

edit#2 try

changing CURDATE( ) to NOW( )

newbie_07 · May 14, 2008

Here's the table structure:

--
-- Table structure for table `invoices`
--

CREATE TABLE `invoices` (
  `invoice_id` int(50) NOT NULL auto_increment,
  `client_id` int(50) default NULL,
  `service_name` varchar(255) default NULL,
  `service_description` varchar(255) default NULL,
  `comments` varchar(255) default NULL,
  `init_price` int(50) default NULL,
  `price` int(50) default NULL,
  `adjustments` int(50) default NULL,
  `total_price` int(50) default NULL,
  `current_date` date NOT NULL,
  `due_date` varchar(255) default NULL,
  PRIMARY KEY  (`invoice_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

And the query still not working .

MadTechie · May 14, 2008

Ahhh i spotted the problem,

current_date is reserved

use back ticks ` ie

`current_date`

see below

$sql = "INSERT INTO invoices SET client_id='{$_POST['c_id']}', service_name='{$_POST['s_name']}', total_price='{$_POST['t_price']}', `current_date`=CURDATE() , due_date='{$_POST['d_date']}' ";

newbie_07 · May 15, 2008

Ahhh i spotted the problem,

current_date is reserved

use back ticks ` ie

`current_date`

see below
$sql = "INSERT INTO invoices SET client_id='{$_POST['c_id']}', service_name='{$_POST['s_name']}', total_price='{$_POST['t_price']}', `current_date`=CURDATE() , due_date='{$_POST['d_date']}' ";

Great! Excellent that works fine.....just problem of the reserved word....

Now plz show me how to insert date from the list boxes, like year, month and day.....

redarrow · May 15, 2008

The whole insert is floored you need to protect the database with mysql_real_escape_string($_POST['varable_name']);

redarrow · May 15, 2008

Correct safe method

<?php
// database connection..

$client_id=mysql_real_escape_string($_POST['c_id']);
$service_name=mysql_real_escape_string($_POST['s_name']);
$total_price=mysql_real_escape_string($_POST['t_price']);
$due_date=mysql_real_escape_string($_POST['d_date']);

$sql = "INSERT INTO invoices SET client_id='$client_id, service_name='$service_name', 
total_price='$total_price', `current_date`=CURDATE() , due_date='$due_date' ";

$sql_result=mysql_query($sql)or dir(mysql_error());

?>

Sign In

inserting date error

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information