Jump to content

Post System Date To Mysql Table


deecos
 Share

Recommended Posts

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());

}

?>

Link to comment
Share on other sites

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());

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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"));

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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. :unsure:

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...