Updating a table through php

stephdumais

Hello,
I realize I could import data from a text file directly into a table with a SQL query, but I'm looking to write a function that would:

Read my text file into an array
Split each line into individual elements (delimited by a coma for example)
update the MySQL table with this data: each line as a row, each line element as a column

Of course, the number of elements delimited by comas on each line reflects the number of fields in the aformentioned table.

Anyone?
Thanks!

Pig

Well, I won't write the code for you, but I will give you the steps.

fopen() and fread() to get the file.

split() based on a character or \r\n to get the new lines

foreach() on your new array:
-split() the current line or possible explode() depending to create a sub-array
-sql insert with new sub-array

stephdumais

Hi, thanks... the array stuff I can figure out, it's the MySQL insert I'm mostly confused about. Could you perhaps let me in on the code for that part at least?

Pig

So if the line looks like:
$line = "value 1, value 2, value 3, value 4, value 5";

then the array might be like this:
$array = explode(", ", $line);

and the sql would look like:
mysql_query("INSERT INTO table_name (col_1, col_2, col_3, col_4, col_5) VALUES ('$array[0]', '$array[1]', '$array[2]', '$array[3]', '$array[4]')");

stephdumais

Thanks, this is exactly what I was looking for...though I have to try it first. Thanks again..

phplearner

stephdumais:

If you get it working, I hope you can share the final outcome 'cuz I'm stuck trying to do the same thing!

Thanks very much.

stephdumais

Certainly:

<?php

$connID = mysql_pconnect($sqlhost, $sqluser, $sqlpass) or
die($errorMsg[1]." ErrorNo. ".mysql_errno()." MySQL said: ".mysql_error());

mysql_select_db($sqldb, $connID) or
die($errorMsg[2]." ErrorNo. ".mysql_errno()." MySQL said: ".mysql_error());

#source file located in same directory as this script:

$lines = file ('list.txt');

foreach ($lines as $line_num => $line) {

in my phonebook application I was using these 7 fields:

$query = "INSERT INTO ab_records (Department,WorkPh,FirstName,LastName,HomePh,MobilePh,Email1) VALUES ('$array2[0]','$array2[1]','$array2[2]','$array2[3]','$array2[4]','$array2[5]','$array2[6]')";

my source file has each element delimited by comas

$line = trim($line);
$array2 = explode(",", $line);

This is the line that actually launches the query.

You can comment it out if all you want is the next function...

mysql_query($query, $connID);

this is a function I just added to display

the source file lines and replace elements

if needed. I then copy-pasted the output into

the source file itself again. You could you a

file writing command for this of course

$tobereplaced = "none";
$replacement = "";

if ($array2[0] == $tobereplaced){ echo $replacement.","; } else { echo $array2[0].","; }
if ($array2[1] == $tobereplaced){ echo $replacement.","; } else { echo $array2[1].","; }
if ($array2[2] == $tobereplaced){ echo $replacement.","; } else { echo $array2[2].","; }
if ($array2[3] == $tobereplaced){ echo $replacement.","; } else { echo $array2[3].","; }
if ($array2[4] == $tobereplaced){ echo $replacement.","; } else { echo $array2[4].","; }
if ($array2[5] == $tobereplaced){ echo $replacement.","; } else { echo $array2[5].","; }
if ($array2[6] == $tobereplaced){ echo $replacement." "; } else { echo $array2[6]." "; }

}
?>

phplearner

stephdumais:

Thank you very much for posting your script to learn from.

I'll try some retweaks for my situation and want to study it in detail. Arrays are kinda "Foreign" to me and was also reading up on 'foreach', but got hung up on the '$keys=>$value' option stuff(which you didn't use here).

Apparently, you upload the .csv file TO the same directory as the script on the server. Are you aware of any way to have the script access the .csv file directly from a PC hard drive (or floppy/CD-ROM) and still work...like with some kind of "Browse" (for file) option?

I'll go read up on fopen & fread...apparently to open & read the .csv file if it's in a different location?

Many thanks.

bigtall

Thank you so much for posting that code. I have a thread going about doing this so it's helped a ton! Thanks for the comments as well. I'm starting to figure this stuff out...

stephdumais

Glad to be of service...you have to thank Pig as well. But it is nice to give back a bit after all the help I've gotten here 🙂

bigtall

Yes, thanks Pig ;-)

One question for you though. Everything works great except that in the file i'm exploding there is a name that is separated by a comma that it obviously separates into two separate elements (ex - array[0] stores "MacDonald" and array[1] stores "Bob" when I want to have array[0] store "MacDonald,Bob"). The array will have 18 elements when it reads each line from the file so this process will knock it down to 17, which is the amount of elements I have in my table. Is it just easier to keep the last name/first name separate or is there an easy answer to this?

bigtall

Bah, nevermind. I'll just change the delimiter to something other than a comma 🙂

Sometimes my noobness amazes even me LOL

examancer

TAB makes a good delimiter... thats why most GUI database programs (Access, FileMaker, etc..) allow export into TAB seperated values.

stephdumais

PHPLearner... what you want is a file upload, no? I manually uploaded my file via an FTP client because this was a one-time use thing for my project but you can certainly add a form there with an upload function.
The script wouldnt actually read the file off your local drive, but a copy in the upload temp directory on the server... this is beyond my knowledge at the moment unfortunately...

phplearner

Originally posted by stephdumais
Glad to be of service...you have to thank Pig as well. But it is nice to give back a bit after all the help I've gotten here 🙂

Yes... (Pig)... "You da man!". Umh, guess that should be: "You da Pig!"

😃

bigtall

Originally posted by examancer
TAB makes a good delimiter... thats why most GUI database programs (Access, FileMaker, etc..) allow export into TAB seperated values.

Yep, i'm using a tab delimiter and it's working like a charm!

phplearner

Originally posted by stephdumais
PHPLearner... what you want is a file upload, no? I manually uploaded my file via an FTP client because this was a one-time use thing for my project but you can certainly add a form there with an upload function.
The script wouldnt actually read the file off your local drive, but a copy in the upload temp directory on the server... this is beyond my knowledge at the moment unfortunately...

Well, thank you very much for your contribution. I'll go poking around for a possible script solution. I'll assume a /temp directory can be in my 'User" area(s) 'cuz I don't have 'root' access on my ISP's server where the actual MySQL DB stuff is located.

Pig

yeah, commas can be a problem depending on your content. I use | alot, though at one point I used something strange like: :😐:: because every other combination I came up with was actually in the content, and I was getting the same kind of improper explosion() that you had.