Save data into phpMyAdmin database from a textfile in PHP

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
yintong
Forum Newbie
Posts: 5
Joined: Tue Oct 13, 2009 8:52 pm

Save data into phpMyAdmin database from a textfile in PHP

Post 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? :)
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Save data into phpMyAdmin database from a textfile in PH

Post 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?.... ( :wink: 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
yintong
Forum Newbie
Posts: 5
Joined: Tue Oct 13, 2009 8:52 pm

Re: Save data into phpMyAdmin database from a textfile in PH

Post 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? :)
yintong
Forum Newbie
Posts: 5
Joined: Tue Oct 13, 2009 8:52 pm

Re: Save data into phpMyAdmin database from a textfile in PH

Post 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!
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Save data into phpMyAdmin database from a textfile in PH

Post 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.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Save data into phpMyAdmin database from a textfile in PH

Post 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.
yintong
Forum Newbie
Posts: 5
Joined: Tue Oct 13, 2009 8:52 pm

Re: Save data into phpMyAdmin database from a textfile in PH

Post 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
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Save data into phpMyAdmin database from a textfile in PH

Post 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();
    }
?>
Post Reply