using php to move rows from text file into mysql table

[mantrid]

Hello
Can anyone point me in the right direction for the way to read a text file a
line at a time and separate the fields on that line and use them as data in
an INSERT to add a record to a mysql table. Then read the second line in
text file and repeat.

Thanks for your time
Ian

[Rik]

On Tue, 19 Jun 2007 23:03:45 +0200, mantrid <ia********@virgin.netwrote:

Hello
Can anyone point me in the right direction for the way to read a text
file a
line at a time and separate the fields on that line and use them as data
in
an INSERT to add a record to a mysql table. Then read the second line in
text file and repeat.

Worst case: fgets() & explode()/split(), but depending on the format
fscanf() or even the great fgetcsv() can be your friends.
--
Rik Wasmus

[Rik]

On Tue, 19 Jun 2007 23:30:42 +0200, Rik <lu************@hotmail.comwrote:

On Tue, 19 Jun 2007 23:03:45 +0200, mantrid <ia********@virgin.net>
wrote:

>Hello
Can anyone point me in the right direction for the way to read a text
file a
line at a time and separate the fields on that line and use them as
data in
an INSERT to add a record to a mysql table. Then read the second line in
text file and repeat.

Worst case: fgets() & explode()/split(), but depending on the format
fscanf() or even the great fgetcsv() can be your friends.

Oh, and if it's possible: do not underestimate the power of LOAD DATA
INFILE syntax of mysql itself. No need to fuss around in PHP if MySQL can
do it practically on it's own.
--
Rik Wasmus

[mantrid]

thanks rik,
will look into your suggestions tomorrow.
ian

"Rik" <lu************@hotmail.comwrote in message
news:op.tt6t46t6qnv3q9@metallium...

On Tue, 19 Jun 2007 23:30:42 +0200, Rik <lu************@hotmail.com>

wrote:

>

On Tue, 19 Jun 2007 23:03:45 +0200, mantrid <ia********@virgin.net>
wrote:

Hello
Can anyone point me in the right direction for the way to read a text
file a
line at a time and separate the fields on that line and use them as
data in
an INSERT to add a record to a mysql table. Then read the second line

in

text file and repeat.

Worst case: fgets() & explode()/split(), but depending on the format
fscanf() or even the great fgetcsv() can be your friends.

Oh, and if it's possible: do not underestimate the power of LOAD DATA
INFILE syntax of mysql itself. No need to fuss around in PHP if MySQL can
do it practically on it's own.
--
Rik Wasmus

[mantrid]

"Rik" <lu************@hotmail.comwrote in message
news:op.tt6t46t6qnv3q9@metallium...

On Tue, 19 Jun 2007 23:30:42 +0200, Rik <lu************@hotmail.com>

wrote:

>

On Tue, 19 Jun 2007 23:03:45 +0200, mantrid <ia********@virgin.net>
wrote:

Hello
Can anyone point me in the right direction for the way to read a text
file a
line at a time and separate the fields on that line and use them as
data in
an INSERT to add a record to a mysql table. Then read the second line

in

text file and repeat.

Worst case: fgets() & explode()/split(), but depending on the format
fscanf() or even the great fgetcsv() can be your friends.

Oh, and if it's possible: do not underestimate the power of LOAD DATA
INFILE syntax of mysql itself. No need to fuss around in PHP if MySQL can
do it practically on it's own.
--
Rik Wasmus

Hi
Decided to use the fgetcsv(). couldnt get LOAD DTA INFILE to work. Im
getting close with the following script, but error message says query failed
as array doesnt contain data. Am I missing something obvious

