using php to move rows from text file into mysql table

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

re: using php to move rows from text file into mysql table

On Tue, 19 Jun 2007 23:03:45 +0200, mantrid <ian.dandav@virgin.netwrote:
Worst case: fgets() & explode()/split(), but depending on the format
fscanf() or even the great fgetcsv() can be your friends.
--
Rik Wasmus

re: using php to move rows from text file into mysql table

On Tue, 19 Jun 2007 23:30:42 +0200, Rik <luiheidsgoeroe@hotmail.comwrote:
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

re: using php to move rows from text file into mysql table

thanks rik,
will look into your suggestions tomorrow.
ian

"Rik" <luiheidsgoeroe@hotmail.comwrote in message
news:op.tt6t46t6qnv3q9@metallium...wrote:in

re: using php to move rows from text file into mysql table

"Rik" <luiheidsgoeroe@hotmail.comwrote in message
news:op.tt6t46t6qnv3q9@metallium...wrote:in
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

re: using php to move rows from text file into mysql table

On Wed, 20 Jun 2007 22:07:07 +0200, mantrid <ian.dandav@virgin.netwrote:
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

re: using php to move rows from text file into mysql table

"Rik" <luiheidsgoeroe@hotmail.comwrote in message
news:op.tt8oweggqnv3q9@metallium...
On Wed, 20 Jun 2007 22:07:07 +0200, mantrid <ian.dandav@virgin.netwrote:
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

re: using php to move rows from text file into mysql table

On Thu, 21 Jun 2007 02:17:08 +0200, mantrid <ian.dandav@virgin.netwrote:
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

re: using php to move rows from text file into mysql table

"Rik" <luiheidsgoeroe@hotmail.comwrote in message
news:op.tt9gsfppqnv3q9@metallium...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.

re: using php to move rows from text file into mysql table

..oO(mantrid)
Yep, but doesn't matter in this case.
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

re: using php to move rows from text file into mysql table

In our last episode,
<mv7l73l0pdv6bo8mnq1kqrv6djubcjnoon@4ax.com>,
the lovely and talented Michael Fesser
broadcast on comp.lang.php:
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.

re: using php to move rows from text file into mysql table

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" <usenet@larseighner.comwrote in message
news:slrnf7lkh9.1qie.usenet@goodwill.larseighner.c om...hh:mm:ss,formatgoesinterpretfunctionyoubest<http://myspace.com/larseighner>other

re: using php to move rows from text file into mysql table

"mantrid" <ian.dandav@virgin.netwrote in message
news:ojBei.2247$_14.155@newsfe2-gui.ntli.net...only(transactionid,userid,companyid,aimlisted,amount,p rice,stamp,comm,datetime)(hrsandsqldd/mm/yyyycreatealwaysMySQLif
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