Page 1 of 1
Save data into phpMyAdmin database from a textfile in PHP
Posted: Fri May 14, 2010 4:02 am
by yintong
Hello.
I am going to save data into database (phpMyAdmin) from a textfile which it contains data with break-line.
I want it to read line by line and insert it into the database. Or rather
insert only the latest data which is newly added into the textfile.
I seriously need help on this as i couldn't search much of this information online.
They are mostly using shell method but i wanna do it in PHP.
How am i going to do it?

Re: Save data into phpMyAdmin database from a textfile in PH
Posted: Fri May 14, 2010 9:25 am
by mikosiko
yintong wrote:I am going to save data into database (phpMyAdmin)
Do you mean that you want to load the text file content into a "table" which reside in a "database" using the PhpMyAdmin interface?.... (

just giving you a little better concepts ).
- Do you have the table that will receive the data already defined in your database?
- what kind of field delimiters your text file use?... is a csv file or something different?
- Is any reason why you need to do that in PHP?... asking because normally is easier and probably faster to do that using line commands or even using the SQL Script option in PhpMyAdmin if that is the tool that you know better.
I normally use the "LOAD DATA INFILE" (Mysql) sentence, this is an example:
Code: Select all
LOAD DATA INFILE 'c:/subfolder/myfile.csv'
INTO TABLE mytable FIELDS TERMINATED BY ','
(mytable_field1,mytable_field2,.... mytable_fieldn,@mytable_datefield)
SET mytable_datefield = date_format(str_to_date(@mytable_datefield,'%m/%d/%Y'),'%Y/%m/%d');
this sentence can be modified in many ways to match your text file structure or, like in your case insert only the new added lines from your text file (using the "IGNORE" parameter).
In particular this command is normally way faster (20 times depending on conditions) than using regular "INSERT" (the sentence that you will need to use if you do the load programing with PHP), and with some expertize the performance could even be improved.
Here is the link to the LOAD DATA instruction if you want to take a look a it:
http://dev.mysql.com/doc/refman/5.1/en/load-data.html
miko
Re: Save data into phpMyAdmin database from a textfile in PH
Posted: Tue May 18, 2010 4:00 am
by yintong
Hi miko,
sorry for late reply. just got to see your reply now as i'm busy.
really thanks alot for your solution.
yes, you are right.
i've already got a table created in the database.
but would like to ask whether the textfile should have the same column as in the database?
like for example, the information in my textfile is like this, where
'xxxxx' is in varchar, the space in between is by using tab & lastly
'1234' is in varchar:
'xxxxx 1234'
can the LOAD DATA INFILE Syntax still read it and save it into the database as accordingly?

Re: Save data into phpMyAdmin database from a textfile in PH
Posted: Tue May 18, 2010 4:13 am
by yintong
Hi miko,
Here's the answer to your doubts:
1) Do you have the table that will receive the data already defined in your database?
Ans: Yes, i do already have a table in my database.
2) What kind of field delimiters your text file use?... is a csv file or something different?
Ans: I'm actually using textfile, in
.txt delimiters.
3) Is any reason why you need to do that in PHP?... asking because normally is easier and probably faster to do that using line commands or even using the SQL Script option in PhpMyAdmin if that is the tool that you know better.
Ans: The reason why i use PHP is because i need it to add data to a textfile first before clicking on a button to ask the computer to read from textfile and then save it into database again.
I hope it's much clearer and you have a better understanding now.

Thanks!
Re: Save data into phpMyAdmin database from a textfile in PH
Posted: Tue May 18, 2010 11:16 am
by mikosiko
yintong wrote:Hi miko,
but would like to ask whether the textfile should have the same column as in the database?
No necessarily, your text file could have more fields than your table, but you have to provide all the fields that are "required" in your table.
yintong wrote:
like for example, the information in my textfile is like this, where
'xxxxx' is in varchar, the space in between is by using tab & lastly
'1234' is in varchar:
'xxxxx 1234'
can the LOAD DATA INFILE Syntax still read it and save it into the database as accordingly?

Yes.
Re: Save data into phpMyAdmin database from a textfile in PH
Posted: Tue May 18, 2010 11:20 am
by mikosiko
yintong wrote:Hi miko,
Here's the answer to your doubts:
2) What kind of field delimiters your text file use?... is a csv file or something different?
Ans: I'm actually using textfile, in .txt delimiters.
.txt is a FILE extension no a "FIELD DELIMITER" ... field delimiters is(are) the character(s) that you are using to delimit your fields in your text field (you can have only one in usage in your text file), you mentioned "tab" in your previous post.
yintong wrote:
3) Is any reason why you need to do that in PHP?... asking because normally is easier and probably faster to do that using line commands or even using the SQL Script option in PhpMyAdmin if that is the tool that you know better.
Ans: The reason why i use PHP is because i need it to add data to a textfile first before clicking on a button to ask the computer to read from textfile and then save it into database again.
It can be managed with the switch "IGNORE" but you must have your table PK in the text file for it to work as it should.
Re: Save data into phpMyAdmin database from a textfile in PH
Posted: Sun May 23, 2010 10:12 pm
by yintong
Hi miko,
thanks for all your help!
it do help alot.

but i've encountered a problem with my coding:
Code: Select all
//When SAVE button is clicked
if ((isset($_POST["MM_save"])) && ($_POST["MM_save"] == "form2")) {
LOAD DATA INFILE './ezlink.txt' INTO TABLE students
FIELDS TERMINATED BY '\t'
(Name, CAN)
LINES TERMINATED BY '\r\n';
echo 'HELLO!';
}
It gives me an error,
Parse error: syntax error, unexpected T_STRING at the line LOAD DATA ......
I do not have any idea where went wrong. :S
Re: Save data into phpMyAdmin database from a textfile in PH
Posted: Mon May 24, 2010 9:21 am
by mikosiko
LOAD DATA is not a PHP sentence is a mysql sentence.
here is an small example for you to follow/adapt ... you must change it properly in case that you are not using the mysqli extension
Code: Select all
<?php
/*** Load csv File into a table example */
/*** Connection Parameters ***/
$hostname = 'localhost';
$username = 'your-username';
$password = 'your-password';
$dbname = 'your-database';
/*** Create a new mysqli object using connection parameters ***/
$link = @new mysqli($hostname, $username, $password, $dbname);
/*** Check connection ***/
if(!mysqli_connect_errno()) {
/*** If connection is suscessfull ***/
/*** Our SQL statement ***/
$sqlstmt = "LOAD DATA INFILE 'C:/xxx/yourfile.csv' INTO TABLE fwia FIELDS TERMINATED BY ','";
$sqlstmt .= " (field1,field2,field3,@dob)";
$sqlstmt .= " SET field4_dob = date_format(str_to_date(@dob,'%m/%d/%Y'),'%Y/%m/%d')";
/* Sql Statement execution */
$link->query($sqlstmt) or die($link->error);
/*** close connection ***/
$link->close();
}
else
{
/*** Unable to connect ***/
echo 'Unable to connect';
exit();
}
?>