**********************************
$YourFile = $uploadfile ;
$handle = fopen($YourFile, "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

list($userid, $companyid, $aimlisted, $amount, $price, $stamp, $comm,
$datetime) = explode(",", $data);
//echo
$userid."<br>".$companyid."<br>".$aimlisted."<br>" .$amount."<br>".$price."<b
r>".$stamp."<br>".$comm."<br>".$datetime;
$q = "INSERT INTO transactions
(transactionid,userid,companyid,aimlisted,amount,p rice,stamp,comm,datetime)
VALUES ('',$userid, $companyid, $aimlisted, $amount, $price, $stamp, $comm,
'$datetime')";
$add_buy = mysql_query($q) or die('<span class=RedWarning>Query failed: '
.. mysql_error().'</span>');

}
fclose($handle);
**************************************

I echoed the variables from the list (commented out in script) and they to
show they contain no data.
can anyone see where Ive gone wrong?

Ian

[Rik]

On Wed, 20 Jun 2007 22:07:07 +0200, mantrid <ia********@virgin.netwrote:

>
"Rik" <lu************@hotmail.comwrote in message
news:op.tt6t46t6qnv3q9@metallium...

>On Tue, 19 Jun 2007 23:30:42 +0200, Rik <lu************@hotmail.com>

wrote:

>>

On Tue, 19 Jun 2007 23:03:45 +0200, mantrid <ia********@virgin.net>
wrote:

Hello
Can anyone point me in the right direction for the way to read a text
file a
line at a time and separate the fields on that line and use them as
data in
an INSERT to add a record to a mysql table. Then read the second line

in

>text file and repeat.
Worst case: fgets() & explode()/split(), but depending on the format
fscanf() or even the great fgetcsv() can be your friends.

Oh, and if it's possible: do not underestimate the power of LOAD DATA
INFILE syntax of mysql itself. No need to fuss around in PHP if MySQL
can
do it practically on it's own.

Hi
Decided to use the fgetcsv(). couldnt get LOAD DTA INFILE to work. Im
getting close with the following script, but error message says query
failed
as array doesnt contain data. Am I missing something obvious

