using split to format data?

[Anon]

I've read all the forums and can find anything like what i'm trying to do when formatting a date. So tell me if this is possible and if so maybe how I could go about it.

I have a csv file to upload to my db. The date is in the format of 8/20/2001.

Is there any way that I could split the date with the (/), assign is section a variable name and then format it properly in the db via the variable names?

[khaine]

$bits=explode("/", $datein);

$dd=$bits[0];
$mm=$bits[1];
$yy=$bits[2];

this will split the string datein into its component parts using the / as a reference.

Mark

[Anon]

So if i split it like this, then how exactly should I upload it into the db?

thanks

$split_date=explode("/", $intro_date);

$mm=$split_date[0];
$dd=$split_date[1];
$yy=$split_date[2];

mysql_query("UPDATE products SET intro_date = ['$yy/$mm/$dd']");

[pornost4r]

you can do it also with 'list'

[Anon]

How might i do that?

[khaine]

yep that would do it

[Anon]

Any idea why this would come back null in the db all the time?

I'm loading the csv file and executing this command upon upload. Intro_date is the field with the 08/20/2001 format.

$newdate = $intro_date;
list($month,$day,$year) = split('/',$newdate);
$newdate = '$year-$month-$day';

mysql_query("UPDATE products SET new_date = ['$newdate']");

[khaine]

Cant says exactly but try echoing what you send to the database just to make sure it is writing it correctly.

$newdate = $intro_date;
list($month,$day,$year) = split('/',$newdate);
$newdate = '$year-$month-$day';

echo "(".$newdate.")";

mysql_query("UPDATE products SET new_date = ['$newdate']");

I always surround an echo with brackets to see if there are any strange characters added on the end like line feeds or tabs.

[Anon]

Two things...

Is the database field set up as a DATE or DATETIME field? It probably is, but sometimes the smallest detail gets in the way.

Also, I wonder if you'd consider using PHP's date functions for your needs. I have similar scripts where a user input in MM/DD/YYYY must be converted for a database, so I use PHP's date functions to do the conversion like so:

//$user_input is whatever string you are trying to convert. The strtotime() function can take just about any string that looks like a date and convert it into a unix timestamp

$time = strtotime($user_input);
$converted_time = date("Y-m-d", $time);

//and then

$query = "INSERT into table (datefield) VALUES ('$converted_time')";

and so on....

It works fine, no null values in the database.

[Anon]

Your describing exactly what I want to do but you lost me what exactly I should do. I'm definitely new to php.

I have the csv filing being input with date mm/dd/yyyy. the field is curretnly set up as varchar because date scrambles the date all up.

Is was i'm trying to do feasible?
Thanks,

chad

[Anon]

First, are you using MySQL? If not, what type of database? I'll proceed as if your answer is MySQL since many use that with PHP.

I prefer using DATE or DATETIME fields to store timestamps if possible; you have some interesting functions available to you that way.

As I understand your application, you have a form field for the date with format mm/dd/yyyy. We'll call that $input_date.

The strtotime() function, as the manual explains, takes any string and tries to make a UNIX timestamp out of it. It's served well handling any kind of input from 08/20/2001 to 20 August 2001.

So that's the first step in processing your input.

$timestamp = strtotime($input_date);

OK, now you have to make that timestamp agreeable to your database. Using a MySQL DATE field, the database requires YYYY-MM-DD format. The PHP function date() returns a string nicely formatted to the options you give it. See the manual for more about this function and the options and arguments it takes. For a MySQL DATE field, it would look like this:

$converted_date = date("Y-m-d", $timestamp);

Now $converted_date is in a format that you can use in a query to a MySQL database.

//Connect to the database first of course
$query = "INSERT into tablename (date_field) VALUES '$converted_date'";
$result = mysql_query($query) or die("Error message here.");

Using this date functions is really simple and a lot easier on the brain (for me anyway) than keeping track of arrays and rearranging exploded strings.

[Anon]

I really appreciate the help. I just have one more minor problem with this whole concept. First i'll show the code then describe my concern:

<?
//check for file upload
if(isset($UploadedFile1)) {
print("Local File: $UploadedFile1 <BR>");
print("Name: $UploadedFile1_name <BR>");
print("Size: $UploadedFile1_size <BR>");
print("<HR>");
if ($UploadedFile1<>"none")

			if(!copy($UploadedFile1,"/usr/local/etc/httpd/vhosts/udllabs/data/".$UploadedFile1_name)) 


            print "File failed to upload"; 
            } 

?>

<?php

if ($submit) {

$db = mysql_connect();

mysql_select_db("udllabs");

mysql_query("DROP TABLE IF EXISTS products");
mysql_query("create table products (
amerisource VARCHAR(20) NULL,
bbc VARCHAR(20) NULL,
bindley_western VARCHAR(20) NULL,
cardinal_corp VARCHAR(20) NULL,
cardinal_whitmire VARCHAR(20) NULL,
mcKesson VARCHAR(20) NULL,
morris_dickson VARCHAR(20) NULL,
ndc VARCHAR(255) NULL,
product_description VARCHAR(255)NULL,
size VARCHAR(255) NULL,
code VARCHAR(255) NULL,
manufacturer VARCHAR(255) NULL,
compare_product VARCHAR(255) NULL,
compare_mfg VARCHAR(255) NULL,
te_rating VARCHAR(255) NULL,
color VARCHAR(255) NULL,
shape VARCHAR(255) NULL,
id_side1 VARCHAR(255) NULL,
id_side2 VARCHAR(255) NULL,
intro_date VARCHAR(255) NULL,
wholesale VARCHAR(255) NULL,
awp VARCHAR(255) NULL,
product_status VARCHAR(255) NULL,
status VARCHAR(255) NULL,
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY)"
);

mysql_query ('load data infile "/usr/local/etc/httpd/vhosts/udllabs/data/products.csv" into table products FIELDS TERMINATED BY "," ENCLOSED BY "\"" ');

$timestamp = strtotime($intro_date);
$converted_date = date("Y-m-d", $timestamp);

mysql_query("UPDATE products SET code = 'Bingo Card' WHERE code = 'O'");
mysql_query("UPDATE products SET code = 'Robot Ready' WHERE code = 'R'");
mysql_query("UPDATE products SET code = 'Emergi-Script' WHERE code = 'S'");
mysql_query("UPDATE products SET code = 'Unit Dose' WHERE code = 'A' OR code = 'B'");
mysql_query("UPDATE products SET intro_date = '$converted_date'");

echo "<p>Thank you! The UDL product database has been updated.</p><br>";

} else{

// display form

?>

<form method="post" action="<?php echo $PHP_SELF?>">

<input type="Submit" name="submit" value="Update Product Database">

</form>

<?php

} // end if

?>

I'm using load data rather than insert statements because i'm using a web form to upload the data then a link to active it or load it into the db.

With this method of yours, is there a way to actually convert the 486 dates ( they're not all the same date) to the yyyy/mm/dd format after they are loaded into there column in the db. The way that i have it set up above changes them all but, they all change to the same date. Whatever is first is what they ALL change to.

thanks again