Formatting date

[jeeves245]

Hey!

 

I know i'm full of questions today. And I really appreciate all the help. But here's another... (Wasn't sure if I should put this in PHP or SQL forum, but it has PHP in it so I chose this one).

 

I'm having a bit of trouble formatting the date in an SQL insert statement. The date being inserted is from a variable, and is in the DD/MM/YYYY format. Do I need to format it to the default MySQL format?

 

I've tried the following:

 

INSERT INTO database(date) VALUES(DATE_FORMAT('$dateStore','%Y %m %d'))

 

Is this on the right track?

 

Cheers.

[phpSensei]

it should be DATETIME or DATE in your mysql   

 

explained here

 

http://dev.mysql.com/doc/refman/5.1/en/datetime.html

[jeeves245]

it should be DATETIME or DATE in your mysql   

 

explained here

 

http://dev.mysql.com/doc/refman/5.1/en/datetime.html

 

Hmm.. I read the article but i'm still confused as how to actually change to way the date is formatted..

 

Do I still use this kind of format:

 

INSERT INTO database(date) VALUES(DATE('$dateStore','%Y %m %d'))?

                                                     

[phpSensei]

Make the Database Column Type a DATETIME in your PHPMYADMIN, also dont use Date_Format, theres another way

 

You can make $datestore a timestamp and use

mysql> SELECT FROM_UNIXTIME('$datestore',
    ->                      '%Y %D %M');
        -> '2007 30th November 10:30:59 2007'

 

[jeeves245]

Make the Database Column Type a DATETIME in your PHPMYADMIN, also dont use Date_Format, theres another way

 

You can make $datestore a timestamp and use

mysql> SELECT FROM_UNIXTIME('$datestore', '%Y %D %M');
        -> '2007 30th November 10:30:59 2007'

 

 

Hmm.. Not too sure I understand that. Sorry, i'm still learning when it comes to PHP and SQL. I'm getting better but some things still confuse the crap outta me, haha.

 

You able to give a brief explanation to how that works?

 

Thanks

[phpSensei]

<?php
$datestore = time(); // time will generate a UNIX TIMESTAMP 1199167200
$sql = mysql_query("INSERT INTO table_name (`date`) VALUES (FROM_UNIXTIME('$datestore', '%Y %D %M'))");
?>

 

As you can see it converts the Timestamp we generated using the time() function into your own format. Now this will go into your DATABASE in a column called "date", the TYPE of column for the date must be DATETIME (Y M D HH:MM:SS) or DATE (Y M D) in order for it to function properly...

[jeeves245]

Ok, understood. Thanks

 

However, the $dateStore variable actually has a date stored in it which needs to be kept. So i'm not needing to actually generate a time or date here, just change the format of the date that I already have.

[phpSensei]

Ok, understood. Thanks

 

However, the $dateStore variable actually has a date stored in it which needs to be kept. So i'm not needing to actually generate a time or date here, just change the format of the date that I already have.

 

Oh thats a different story then... Alright post your script for your $datestore, where you set the date. I want to see the format.

[jeeves245]

Well the date is taken from a CSV file using the following script:

 

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

while (!feof($file_handle) ) {

$line_of_text = fgets($file_handle);
$parts = explode(',', $line_of_text);

print $parts[0] . $parts[1]. $parts[2]. $parts[3]. $parts[4]. $parts[5]. $parts[6]. $parts[7]. "<BR>";

 

Date is stored in $parts[0] (I used $dateStore in above post for clarity).

 

In the CSV file itself, the date is in a simple DD/MM/YYYY format.

[phpSensei]

alright try this then

 

<?php

$datestore = strtotime(str_replace("/","-",$datestore));
$sql = mysql_query("INSERT INTO table_name (`date`) VALUES (FROM_UNIXTIME('$datestore', '%Y %D %M'))");

?>

[jeeves245]

alright try this then

 

<?php

$datestore = strtotime(str_replace("/","-",$datestore));
$sql = mysql_query("INSERT INTO table_name (`date`) VALUES (FROM_UNIXTIME('$datestore', '%Y %D %M'))");

?>

 

What part of that do I need to incorporate my already stored date into ($parts[0])?

[phpSensei]

Post your script where you insert the date, so i can incorporate it for you

[jeeves245]

$sql="INSERT INTO deliveryInformation(deliveryDate,psNumber,numItems,volume)
VALUES($parts[0]','$parts[2]','$parts[3]','$parts[7]')";

[phpSensei]

theres a missing single quote before the $parts[0]

 

here try this now

 

$datestore = strtotime(str_replace("/","-",$parts[0]));
$sql="INSERT INTO deliveryInformation(deliveryDate,psNumber,numItems,volume)
VALUES('$datestore','$parts[2]','$parts[3]','$parts[7]')";

[jeeves245]

theres a missing single quote before the $parts[0]

 

here try this now

 

$datestore = strtotime(str_replace("/","-",$parts[0]));
$sql="INSERT INTO deliveryInformation(deliveryDate,psNumber,numItems,volume)
VALUES('$datestore','$parts[2]','$parts[3]','$parts[7]')";

 

Hmm it's inserting the records, but all the dates are 0000-00-00.

 

I suspect it's something to do with my code rather than yours.

[phpSensei]

hoping you changed the data type to DATE, yuo can actually just do this

 

$datestore = str_replace("/","-",$parts[0]);
$sql="INSERT INTO deliveryInformation(deliveryDate,psNumber,numItems,volume)
VALUES('$datestore','$parts[2]','$parts[3]','$parts[7]')";

 

 

the DATE type requires this format 0000-00-00

 

so 08/20/09 lets say would convert to 2008-20-09

[jeeves245]

hoping you changed the data type to DATE, yuo can actually just do this

 

$datestore = str_replace("/","-",$parts[0]);
$sql="INSERT INTO deliveryInformation(deliveryDate,psNumber,numItems,volume)
VALUES('$datestore','$parts[2]','$parts[3]','$parts[7]')";

 

 

the DATE type requires this format 0000-00-00

 

so 08/20/09 lets say would convert to 2008-20-09

 

Yep, the database field is set to DATE type.

[phpSensei]

that should def work then...

 

if it doesnt, print out $parts[0] and see what it gives you

[jeeves245]

Hmmm now i'm really confused. In the CVS file, there's 8 different pieces of data that are seperated by commas. The script puts them into $parts[0] through $parts[7] ($parts[0] being the date). But when I try to echo them out to the page, it comes back blank. Yet the SQL statements are putting everything in the database correctly except for the date, so the data is definitely in the $parts variable as it should be.

 

Quite confused now... any ideas as to why this would be?

 

Here's the entire script:

 

<?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);
//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

$datestore = strtotime(str_replace("/","-",$parts[0]));
$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);
   
?>

 

Sample line of CVS file:

16/07/2009,CUSTOMER,170932,2,ADDRESS1,ADDRESS2,ADDRESS3,1.42

 

By the way, thanks for all the help phpSensei, I really appreciate it Off to bed now, but i'll check back in the morning.

[phpSensei]

so $parts[0]$part[1]$parts[2] are all blank?

[jeeves245]

so $parts[0]$part[1]$parts[2] are all blank?

 

They all insert into the database but won't echo out to the browser. Except for $part0 which is the date, it won't insert into the database or echo out to the browser. 

[phpSensei]

change

$line_of_text = fgets($file_handle);
$parts = explode(',', $line_of_text);

 

to

 

$line_of_text = fgets($file_handle);
$parts = explode(',', $line_of_text);
print_r($partS);

 

whats the output?

[jeeves245]

change

$line_of_text = fgets($file_handle);
$parts = explode(',', $line_of_text);

 

to

 

$line_of_text = fgets($file_handle);
$parts = explode(',', $line_of_text);
print_r($partS);

 

whats the output?

 

Array ( [0] => 16/07/2009 [1] => CUSTOMER [2] => 170372 [3] => 2 [4] => ADDRESS1 [5] => ADDRESS2 [6] => ADDRESS3 [7] => 0.33 => [8]

 

(have censored out address and customer for obvious reasons)

[phpSensei]

change

$line_of_text = fgets($file_handle);
$parts = explode(',', $line_of_text);

 

to

 

$line_of_text = fgets($file_handle);
$parts = explode(',', $line_of_text);
print_r($partS);

 

whats the output?

 

Array ( [0] => 16/07/2009 [1] => CUSTOMER [2] => 170372 [3] => 2 [4] => ADDRESS1 [5] => ADDRESS2 [6] => ADDRESS3 [7] => 0.33 => [8]

 

(have censored out address and customer for obvious reasons)

 

$parts[0] is 16/07/2009, I dont understand why its not printing out to the page...

 

did you do print $parts[0]?

[jeeves245]

No I used echo. Does it make a difference?