**********************************
$YourFile = $uploadfile ;
$handle = fopen($YourFile, "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

list($userid, $companyid, $aimlisted, $amount, $price, $stamp,
$comm,
$datetime) = explode(",", $data);

Well, the power of fgetscv() is in the fact that your should not need this
explode.
Try the list() directly on the $data array. If that doesn't work,
print_r($data) to check wether everything is OK.

--
Rik Wasmus

[mantrid]

"Rik" <lu************@hotmail.comwrote in message
news:op.tt8oweggqnv3q9@metallium...
On Wed, 20 Jun 2007 22:07:07 +0200, mantrid <ia********@virgin.netwrote:

>
"Rik" <lu************@hotmail.comwrote in message
news:op.tt6t46t6qnv3q9@metallium...

>On Tue, 19 Jun 2007 23:30:42 +0200, Rik <lu************@hotmail.com>

wrote:

>>

On Tue, 19 Jun 2007 23:03:45 +0200, mantrid <ia********@virgin.net>
wrote:

Hello
Can anyone point me in the right direction for the way to read a text
file a
line at a time and separate the fields on that line and use them as
data in
an INSERT to add a record to a mysql table. Then read the second line

in

>text file and repeat.
Worst case: fgets() & explode()/split(), but depending on the format
fscanf() or even the great fgetcsv() can be your friends.

Oh, and if it's possible: do not underestimate the power of LOAD DATA
INFILE syntax of mysql itself. No need to fuss around in PHP if MySQL
can
do it practically on it's own.

Hi
Decided to use the fgetcsv(). couldnt get LOAD DTA INFILE to work. Im
getting close with the following script, but error message says query
failed
as array doesnt contain data. Am I missing something obvious

**********************************
$YourFile = $uploadfile ;
$handle = fopen($YourFile, "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

list($userid, $companyid, $aimlisted, $amount, $price, $stamp,
$comm,
$datetime) = explode(",", $data);

Well, the power of fgetscv() is in the fact that your should not need this
explode.
Try the list() directly on the $data array. If that doesn't work,
print_r($data) to check wether everything is OK.

--
Rik Wasmus
That did it. thanks.
Just one problem with the field datetime (type datetime). values are not
saving, get 0000-00-00 00:00:00 added instead of the actual date and time.
All other fields update ok
Any clues.

Ian

[Rik]

On Thu, 21 Jun 2007 02:17:08 +0200, mantrid <ia********@virgin.netwrote:

That did it. thanks.
Just one problem with the field datetime (type datetime). values are not
saving, get 0000-00-00 00:00:00 added instead of the actual date and
time.

That is what happens if MySQL gets a string it cannot interpret as a
datetime field. Check the format of the date you have in the file, and
adjust it before inserting to MySQL's format.

--
Rik Wasmus

[mantrid]

"Rik" <lu************@hotmail.comwrote in message
news:op.tt9gsfppqnv3q9@metallium...

On Thu, 21 Jun 2007 02:17:08 +0200, mantrid <ia********@virgin.netwrote:

That did it. thanks.
Just one problem with the field datetime (type datetime). values are not
saving, get 0000-00-00 00:00:00 added instead of the actual date and
time.

That is what happens if MySQL gets a string it cannot interpret as a
datetime field. Check the format of the date you have in the file, and
adjust it before inserting to MySQL's format.

--
Rik Wasmus

Is it best to do that in Excel or use a php date format function?
In Excel it is dd/mm/yyy
but it wont allow me to save as a CVS file in the form yyy-mm-dd hh:mm:ss,
it just changes it back.
ive tried DATE_FORMAT('".$dateandtime."','%Y-%m-%d %H:%i:%s') in the sql
statement but its not working, am I using this function wrongly?

Do you know a php function that does a similar thing? I know the date format
characters (eg d M Y etc) but can only see examples of their use with
date(), which is the current date, not a date from my CVS file.

[Michael Fesser]

..oO(mantrid)

>Is it best to do that in Excel or use a php date format function?
In Excel it is dd/mm/yyy
but it wont allow me to save as a CVS file in the form yyy-mm-dd hh:mm:ss,
it just changes it back.
ive tried DATE_FORMAT('".$dateandtime."','%Y-%m-%d %H:%i:%s') in the sql
statement but its not working, am I using this function wrongly?

Yep, but doesn't matter in this case.

>Do you know a php function that does a similar thing? I know the date format
characters (eg d M Y etc) but can only see examples of their use with
date(), which is the current date, not a date from my CVS file.

Try strtotime(). If it doesn't work as expected (for example dd/mm/yyyy
vs. mm/dd/yyyy), use string functions to split the Excel date and create
your own date string in MySQL format.

Micha

[Lars Eighner]

In our last episode,
<mv********************************@4ax.com>,
the lovely and talented Michael Fesser
broadcast on comp.lang.php:

.oO(mantrid)

>>Is it best to do that in Excel or use a php date format function?
In Excel it is dd/mm/yyy
but it wont allow me to save as a CVS file in the form yyy-mm-dd hh:mm:ss,
it just changes it back.
ive tried DATE_FORMAT('".$dateandtime."','%Y-%m-%d %H:%i:%s') in the sql
statement but its not working, am I using this function wrongly?

Yep, but doesn't matter in this case.

>>Do you know a php function that does a similar thing? I know the date format
characters (eg d M Y etc) but can only see examples of their use with
date(), which is the current date, not a date from my CVS file.

Try strtotime(). If it doesn't work as expected (for example dd/mm/yyyy
vs. mm/dd/yyyy), use string functions to split the Excel date and create
your own date string in MySQL format.

Some additional notes: You used yyy twice in your post, so I wonder
if it is a typo. MySQL will accept 2 or 4 digit years, but I have my
doubts about 3.

Also DATETIME and DATE field will accept any separator, so if something goes
screwy with the date and you send 'HH:MM:SS' first it will try to interpret
that as the date HH-MM-SS. For MM over 12 and SS over 31 that will always
produce an invalid date which will default to 0000-00-0000.

