Post System Date To Mysql Table

[deecos]

PHP wizards one an all,

I need help with some PHP syntax (maybe a system memory variable too).

 

I would like to know how to insert the current system date into a MySQL column.

Below is some code generated from Dreamweaver that inserts a date provided via an input form. I would like to replace the form's input value and simply insert the date behind the scenes (no need to ask the user to supply today's date) I believe the date format MySQL expects is YYYY-MM-DD.

 

Now that I think about it, it would be nice to know the same thing for a column defined as a timestamp as well.

 

Thanks ahead of time for any time you can devote to my inquiry.

 

*********************************************

______________the infamous CODE_______________

*********************************************

<?php require_once('Connections/test.php'); ?>

<?php

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

$theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

 

switch ($theType) {

case "text":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "long":

case "int":

$theValue = ($theValue != "") ? intval($theValue) : "NULL";

break;

case "double":

$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";

break;

case "date":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "defined":

$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

break;

}

return $theValue;

}

 

$editFormAction = $_SERVER['PHP_SELF'];

if (isset($_SERVER['QUERY_STRING'])) {

$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);

}

 

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

$insertSQL = sprintf("INSERT INTO SUBSCRIBER_T (FULL_NAME, EMAIL_ADDR, CREATE_DT) VALUES (%s, %s, %s)",

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

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

GetSQLValueString($_POST['CREATE_DT'], "date"));

mysql_select_db($database_test, $test);

$Result1 = mysql_query($insertSQL, $test) or die(mysql_error());

}

?>

[Dennis]

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

$insertSQL = sprintf("INSERT INTO SUBSCRIBER_T (FULL_NAME, EMAIL_ADDR, CREATE_DT) VALUES (%s, %s, %s)",

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

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

GetSQLValueString($_POST['CREATE_DT'], "date"));

mysql_select_db($database_test, $test);

$Result1 = mysql_query($insertSQL, $test) or die(mysql_error());

Try this

 

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

$insertSQL = sprintf("INSERT INTO SUBSCRIBER_T (FULL_NAME, EMAIL_ADDR, CREATE_DT) VALUES (%s, %s, %s)",

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

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

now(), "date"));

mysql_select_db($database_test, $test);

$Result1 = mysql_query($insertSQL, $test) or die(mysql_error());

[deecos]

Thanks for the help, but I was unable to get it to work.

 

First I got some crazy error that I resolved by removing an extra parenthesis (See red parenthesis below)

 

$insertSQL = sprintf("INSERT INTO SUBSCRIBER_T (FULL_NAME, EMAIL_ADDR, CREATE_DT) VALUES (%s, %s, %s)",

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

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

now(),"date"));

 

After I eliminated that problem, I got the following error.

 

Fatal error: Call to undefined function: now() in /home/sniborn/public_html/_db_insert_test1.php on line 37

[Dennis]

Or this:

 

date('Y m d'), "date"));

 

Since you do not need the time which now() provides.

[Dennis]

Add this before your MySql query, but in between a PHP <?php ?>

 

$Get_current_date= date('Y m d');

 

 

Then add $Get_current_date into

 

$insertSQL = sprintf("INSERT INTO SUBSCRIBER_T (FULL_NAME, EMAIL_ADDR, CREATE_DT) VALUES (%s, %s, %s)",

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

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

GetSQLValueString($Get_current_date,"date"));

[deecos]

I sense I am getting closer, but no cigar.

 

The most recent suggestion

GetSQLValueString($Get_current_date,"date"));

produces the following error:

Column 'CREATE_DT' cannot be null

(the column is defined as NOT NULL)

 

Although an earlier suggestion failed

date('Y m d'), "date"));

I modified it as follows

date('Y-m-d'), "date");

resulting in no errors, but the value that posted in the database was 0000-00-00

 

Thanks for all your suggestions I'll keep working at it.

[HCSuperStores]

Maybe I'm missing something, but I use the NOW() function for system dates and times. If you find that the system is off by hours, dates or minutes, you can adjust it with the INTERVAL command.

 

$insertSQL = sprintf("INSERT INTO SUBSCRIBER_T (FULL_NAME, EMAIL_ADDR, CREATE_DT) VALUES ($thename,$theaddr,NOW())"

 

Is this what you are looking for?

[borfast]

NOW() should work but I suggest you use UNIX timestamps instead.

Use the time() function to get a UNIX timestamp from PHP and then insert the returned value into the database.

 

This way, if the need comes, you'll be able to get the timestamp from the database and manipulate it more easily than if you use MySQL's formated timestamp.

[HCSuperStores]

Hi Raul,

 

Not trying to get in a huge debate over this, but if you have DATE, TIME or DATETIME fields in MySQL, it's all the same. It doesn't matter HOW the data gets populated into these fields, once the data is there it is formatted in a way that MySQL is used to seing things.

 

So if you have a DB field of type DATE, it doesn't matter what you initially used to put the data there, the system date from PHP or using the NOW command from MySQL, they will both come back the same.

 

I am curious, because I am working with this right now in a system of mine, is there ever a time where the NOW() and the date accessed by PHP and Perl would be different? I would think that they both are looking at the same clock, but I'm not sure.

 

And if I'm missing something on the above comments, forgive me! I'm partly biased towards doing the time-stamp "my way" because I found the alternative such a pain! Not only do you have to get the time, but format it the right way. And if you mess up, you end up missing the hours, or minutes, or messing up your date. Using the NOW function was, for me, a huge time saver.

 

Again IMO ...

 

Love to learn from everyone on things like this. Let me know what you find out deecos!

[DarqFlare]

You can use NOW() to insert into a timestamp field in MySQL.

 

To extract it in a PHP-friendly form, you'll use something like the following:

 

>$Result = mysql_query("select unix_timestamp(`tiemstamp_field`) from `table` where `rowid`='1'");

 

The unix_timestamp() returns any MySQL-formatted timestamp/date/time into the Unix timestamp that PHP uses.

[HCSuperStores]

Forgive me if I'm missing some of the pieces to help with PHP ... because I AM missing some of the pieces. I'm a Perl guy! Thumbs Up

[deecos]

Thanks to all of you for supplying the hints and key information I needed to solve this problem. To begin with, I didn't understand which functions were PHP and which ones were MySQL, so I had to wade through the documentation to better understand it.

My requirement was to accept user input from a form, then post the user input along with the current date (or system date) to the database. The following code , derived from the suggestions, hints, and a little research, acheives the desired result.

 

 

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

$insertSQL = sprintf("INSERT INTO SUBSCRIBER_T (FULL_NAME, EMAIL_ADDR, CREATE_DT) VALUES (%s, %s, CURRENT_DATE())",

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

GetSQLValueString($_POST['EMAIL_ADDR'], "text"));

 

Thanks again for everyone's help! Rock Sign

[borfast]

The big difference between using MySQL's NOW() and PHP's time() is that PHP's time() will be compatible with any database server, while NOW() may not work, if you change the database server. It requires a tiny bit more work, yes but it's not that hard.