Formatting date

[phpSensei]

Nope it shouldnt, the array brings back the date as $parts[0]..

 

You can PM me "deliveries.csv" and I can run it on my local server, see if I get the same results.

 

 

[jeeves245]

Hmm I used print and everything printed out correctly. So it must be something to do with the code I'm using the insert the date into the database

[roopurt18]

I didn't read all the back and forth, but:

<?php
$dates = array( '2009-01-01 23:43:01', '12/31/09 12 AM', '3:45 PM' );
// Assume `column` is either TIMESTAMP or DATETIME
foreach( $dates as $d ) {
  $temp = strtotime( $d );
  if( $temp === false ) {
    echo "can't insert {$d}";
    continue;
  }
  $temp = "'" . date( 'Y-m-d H:i:s', $temp ) . "'"
  if( mysql_query( "insert into `yourtable` ( `column` ) values ( {$temp} )" ) ) {
    echo "inserted {$d}";
  }else{
    echo "failed on {$d}";
  }
?>

 

The trick is to use strtotime on the input if it is in a common enough format to get a timestamp out of it.  Then pass it through date( 'Y-m-d H:i:s' ) to put it in YYYY-MM-DD HH:MM:SS format, which is what the database accepts.

[phpSensei]

If everything is printing out correctly, the final product should look  like

 

<?php
$con = mysql_connect("localhost","root","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("database", $con);

$file_handle = fopen("deliveries.csv", "rb");

while (!feof($file_handle) ) {
$line_of_text = fgets($file_handle);
$parts = explode(',', $line_of_text);
$datestore = str_replace("/","-",$parts[0]);
$datestore = explode("-",$datestore);
$datestore = $datestore[2]."-".$datestore[1]."-".$datestore[0];

//Seperate parts of CSV file where there is a comma

$sql="INSERT INTO customer (customerName, address1, address2, address3)
VALUES
('$parts[1]','$parts[4]','$parts[5]','$parts[6]')";
//The above works correctly

$sql="INSERT INTO deliveryInformation(deliveryDate,psNumber,numItems,volume)
VALUES('$datestore','$parts[2]','$parts[3]','$parts[7]')";
//$parts 2,3,7 are inserted correctly, but date shows as 0000-00-00

mysql_query($sql);
}

fclose($file_handle);

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
else
   {
   echo "Records submitted.";
   }
   
   mysql_close($con);
   
?>

[phpSensei]

^^^ Try mine first then..

 

I didn't read all the back and forth, but:

<?php
$dates = array( '2009-01-01 23:43:01', '12/31/09 12 AM', '3:45 PM' );
// Assume `column` is either TIMESTAMP or DATETIME
foreach( $dates as $d ) {
  $temp = strtotime( $d );
  if( $temp === false ) {
    echo "can't insert {$d}";
    continue;
  }
  $temp = "'" . date( 'Y-m-d H:i:s', $temp ) . "'"
  if( mysql_query( "insert into `yourtable` ( `column` ) values ( {$temp} )" ) ) {
    echo "inserted {$d}";
  }else{
    echo "failed on {$d}";
  }
?>

 

The trick is to use strtotime on the input if it is in a common enough format to get a timestamp out of it.  Then pass it through date( 'Y-m-d H:i:s' ) to put it in YYYY-MM-DD HH:MM:SS format, which is what the database accepts.

 

I tried that but it didnt work ...

 

Hopefully your script will work, I test mine above, just re arranged the format to YYYY-MM-DD (DATE)... and inserts it into the db.

 

[jeeves245]

phpSensei, that script works!! Thanks so much.

 

I assume I can use a similar method to get the date back out again? Might try it myself and see how I go

 

Thanks again.

[phpSensei]

phpSensei, that script works!! Thanks so much.

 

I assume I can use a similar method to get the date back out again? Might try it myself and see how I go

 

Thanks again.

 

Ya np,

 

Mark topic as solved if everything is okay now.

[jeeves245]

One more quick question.. is there any way that script can be modified to accommodate for another date? Say that $parts[1] was also a date. Would this work?

 

<?php
//for $parts[0]
$datestore = str_replace("/","-",$parts[0]);
$datestore = explode("-",$datestore);
$datestore = $datestore[2]."-".$datestore[1]."-".$datestore[0];
//for $parts[1]
$datestore2 = str_replace("/","-",$parts[1]);
$datestore2 = explode("-",$datestore2);
$datestore2 = $datestore2[2]."-".$datestore2[1]."-".$datestore2[0];
?>

 

In theory I thought that would work, but it doesn't.

[phpSensei]

Yes that would work... You can make that a function actually, to make your code more clear

 

<?php
function formatCSVDate($part){

$part= str_replace("/","-",$part);
$part= explode("-",$part);
$part= $part[2]."-".$part[1]."-".$part[0];

return $part;
}
?>

 

 

do

 

<?php
//for $parts[0]
$datestore =formatCSVDate($parts[0]);
//for $parts[1]
$datestore2 =formatCSVDate($parts[1]);
?>

[jeeves245]

Cool, perfect. Thanks again. I'm learning a lot here

 

Just been playing around with some code (trying to do it myself)... Would this be the correct way to format the date when extracting it from the database? Assume this code is executed AFTER the SQL select statement.

 

$datestore = str_replace("/","-",$row['deliveryDate']);
$datestore = explode("-",$datestore);
$datestore = $datestore[2]."-".$datestore[1]."-".$datestore[0];

 

$row['deliveryDate'] is where the date from $parts[0] is stored.

[phpSensei]

When its in the DATABASE and you execute it to the page, its already formatted so just print it out.

 

If you want to change the format then covert the string to a timestamp then format it to anything you liek using the date function http://ca.php.net/manual/en/function.date.php

 

<?php

/* Mysql Query Here...*/
$date = $row['deliveryDate'];
$timestamp = strtotime($date);

// use the date() function to change the format
$new_date = date("Y m d h:i:s",$timestamp);


?>

[jeeves245]

When its in the DATABASE and you execute it to the page, its already formatted so just print it out.

 

If you want to change the format then covert the string to a timestamp then format it to anything you liek using the date function http://ca.php.net/manual/en/function.date.php

 

<?php

/* Mysql Query Here...*/
$date = $row['deliveryDate'];
$timestamp = strtotime($date);

// use the date() function to change the format
$new_date = date("Y m d h:i:s",$timestamp);


?>

 

Yeah sorry I meant I want to change the format back to what it is in the CSV file. Will try that code. Thanks.

[phpSensei]

When its in the DATABASE and you execute it to the page, its already formatted so just print it out.

 

If you want to change the format then covert the string to a timestamp then format it to anything you liek using the date function http://ca.php.net/manual/en/function.date.php

 

<?php

/* Mysql Query Here...*/
$date = $row['deliveryDate'];
$timestamp = strtotime($date);

// use the date() function to change the format
$new_date = date("Y m d h:i:s",$timestamp);


?>

 

Yeah sorry I meant I want to change the format back to what it is in the CSV file. Will try that code. Thanks.

 

 

Ya use this function

 

<?php
function CSVDate($date){

$timestamp = strtotime($date);

// use the date() function to change the format
$new_date = date("d/m/Y",$timestamp);

return $new_date;

}

$old_csv_date = CSVDate($row['deliveryDate']);
print $old_csv_date;
?>