Page 1 of 1
INSERT INTO - date type problem
Posted: Sun Jan 18, 2009 3:05 pm
by ofir0803
Hi
I have this 2 tables:
Code: Select all
tbl_order: orderId(PK),orderDate,shippedDate,status,comment,customerId(FK);
tbl_customer: custometId(PK),firstName,lastName...
there is a Relationship between customerId in the tbl_customer to the customerId in the tbl_order.
i want to insert into tbl_order the customerId that is in the tbl_customer and the orderDate that the user is filling in the form.
i started by copying from the table like this:
Code: Select all
INSERT INTO tbl_order (customerId)
SELECT customerId FROM tbl_customer order by customerId desc limit 1
but now i want to insert the date to colume orderDate in the right customerId
like this?
Code: Select all
INSERT INTO tbl_order (orderDate) VALUES NOW()
I keep getting 00:00:00 in my database
please help
Re: INSERT INTO - date type problem
Posted: Sun Jan 18, 2009 3:18 pm
by Burrito
what is the data type for the date field?
the now() function only works with certain data types (timestamp, date, datetime, varchar etc).
Re: INSERT INTO - date type problem
Posted: Sun Jan 18, 2009 3:21 pm
by ofir0803
Burrito wrote:what is the data type for the date field?
the now() function only works with certain data types (timestamp, date, datetime etc).
oh!!!
sorry, of course the data type is DATE
Re: INSERT INTO - date type problem
Posted: Sun Jan 18, 2009 3:24 pm
by Burrito
try
Code: Select all
$query = "INSERT INTO tbl_order (orderDate) VALUES (NOW())";
Re: INSERT INTO - date type problem
Posted: Sun Jan 18, 2009 3:30 pm
by ofir0803
don't work for me.
i have attached screen shut...
Re: INSERT INTO - date type problem
Posted: Sun Jan 18, 2009 3:41 pm
by Burrito
are you running that query from phpMyAdmin? There's no reason it shouldn't work.
you can try this but it does the exact same thing
Code: Select all
INSERT INTO `tbl_order` SET `orderDate` = now();
Re: INSERT INTO - date type problem
Posted: Sun Jan 18, 2009 3:48 pm
by ofir0803
oh, i keep getting error :
"#1452 - Cannot add or update a child row"
i don't understand why the customerId make him a problem i allready insert a value there
Re: INSERT INTO - date type problem
Posted: Sun Jan 18, 2009 3:50 pm
by Burrito
you have that tabled keyed to another table which means the foreign key field needs a value that matches a primary key from the other table.
Re: INSERT INTO - date type problem
Posted: Sun Jan 18, 2009 3:51 pm
by Eran
Are you reading the error message? is says clearly you have a foreign key constraint violation. You can not insert a row into tbl_order that does not reference a valid row in tbl_customer. Who set up those constraints? wasn't it you?
Re: INSERT INTO - date type problem
Posted: Sun Jan 18, 2009 4:07 pm
by ofir0803
Burrito wrote:you have that tabled keyed to another table which means the foreign key field needs a value that matches a primary key from the other table.
i see.
how can i make a query that insert my date and also my customerId that matches to the tbl_customer table?
Re: INSERT INTO - date type problem
Posted: Sun Jan 18, 2009 4:17 pm
by Burrito
ofir0803 wrote:i see.
how can i make a query that insert my date and also my customerId that matches to the tbl_customer table?
You'll also need to include a valid foreign key value in your insert statement.
Re: INSERT INTO - date type problem
Posted: Sun Jan 18, 2009 4:23 pm
by ofir0803
Burrito wrote:ofir0803 wrote:i see.
how can i make a query that insert my date and also my customerId that matches to the tbl_customer table?
You'll also need to include a valid foreign key value in your insert statement.
ok.
like this?
Code: Select all
INSERT INTO tbl_order (customerId)
SELECT customerId FROM tbl_customer order by customerId desc limit 1
but how in the same time can i insert the date in tbl_order?
Re: INSERT INTO - date type problem
Posted: Sun Jan 18, 2009 4:35 pm
by Burrito
Code: Select all
INSERT INTO `tbl_order` SET `orderDate` = now(), customerID = xxx