Php Reading From a Delimited Txt File

MySQL - Database Export

The simplest way of exporting a table data into a text file is by using the SELECT...INTO OUTFILE statement that exports a query result directly into a file on the server host.

Exporting Information with the SELECT ... INTO OUTFILE Statement

The syntax for this statement combines a regular SELECT command with INTO OUTFILE filename at the cease. The default output format is the aforementioned equally it is for the LOAD Data control. And then, the following statement exports the tutorials_tbl table into /tmp/tutorials.txt equally a tab-delimited, linefeed-terminated file.

mysql> SELECT * FROM tutorials_tbl     -> INTO OUTFILE '/tmp/tutorials.txt';

You lot can change the output format using various options to signal how to quote and delimit columns and records. To consign the tutorial_tbl table in a CSV format with CRLF-terminated lines, employ the post-obit lawmaking.

mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/tutorials.txt'    -> FIELDS TERMINATED Past ',' ENCLOSED BY '"'    -> LINES TERMINATED BY '\r\n';

The SELECT ... INTO OUTFILE has the post-obit properties −

The output file is created directly past the MySQL server, so the filename should signal where y'all desire the file to be written on the server host. There is no LOCAL version of the statement analogous to the LOCAL version of LOAD DATA.
You must take the MySQL FILE privilege to execute the SELECT ... INTO statement.
The output file must not already exist. This prevents MySQL from clobbering files that may be important.
You should accept a login business relationship on the server host or some fashion to retrieve the file from that host. Otherwise, the SELECT ... INTO OUTFILE control will virtually likely be of no value to yous.
Under UNIX, the file is created globe readable and is owned by the MySQL server. This means that although you will be able to read the file, y'all may not exist able to delete it.

Exporting Tables as Raw Data

The mysqldump program is used to re-create or back up tables and databases. It tin can write the table output either as a Raw Datafile or as a set of INSERT statements that recreate the records in the table.

To dump a table as a datafile, you must specify a --tab pick that indicates the directory, where you want the MySQL server to write the file.

For example, to dump the tutorials_tbl table from the TUTORIALS database to a file in the /tmp directory, employ a command every bit shown below.

$ mysqldump -u root -p --no-create-info \    --tab=/tmp tutorials tutorials_tbl password ******

Exporting Table Contents or Definitions in SQL Format

To export a tabular array in SQL format to a file, use the command shown below.

$ mysqldump -u root -p TUTORIALS tutorials_tbl > dump.txt password ******

This will a create file having content as shown below.

-- MySQL dump viii.23 -- -- Host: localhost    Database: TUTORIALS --------------------------------------------------------- -- Server version       3.23.58  -- -- Tabular array construction for table `tutorials_tbl` --  CREATE TABLE tutorials_tbl (    tutorial_id int(11) NOT Cipher auto_increment,    tutorial_title varchar(100) Non Nil default '',    tutorial_author varchar(twoscore) NOT Naught default '',    submission_date appointment default NULL,    Principal KEY  (tutorial_id),    UNIQUE KEY AUTHOR_INDEX (tutorial_author) ) TYPE = MyISAM;  -- -- Dumping data for tabular array `tutorials_tbl` --  INSERT INTO tutorials_tbl     VALUES (one,'Learn PHP','John Poul','2007-05-24'); INSERT INTO tutorials_tbl     VALUES (ii,'Learn MySQL','Abdul Southward','2007-05-24'); INSERT INTO tutorials_tbl     VALUES (3,'JAVA Tutorial','Sanjay','2007-05-06');

To dump multiple tables, proper noun them all followed past the database name argument. To dump an entire database, don't name any tables subsequently the database equally shown in the following code cake.

$ mysqldump -u root -p TUTORIALS > database_dump.txt countersign ******

To dorsum up all the databases available on your host, use the following lawmaking.

$ mysqldump -u root -p --all-databases > database_dump.txt password ******

The --all-databases option is available in the MySQL 3.23.12 version. This method tin be used to implement a database backup strategy.

Copying Tables or Databases to Another Host

If you desire to copy tables or databases from one MySQL server to another, so utilise the mysqldump with database name and table name.

Run the post-obit command at the source host. This will dump the complete database into dump.txt file.

$ mysqldump -u root -p database_name table_name > dump.txt countersign *****

You lot can copy complete database without using a detail table name as explained higher up.

Now, ftp dump.txt file on another host and use the post-obit command. Before running this control, make sure you have created database_name on destination server.

$ mysql -u root -p database_name < dump.txt password *****

Another way to achieve this without using an intermediary file is to send the output of the mysqldump directly over the network to the remote MySQL server. If you can connect to both the servers from the host where the source database resides, use the following command (Make sure you have access on both the servers).

$ mysqldump -u root -p database_name \    | mysql -h other-host.com database_name

In mysqldump, half of the command connects to the local server and writes the dump output to the pipe. The remaining half of the command connects to the remote MySQL server on the other-host.com. It reads the pipe for input and sends each statement to the other-host.com server.