Insert current date and time into MySQL

Heather

I'm trying to insert the current time and date into a MySQL table as well as some other data and it doesn't seem to be working. Here is the code I'm using:

$sql = "INSERT INTO $table (session,product,quantity,cost_price,cust_id,order_date,
order_time) VALUES ";
$sql .= "('$session','$product','$quantity','$cost_price','$cust_id',
'sysdate','systime')";

It's probably simple but I can't see it, someone please help!!

laserlight

how are you storing the date and time?
A PHP-type Unix timestamp?
Then your script could call time(), the result of which is entered.
Or you could use NOW() or something from mysql, but that depends on how it is stored.

Heather

This was the create table statement that I used:

drop table sales_order;
create table sales_order(
session tinytext not null,
product varchar(30) not null,
quantity tinytext not null,
cost_price float(4,2),
cust_id int not null,
sales_order_id int not null auto_increment,
order_date date,
order_time varchar(20),
Primary Key(sales_order_id, product));

I'm not sure if they're set up correctly, could u help please, thanx

laserlight

I could give you the solution, but why not think:

What is an example data of the order_date field?
What is an example of the order_time field?

Once you have that down, you could explore using PHP in time() and date() and other date/time functions.

tsinka

Hi,

you could change the date field to the type timestamp. The first timestamp field in a table is filled with the current unix timestamp automatically. You can extract the date info with e.g.
$dtmDate = date("m/d/Y H:i:s",$ts); if you want do display it or you can extract the year with date("Y",$ts) and so on ...

$ts has to be filled with the database field value.

tsinka

I forgot to mention that you should exclude that timestamp field in your insert query, since it will be filled automatically 🙂

tsinka

Hi,

I wasn't specific enough ...

By using a timestamp field you can save the time and the date in one field. It isn't neccessary anymore to use two fields for date and time.

Instead of changing the field type it would be better to add an additional field and to move the data to that field.

I don't know exactly what happens with the existing data if you just change the field type.

Follow the hints of laserlight if you don't want to mess around with the database 🙂

Regards,
Thomas

Heather

Thanks to everyone that helped me. I ended up using the date/time function in PHP to get the current date and time, put them into variables and sent them to the database.

Thanks again 🙂