inserting date error

[newbie_07]

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]

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]

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]

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]

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]

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

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

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

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]

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]

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]

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]

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]

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]

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

[redarrow]

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

?>