[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 30565
  • Last Modified:

PHP/MySQL insert date inserts 0000-00-00

Hi folks I have the values from a .csv file being inserted into a mysql DB.

the csv looks like this:

04/29/2004,Ali,Islah,Alameda City,Quality Standards
04/29/2004,Dave,James,Alameda City,Quality Standards
04/29/2004,Lisa,Gibons,Alameda City,Quality Standards

The code looks like this:

[php]
       include("dbc.inc.php");
      
      $conn = @mysql_connect($host, $user, $pw, $dbname);
            @mysql_selectdb($dbname);

if (!$conn) {
        echo "Connection failed\n";
        exit;
            }
$filename = "/web/pages/scripts/" . $csv_file_name ;//. ".csv";
$fcontents = file($filename);
   while (list($line_num, $line) = each($fcontents))
    {
      {
      $msg = $line;

      $CSVRecord = explode (",", $msg);

      $StringLength = strlen($CSVRecord[4])-1;
        $CSVInsert="INSERT into certifications(seminar_date,last_name,first_name,agency,seminar)
                  Values('$CSVRecord[0]', trim('$CSVRecord[1]'), trim('$CSVRecord[2]'), trim('$CSVRecord[3]'), trim('$CSVRecord[4]'));";
                        
                        $result = mysql_query($CSVInsert) or die ("Error in query: $query. " . mysql_error());
                        
      if(!$CSVInsert) echo("Invalid Insert");
        
      }
  }

  mysql_close($conn);
[/php]

It's pretty straight forward and works except the date. When the seminar_date field is of type varchar it inserts fine. But when the seminar_date field is type datetime, as it should be, it inserts as 0000-00-00

I'm porting this app from postgresql to mysql. Any help would be greatly appreciated.
0
ewarmour
Asked:
ewarmour
  • 3
  • 3
1 Solution
 
steve918Commented:
Hi ewarmour,

You can insert the entire file into mysql in one statement without having to parse the data in php.

mysql_query("LOAD DATA INFILE 'C:\Myfile.csv' INTO TABLE table FIELDS TERMINATED BY ',' IGNORE 1 LINES");

http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html

-Steven
0
 
Muhammad WasifCommented:
try this query

$CSVInsert="INSERT into certifications(seminar_date,last_name,first_name,agency,seminar)
                  Values('".str_replace("/","-",$CSVRecord[0])."', trim('$CSVRecord[1]'), trim('$CSVRecord[2]'), trim('$CSVRecord[3]'), trim('$CSVRecord[4]'))";
0
 
ewarmourAuthor Commented:
Thanks wasifg, I tried that and the value is still 0000-00-00 00:00:00.

0
Independent Software Vendors: 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!

 
ewarmourAuthor Commented:
Hi steve918... no luck with the import

I've tried

mysql_query("LOAD DATA INFILE '$csv_file_name' INTO TABLE certifications FIELDS TERMINATED BY ','")
                    or die
                  ("Error in query: $query. " . mysql_error());

0
 
Muhammad WasifCommented:
The problem is in the format of the data i.e mm/dd/yyyy

04/29/2004,Ali,Islah,Alameda City,Quality Standards

but mysql accepts yyyy-mm-dd
 so change your query as follows

list($month, $day, $year) = explode("/",$CSVRecord[0]);

$CSVInsert="INSERT into certifications(seminar_date,last_name,first_name,agency,seminar)
                  Values('$year-$month-$day', trim('$CSVRecord[1]'), trim('$CSVRecord[2]'), trim('$CSVRecord[3]'), trim('$CSVRecord[4]'));";
0
 
ewarmourAuthor Commented:
Thanks wasifig, that worked perfectly.
0
 
Muhammad WasifCommented:
Your are welcome

Wasif Ghani
0

Featured Post

Technology Partners: 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!

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