INSERT INTO - date type problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ofir0803
Forum Newbie
Posts: 22
Joined: Sun Jan 18, 2009 3:03 pm

INSERT INTO - date type problem

Post 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
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Re: INSERT INTO - date type problem

Post 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).
ofir0803
Forum Newbie
Posts: 22
Joined: Sun Jan 18, 2009 3:03 pm

Re: INSERT INTO - date type problem

Post 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
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Re: INSERT INTO - date type problem

Post by Burrito »

try

Code: Select all

 
$query = "INSERT INTO tbl_order (orderDate) VALUES (NOW())";
 
ofir0803
Forum Newbie
Posts: 22
Joined: Sun Jan 18, 2009 3:03 pm

Re: INSERT INTO - date type problem

Post by ofir0803 »

don't work for me.
i have attached screen shut...
Attachments
2009-01-18_232753.jpg
2009-01-18_232753.jpg (164 KiB) Viewed 796 times
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Re: INSERT INTO - date type problem

Post 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();
 
ofir0803
Forum Newbie
Posts: 22
Joined: Sun Jan 18, 2009 3:03 pm

Re: INSERT INTO - date type problem

Post 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
Attachments
errror.jpg
errror.jpg (35.25 KiB) Viewed 774 times
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Re: INSERT INTO - date type problem

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: INSERT INTO - date type problem

Post 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?
ofir0803
Forum Newbie
Posts: 22
Joined: Sun Jan 18, 2009 3:03 pm

Re: INSERT INTO - date type problem

Post 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?
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Re: INSERT INTO - date type problem

Post 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.
ofir0803
Forum Newbie
Posts: 22
Joined: Sun Jan 18, 2009 3:03 pm

Re: INSERT INTO - date type problem

Post 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?
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Re: INSERT INTO - date type problem

Post by Burrito »

Code: Select all

 
INSERT INTO `tbl_order` SET `orderDate` = now(), customerID = xxx
 
Post Reply