• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 420
  • Last Modified:

Date problem in PHP and in MySQL table 2011

Below is the code that gives the date in PHP but o's in my table in MySQL.

// Get the current date.
            // $date1 = date('Y m d'); // year as 4 digits, month as 2 digits and day as 2 digits
      $date1 = date('m/d/Y'); // year as 4 digits, month as 2 digits and day as 2 digits

I have worked about one hour so far on this problem.

0
KennethSumerford1
Asked:
KennethSumerford1
  • 7
  • 4
1 Solution
 
KennethSumerford1Author Commented:
Here is what it looks like in my date field in the MySQL DB table:  0000-00-00

This is the result in the Insert statement in the SQL in PHP:  '09/10/2011'  

In the structure of the prospect table it looks like this (field and format):  
OrigContactDate       date
0
 
hieloCommented:
When inserting a date into date or datetime field in MySQL, you need to format the date as YYYY-mm-dd

$date1=date('Y-m-d');
mysql_query( "INSERT INTO TableName(lastUpdate) VALUES('{$date1}')") or die( mysql_error() );
0
 
hieloCommented:
I did not see your previous update earlier:
mysql_query( "INSERT INTO tableName(OrigContactDate) VALUES('{$date1}')") or die( mysql_error() );

PS: When "extracting" the date, if you want it as mm/dd/yyyy, you can use:
SELECT DATE_FORMAT(`OrigContactDate`, '%m/%d/%Y') as `OrigContactDate` FROM tableName
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
KennethSumerford1Author Commented:
The server is at Go Daddy running Windows, not Lennux. Could there be something else in the structure of the table to make the date not register correctly in the date field?
0
 
hieloCommented:
try something like this (but provide the correct tableName):
$sql="INSERT INTO tableName(OrigContactDate) VALUES('{$date1}')";
echo 'attempting to execute '.htmlentities($sql,ENT_QUOTES);
mysql_query($sql) or die( mysql_error() );

What do you get?
0
 
KennethSumerford1Author Commented:
I still get the value of  0000-00-00  in the date field of the table when  I tried
$date1=date('Y-m-d');

$SQL1 .= ", '" . $date1 . "' "; // OrigContactDate

Do I need  #  in this SQL statement?
0
 
KennethSumerford1Author Commented:
Maybe it is taking the date as a string.  
0
 
hieloCommented:
>>Do I need  #  in this SQL statement?
Not for MySQL. Are trying to insert the current time? If so, try inserting Now().

Instead of:
$SQL1 .= ", '" . $date1 . "' "; // OrigContactDate

use:
$SQL1 .= ", Now() "; // OrigContactDate
0
 
KennethSumerford1Author Commented:
$SQL1 .= ", '{$date1}' "; // OrigContactDate

This still gives me     0000-00-00
0
 
KennethSumerford1Author Commented:
2011-09-10

$SQL1 .= ", '{$date1}' "; // OrigContactDate    and
$SQL1 .= ", Now() "; // OrigContactDate  
both work!  
0
 
KennethSumerford1Author Commented:
Good solution!  The date in PHP to MySQL can be tricky.

Thanks for your help.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now