I believe TIMESTAMP is stricter. Also, I note there have been changes
in the relevant functions / data structures of both PHP and My SQL, so
you should check the manuals of the version you are actually running.
Perfectly good advice may not work for you if you have a legacy system.

But if you discover exactly what your MySQL wants, rolling your own function
to manipulate the date string is good advice that will always work. If you
have a MySQL client on the pertinent server and a sandbox database, the best
way to determine exactly what your MySQL wants is to try it from the MySQL
client. You cannot expect to use PHP to write a query line that works if
you cannot type in a query that works at the MySQL client prompt.

--
Lars Eighner <http://larseighner.com/ <http://myspace.com/larseighner>
Countdown: 578 days to go.
Owing to googlegroups not screening users to eliminate spammers and other
USENET abusers, I do not see most posts from googlegroups.

[mantrid]

Hello

I used phpmyadmin to run an sql to insert a record into the table. The only
one that worked was

INSERT INTO transactions
(transactionid,userid,companyid,aimlisted,amount,p rice,stamp,comm,datetime)
VALUES ('',1, 2, 1, 11111, .76, 3.54, 7, '2002-04-10')

ie Using a date in the correct format as the mysql table and in quotes (hrs
mins secs were not needed), leave out the quotes or in any other format and
it doesnt work.

ian
"Lars Eighner" <us****@larseighner.comwrote in message
news:sl********************@goodwill.larseighner.c om...

In our last episode,
<mv********************************@4ax.com>,
the lovely and talented Michael Fesser
broadcast on comp.lang.php:

.oO(mantrid)

>Is it best to do that in Excel or use a php date format function?
In Excel it is dd/mm/yyy
but it wont allow me to save as a CVS file in the form yyy-mm-dd

hh:mm:ss,

>it just changes it back.
ive tried DATE_FORMAT('".$dateandtime."','%Y-%m-%d %H:%i:%s') in the sql
statement but its not working, am I using this function wrongly?

Yep, but doesn't matter in this case.

>Do you know a php function that does a similar thing? I know the date

format

>characters (eg d M Y etc) but can only see examples of their use with
date(), which is the current date, not a date from my CVS file.

Try strtotime(). If it doesn't work as expected (for example dd/mm/yyyy
vs. mm/dd/yyyy), use string functions to split the Excel date and create
your own date string in MySQL format.

Some additional notes: You used yyy twice in your post, so I wonder
if it is a typo. MySQL will accept 2 or 4 digit years, but I have my
doubts about 3.

Also DATETIME and DATE field will accept any separator, so if something

goes

screwy with the date and you send 'HH:MM:SS' first it will try to

interpret

that as the date HH-MM-SS. For MM over 12 and SS over 31 that will always
produce an invalid date which will default to 0000-00-0000.

I believe TIMESTAMP is stricter. Also, I note there have been changes
in the relevant functions / data structures of both PHP and My SQL, so
you should check the manuals of the version you are actually running.
Perfectly good advice may not work for you if you have a legacy system.

But if you discover exactly what your MySQL wants, rolling your own

function

to manipulate the date string is good advice that will always work. If

you

have a MySQL client on the pertinent server and a sandbox database, the

best

way to determine exactly what your MySQL wants is to try it from the MySQL
client. You cannot expect to use PHP to write a query line that works if
you cannot type in a query that works at the MySQL client prompt.

--
Lars Eighner <http://larseighner.com/>

<http://myspace.com/larseighner>

Countdown: 578 days to go.
Owing to googlegroups not screening users to eliminate spammers and

other

USENET abusers, I do not see most posts from googlegroups.

[mantrid]

"mantrid" <ia********@virgin.netwrote in message
news:oj****************@newsfe2-gui.ntli.net...

Hello

I used phpmyadmin to run an sql to insert a record into the table. The

only

one that worked was

INSERT INTO transactions

(transactionid,userid,companyid,aimlisted,amount,p rice,stamp,comm,datetime)

VALUES ('',1, 2, 1, 11111, .76, 3.54, 7, '2002-04-10')

