timestamp in mySQL db

[glamdring]

Hi all, first post here and fairly new so go easy!

Can anyone tell me how I get a basic timestamp in the format "MONTH YEAR DATE HOURS MINS SECS" into a table in mySQL from a simple form? I have had a bit of a play about but to no avail.

I have a basic database which I am constructing which I want to record client details in : these are the basics - name, address etc but I also want to know the time and date the details were entered into the database.

Can I do this with a simple SQL query and if so how, or do I have to construct something within the form to generate a field for the table?

Is there also a way of logging an IP address with this method?

Thanks peeps

[coopster]

Welcome to WebmasterWorld, glamdring!

A TIMESTAMP [dev.mysql.com] column is useful for recording the date and time of an

INSERT

or

UPDATE

operation. The first

TIMESTAMP

column in a table is automatically set to the date and time of the most recent operation if you don't assign it a value yourself. You can also set any

TIMESTAMP

column to the current date and time by assigning it a

NULL

value (If you insert NULL into a TIMESTAMP column, the current date and time is inserted.)

See Also:
[dev.mysql.com...]
[dev.mysql.com...]

Logging the IP address? Sure. Grab the server variable and insert it using your program/script of choice.

[glamdring]

Sorry perhaps didnt explain myself very well or I have misunderstood your reply. I am VERY new at mySQL (like, this week new) so that may be the case.

Although I have a field called "date" set up in my table (type "datetime"), when I submit the form all I get is 0000-00-00 00:00:00 in the database.

Do I actually need to submit a hidden value or something from a form to generate the datetime field in the table, or does mySQL do it automatically with a mysql_query? If so, how the heck do I get it to do it?

Thanks again

[glamdring]

Perhaps it would help if I posted what I have already done?

<?
$db="clientdetails";
mysql_query ("INSERT INTO clientdetails (quantity, compcode, date, fname, address_1, address_1a, address_2, county, country, zip, submit_by, PrPhone, SecPhone) VALUES ('$quantity', '$compcode','$date','$fname','$address_1','$address_1a','$address_2','$county','$country','$zip','$submit_by','$PrPhone','$SecPhone')");
?>

This submits almost all of what I want but I dont know how to enter the TIMESTAMP value into the database from here - does it need to be generated in a form first?

Also, two of the values I have in this query - 'quantity' and 'compcode' and being passed from another form, but they dont get submitted at all. Any ideas on that?

[glamdring]

Well I've done it now anyway.

In case anyone wonders, this is what I needed to do:

<?
$date = date("Y-m-d G:i:s") ;
$db="dbname";
mysql_query ("INSERT INTO dbname (quantity, date, compcode, fname, address_1, address_1a, address_2, county, country, zip, submit_by, PrPhone, SecPhone) VALUES ('$quantity','$date','$compcode','$fname','$address_1','$address_1a','$address_2','$county','$country','$zip','$submit_by','$PrPhone','$SecPhone')");
?>

No coding in php is required to get the datetime stamp in mySQL db.

[coopster]

Congratulations!

You could also streamline your code a bit more by letting MySQL assign the date value for you as opposed to using PHP's date() function to build it. You can specify DATETIME, DATE, and TIMESTAMP [dev.mysql.com] values using any of a common set of formats, one of which may be the result of a function that returns a value that is acceptable in a DATETIME, DATE, or TIMESTAMP context, such as NOW() [dev.mysql.com] or CURRENT_DATE [dev.mysql.com].

<?php 
mysql_query ("INSERT INTO dbname (quantity, date, compcode, fname, address_1,  
address_1a, address_2, county, country, zip, submit_by, PrPhone, SecPhone)  
VALUES ('$quantity', NOW(), '$compcode', '$fname', '$address_1', '$address_1a', '$address_2', '$county', '$country', '$zip', '$submit_by', '$PrPhone', '$SecPhone')"); 
?>