Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

From dd-mm-yyyy TO yyyy-mm-dd On INSERT

Codes here !

Moderators: macek, egami, gesf

From dd-mm-yyyy TO yyyy-mm-dd On INSERT

Postby ralph_5uk » Thu Feb 20, 2003 9:37 am

I am reading in a large number of fields including dates from a txt file and inserting them a mysql database..

--------------------------------------------------------
A bit of pseudo code to get the picture

CREATE TABLE $table_name ( id int(11) NOT NULL auto_increment, field1 varchar(10), date_posted date, PRIMARY KEY (id) )

LOAD DATA INFILE '$textfile' replace INTO TABLE $table_name

-----------------------------------------------------------
However the dates in the $textfile are in the format dd-mm-yyyy but MYSQL seems to only like to INSERT them if they are in the format yyyy-mm-dd... how can i easily get around this
:D
Last edited by ralph_5uk on Fri Feb 21, 2003 7:00 am, edited 2 times in total.
ralph_5uk
New php-forum User
New php-forum User
 
Posts: 3
Joined: Thu Feb 20, 2003 9:30 am

Postby Joan Garnet » Thu Feb 20, 2003 5:37 pm

You could use it to specify the format:
DATE_FORMAT
http://www.phpfreaks.com/mysqlref/3.php
User avatar
Joan Garnet
Moderator
Moderator
 
Posts: 387
Joined: Sat Aug 03, 2002 2:56 am
Location: Mars

Postby ralph_5uk » Fri Feb 21, 2003 5:55 am

Cheers for your response,

Can I use the date_format() on the INSERT statement

Or should I then just store the field at the time of INSERT as a text field or varchar(9) and then use date_format() at the time when I draw it out of the database using a SELECT statement....

What Id really like to do is INSERT it properly to a date field in the mysql database table
ralph_5uk
New php-forum User
New php-forum User
 
Posts: 3
Joined: Thu Feb 20, 2003 9:30 am

Postby Joan Garnet » Sun Feb 23, 2003 12:28 pm

Well,
you can't format the input of the DATE column type :(
but you could format the output using
Code: Select all
DATE_FORMAT(date,format)

Also, as you suggested you can save it in the database as varchar and then give it the format.

bye!
User avatar
Joan Garnet
Moderator
Moderator
 
Posts: 387
Joined: Sat Aug 03, 2002 2:56 am
Location: Mars

Postby ralph_5uk » Thu Feb 27, 2003 9:40 am

I wrote the function dateRotator read the text file into one big array, used array_chunk()

and hey presto

Code: Select all
$textfile = "c:/temp/test.txt";
$string =file_get_contents($textfile);
$token_array = split(",", $string);
$newarray=array_chunk($token_array,$number_fields);

for($i=0; $i<sizeof($newarray); $i++)
{
   //get the dates into the right format
   $newarray[$i][$dob_index] = dateRotator($newarray[$i][$dob_index]);
}


function dateRotator($olddate)
{
      $token_array = split("/", $olddate);
      $dash="-";
      $newdate = $token_array[2].$dash.$token_array[1].$dash.$token_array[0];
      return $newdate;
}

mysql_query("drop table if exists $table_name");
$qry_create = "CREATE TABLE ".$table_name." ( id int(11) NOT NULL auto_increment, field1 varchar(10), field2 varchar(10), field3 varchar(10), field4 varchar(10), date_posted date, PRIMARY KEY  (id) );";
mysql_query($qry_create);
   
for($i=0; $i<sizeof($newarray); $i++)
{

   $sqlA = "";
   $sqlA = "INSERT INTO ".$table_name."
                                                         (field1,
                                                          field2,
                                                          field3,
                                                          field4,
                                                          date_posted) VALUES
                                                                                    ('".$newarray[$i][0]."',
                                                                                     '".$newarray[$i][1]."',
                                                                                     '".$newarray[$i][2]."',
                                                                                     '".$newarray[$i][3]."',
                                                                                     '".$newarray[$i][4]."')";

   mysql_query($sqlA,$db) or die("Couldn't execute queryAAA.");

}

         
ralph_5uk
New php-forum User
New php-forum User
 
Posts: 3
Joined: Thu Feb 20, 2003 9:30 am


Return to mySQL & php coding

Who is online

Users browsing this forum: No registered users and 2 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.