populating Database using a textfile

[boyzdynasty]

I want to use PHP to populate mySQL DB...

the textfile looks like this...

first|last|password|address|state|city|zip|phone|e mail|comments
first|last|password|address|state|city|zip|phone|e mail|comments
first|last|password|address|state|city|zip|phone|e mail|comments
first|last|password|address|state|city|zip|phone|e mail|comments
first|last|password|address|state|city|zip|phone|e mail|comments
first|last|password|address|state|city|zip|phone|e mail|comments

*the dilimiter is the '|' character
ok...you get the pix...

So how do I read from a file and insert it into the DB?
B/c I don't want to copy and paste the values and do an INSERT at a time...

If anyone is familar w/ this, I really appreciate the help.

Thanx in advance.

[boyzdynasty]

oops... the delimiter is a 'tab'... i have the data in an excel spreadsheet.
I could only save it as a txt file with 'tab' as the delimiter :(

[CyanBlue]

Um... What are you trying to do, boyzdynasty???

Is this what you want???

You have a database in Excel... and you export the text file from there, and you want to put the data back to MySQL database via PHP???

(It is very unlikely that I'd come up with the answer without actually trying it, but I just wanted to clarify what you are asking so that other people can see and understand the question... :p)

[boyzdynasty]

yes. thanx for repeating.

well...i had to do the hard way :(

b/c one of the fields I had to encrypt b4 inserting into DB.

So i ended up making a *.sql file...instead w/ the insert command for each record and encrypting the password field...

took me 2hrs...maybe more...
b/c I had madd syntax errors and it was just a lot of records to fix. *sigh*

But I did figure out how to run *sql files.

[boyzdynasty]

i was tellin' Ricod, I don't mind doing all the group work...well...
i'm getting annoyed...now

they were complaining and stressing about how I will not finish in time.

*errRRR*

---watever---

[CyanBlue]

But I did figure out how to run *sql files.How??? I wanna know!!! :)they were complaining and stressing about how I will not finish in time.I'll either kick their butts or I won't do it in time if I were you... :(

[boyzdynasty]

this course (the grade for this course) depends on the project. *sigh*

I really want an 'A' since I am using things (FLASH, PHP,mySQL) that I want to excel in. so, the 'A' is a reward of all my hardwork :)

at the shell prompt...command line

mysql -u userName -p dbName < *.sql
then will be prompt for password.

but if you didn't set a password and stuff...
mysql dbName < *.sql

[CyanBlue]

Ah... Got it... ;)

I had no idea what that *.SQL file looks like... So, I did a quick search on my computer to find some *.SQL files and opened it in the text editor... Now I get it...

Thanks... ;)

Yeah... That is painful thing to do... Just give them as much work as you can, and see if they can pull it out... And give your professor some hint that you have done all the work... I guess that's the only thing you can do to accomplish the project and make them suffer at the same time... ;)

[boyzdynasty]

just to note... the file doesn't have to end in *.sql

as long it has mySQL syntax, the compiler or whatever that parses the code will be able to decipher it.

-------------------

So far, the project looks crappy. :(
All I'm concentrating on is getting the project done (the functionality part).
And start making better transitions to each piece of the flash movie to make it look pretty.

-------------------

O...the demo didn't go so well. The prof. said she was worried that we will not get it done in time base on what we had.
O...well...I just got to get it done ASAP so I can start studyin' for finals for my other classes.

[freddycodes]

Just another note, it would have taken no time at all to write some PHP code to parse the text file and create sql file for you or even better yet, to just insert it into the database.


You could have used file() to read the contents into an array, used explode on each element of the array to make the array of fields for each line, and simply looped through and inserted them into the database. Wish I would have seen your post earlier, I could have saved you an hour and 40 minutes easily.

[boyzdynasty]

i kinda follow you.

but can you post up a sample file.

[freddycodes]

Okay when exporting from Excel you can specify to have the field names be the first line of the text file, so assuming the excel field names are the same as the column names in MySQL, you could manually put them in the text file as well.

Lets say the text file looked like

first last password address state city zip phone email comments
John Smith myPass 10 Some St SomeState SomeCity SomeZipCode Somephone someone@mail.com fldgj lhjglk jdl hjdg
John Smith myPass 10 Some St SomeState SomeCity SomeZipCode Somephone someone@mail.com fldgj lhjglk jdl hjdg
John Smith myPass 10 Some St SomeState SomeCity SomeZipCode Somephone someone@mail.com fldgj lhjglk jdl hjdg
John Smith myPass 10 Some St SomeState SomeCity SomeZipCode Somephone someone@mail.com fldgj lhjglk jdl hjdg
John Smith myPass 10 Some St SomeState SomeCity SomeZipCode Somephone someone@mail.com fldgj lhjglk jdl hjdg
John Smith myPass 10 Some St SomeState SomeCity SomeZipCode Somephone someone@mail.com fldgj lhjglk jdl hjdg
John Smith myPass 10 Some St SomeState SomeCity SomeZipCode Somephone someone@mail.com fldgj lhjglk jdl hjdg
John Smith myPass 10 Some St SomeState SomeCity SomeZipCode Somephone someone@mail.com fldgj lhjglk jdl hjdg
John Smith myPass 10 Some St SomeState SomeCity SomeZipCode Somephone someone@mail.com fldgj lhjglk jdl hjdg
John Smith myPass 10 Some St SomeState SomeCity SomeZipCode Somephone someone@mail.com fldgj lhjglk jdl hjdg
John Smith myPass 10 Some St SomeState SomeCity SomeZipCode Somephone someone@mail.com fldgj lhjglk jdl hjdg
John Smith myPass 10 Some St SomeState SomeCity SomeZipCode Somephone someone@mail.com fldgj lhjglk jdl hjdg
John Smith myPass 10 Some St SomeState SomeCity SomeZipCode Somephone someone@mail.com fldgj lhjglk jdl hjdg
John Smith myPass 10 Some St SomeState SomeCity SomeZipCode Somephone someone@mail.com fldgj lhjglk jdl hjdg
John Smith myPass 10 Some St SomeState SomeCity SomeZipCode Somephone someone@mail.com fldgj lhjglk jdl hjdg
John Smith myPass 10 Some St SomeState SomeCity SomeZipCode Somephone someone@mail.com fldgj lhjglk jdl hjdg
John Smith myPass 10 Some St SomeState SomeCity SomeZipCode Somephone someone@mail.com fldgj lhjglk jdl hjdg




The following PHP code be used to make the INSERT line.

<?php
$data = file("file.txt");
$fields = explode("\t", array_shift($data));
foreach($data as $key => $val) {
$val = chop($val);
$tmp = explode("\t", $val);
$str = "INSERT INTO TABLENAME SET ";
$f = array();
foreach($tmp as $key2 => $val2) {
if($fields[$key2] == 'password') {
$f[] = "{$fields[$key2]} = md5('$val2')";
}
else {
$f[] = "{$fields[$key2]} = '$val2'";
}
}
$str .= implode(",", $f).";\n";
print $str;
}
?>

[boyzdynasty]

thank for that.
I'll play around w/ it tonight.

This will work wonders for me in the future :) ... *well...just save some time*