mySQL INSERT and timeStamp question ...

[A JM]

I have a form and an INSERT statement for my DB on my page; I want to add a timestamp to one of the fields in my DB and I though I could just simply add it to the SQL statement is there something special to doing this?

 

This is the original:

 

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {

  $insertSQL = sprintf("INSERT INTO claimcue (clnt_name, clnt_company, clnt_street1, clnt_street2, clnt_city, clnt_state, clnt_zipa, new_timestamp, chk_new) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)",

                      GetSQLValueString($_POST['clnt_name'], "text"),

                      GetSQLValueString($_POST['clnt_company'], "text"),

                      GetSQLValueString($_POST['clnt_street1'], "text"),

                      GetSQLValueString($_POST['clnt_street2'], "text"),

                      GetSQLValueString($_POST['clnt_city'], "text"),

                      GetSQLValueString($_POST['clnt_state'], "text"),

                      GetSQLValueString($_POST['clnt_zipa'], "text"),

                      GetSQLValueString($_POST['new_timestamp'], "date"),

                      GetSQLValueString($_POST['chk_new'], "int"));

 

Modified:

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {

  $insertSQL = sprintf("INSERT INTO claimcue (clnt_name, clnt_company, clnt_street1, clnt_street2, clnt_city, clnt_state, clnt_zipa, chk_new, new_timestamp) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, now())",

                      GetSQLValueString($_POST['clnt_name'], "text"),

                      GetSQLValueString($_POST['clnt_company'], "text"),

                      GetSQLValueString($_POST['clnt_street1'], "text"),

                      GetSQLValueString($_POST['clnt_street2'], "text"),

                      GetSQLValueString($_POST['clnt_city'], "text"),

                      GetSQLValueString($_POST['clnt_state'], "text"),

                      GetSQLValueString($_POST['clnt_zipa'], "text"),

                      GetSQLValueString($_POST['chk_new'], "int"));

 

Can anyone help with this?

 

Thanks.

 

A JM,

[Ken2k7]

You have to add the column to the table before you can INSERT values to it.

[A JM]

The column 'new_timestamp' does exist in the table.

 

Since I'm still pretty green with PHP I assume that

 

GetSQLValueString($_POST['clnt_zipa'], "text")

 

is used to pull the information from my form and then place that variable into %s is this correct?

 

A JM,

[A JM]

Trying something a little different using a variable $date and not able to get the time and date together only the date???

 

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {

$date = date('YmdH:m:s');

  $insertSQL = sprintf("INSERT INTO claimcue (clnt_name, clnt_company, clnt_street1, clnt_street2, clnt_city, clnt_state, clnt_zipa, chk_new, new_timestamp) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, now())",

                      GetSQLValueString($_POST['clnt_name'], "text"),

                      GetSQLValueString($_POST['clnt_company'], "text"),

                      GetSQLValueString($_POST['clnt_street1'], "text"),

                      GetSQLValueString($_POST['clnt_street2'], "text"),

                      GetSQLValueString($_POST['clnt_city'], "text"),

                      GetSQLValueString($_POST['clnt_state'], "text"),

                      GetSQLValueString($_POST['clnt_zipa'], "text"),

                      GetSQLValueString($date, "date"),

                      GetSQLValueString($_POST['chk_new'], "int"));

 

 

A JM,

 

[Ken2k7]

Is the field labeled as TIMESTAMP? Maybe it's in DATE format. In your first post, use the modified version and change now() to CURDATE().

 

Try it.

[A JM]

Thanks for the curdate() function.

 

I did have the field set for 'timestamp' but have since changed it to 'datetime'.

 

Curdate() does fill the field with the date but not the time, any ideas?

 

A JM,

 

[A JM]

Thanks for the curdate() function.

 

I did have the field set for 'timestamp' but have since changed it to 'datetime'; I believe they both will accept date/time...?

 

Curdate() does fill the field with the date but not the time, any ideas?

 

I believe the following is used by Dreamweaver and is not a standard PHP function (GetSQLValueString($date, "date")) but I could be wrong... how would i simply edit the sql statement with an item in the middle of the variable list?

 

I think I'm getting closer... "Date type columns are not strings" - this got me thinking about the above function and that it won't work in the above situation.

 

A JM,

 

[edit] got it - see the following for a couple of examples (http://www.gizmola.com/blog/archives/51-Exploring-Mysql-CURDATE-and-NOW.-The-same-but-different..html)

 

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {

  $insertSQL = sprintf("INSERT INTO claimcue (clnt_name, clnt_company, clnt_street1, clnt_street2, clnt_city, clnt_state, clnt_zipa, chk_new, new_timestamp) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, now())",

                      GetSQLValueString($_POST['clnt_name'], "text"),

                      GetSQLValueString($_POST['clnt_company'], "text"),

                      GetSQLValueString($_POST['clnt_street1'], "text"),

                      GetSQLValueString($_POST['clnt_street2'], "text"),

                      GetSQLValueString($_POST['clnt_city'], "text"),

                      GetSQLValueString($_POST['clnt_state'], "text"),

                      GetSQLValueString($_POST['clnt_zipa'], "text"),

                      GetSQLValueString($_POST['chk_new'], "int"));