ie Using a date in the correct format as the mysql table and in quotes

(hrs

mins secs were not needed), leave out the quotes or in any other format

and

it doesnt work.

ian
"Lars Eighner" <us****@larseighner.comwrote in message
news:sl********************@goodwill.larseighner.c om...

In our last episode,
<mv********************************@4ax.com>,
the lovely and talented Michael Fesser
broadcast on comp.lang.php:

.oO(mantrid)

>>Is it best to do that in Excel or use a php date format function?
>>In Excel it is dd/mm/yyy
>>but it wont allow me to save as a CVS file in the form yyy-mm-dd

hh:mm:ss,

>>it just changes it back.
>>ive tried DATE_FORMAT('".$dateandtime."','%Y-%m-%d %H:%i:%s') in the

sql

>>statement but its not working, am I using this function wrongly?

Yep, but doesn't matter in this case.

>>Do you know a php function that does a similar thing? I know the date

format

>>characters (eg d M Y etc) but can only see examples of their use with
>>date(), which is the current date, not a date from my CVS file.

Try strtotime(). If it doesn't work as expected (for example

dd/mm/yyyy

vs. mm/dd/yyyy), use string functions to split the Excel date and

create

your own date string in MySQL format.

Some additional notes: You used yyy twice in your post, so I wonder
if it is a typo. MySQL will accept 2 or 4 digit years, but I have my
doubts about 3.

Also DATETIME and DATE field will accept any separator, so if something

goes

screwy with the date and you send 'HH:MM:SS' first it will try to

interpret

that as the date HH-MM-SS. For MM over 12 and SS over 31 that will

always

produce an invalid date which will default to 0000-00-0000.

I believe TIMESTAMP is stricter. Also, I note there have been changes
in the relevant functions / data structures of both PHP and My SQL, so
you should check the manuals of the version you are actually running.
Perfectly good advice may not work for you if you have a legacy system.

But if you discover exactly what your MySQL wants, rolling your own

function

to manipulate the date string is good advice that will always work. If

you

have a MySQL client on the pertinent server and a sandbox database, the

best

way to determine exactly what your MySQL wants is to try it from the

MySQL

client. You cannot expect to use PHP to write a query line that works

if

you cannot type in a query that works at the MySQL client prompt.

--
Lars Eighner <http://larseighner.com/>

<http://myspace.com/larseighner>

Countdown: 578 days to go.
Owing to googlegroups not screening users to eliminate spammers and

other

USENET abusers, I do not see most posts from googlegroups.

ok got it

had to change

$handle = fopen($YourFile, "r");
while (($data = fgetcsv($handle, 1000, "','")) !== FALSE) {
list($userid, $companyid, $aimlisted, $amount, $price, $stamp, $comm,
$dateandtime) = $data;

to

$handle = fopen($YourFile, "r");
while (($data = fgetcsv($handle, 1000)) !== FALSE) {
list($userid, $companyid, $aimlisted, $amount, $price, $stamp, $comm,
$dateandtime) = $data;
that will teach me not to just cut and paste others peoples use of the
functions before reading them properly.
and

$q = "INSERT INTO transactions
(transactionid,userid,companyid,aimlisted,amount,p rice,stamp,comm,datetime)
VALUES ('',$userid, $companyid, $aimlisted, $amount, $price, $stamp,
$comm,'".$dateandtime."')";
to
$q = "INSERT INTO transactions
(transactionid,userid,companyid,aimlisted,amount,p rice,stamp,comm,datetime)
VALUES ('',$userid, $companyid, $aimlisted, $amount, $price, $stamp,
$comm,'".$dateandtime."')";

the insertion of the quotes around $dateandtime allowed it to be accepted. I
also didnt need to manipulate $dateandtime to change it from dd/mm/yyyy to
yyyy-mm-dd, this appears to have happened automatically when using fgetcsv()
or possibly at the mysql insert stage.

Thanks all for the help

Ian