[MySQL Tutorial 12] Importing and Exporting Database

0

In the previous MySQL tutorial on Joining Tables, you learnt how to join the two tables. In this tutorial, you will learn how to export database into our system and how to import the data from file present in our system to the MySQL database.

To export from MySQL database into our system, “INTO OUTFILE” keyword is used. The syntax for this is:

Select * from table_name into outfile “file_path”;


Where file path is the path to the location in your system where you want to place the file.

For example,

In the MySQL Tutorial 11 we used a table “office1” with the columns “empid”,”name” and “salary”. To

Select * from office1 into outfile "c:/wamp/www/file.txt”;


Here, data from “office1” table is stored in “file.txt”.

 

Using PHP:

<?php
$link=mysql_connect('localhost','root','your_password');
mysql_select_db('mysql');
echo "connected to mysql<br>";
$q=mysql_query('select * from office1 into outfile "c:/wamp/www/file.txt"');
if($q)
{
                echo "file export successful";
}
else
{
                echo "unsuccessful";
}
?>

 

The output is:

Check the file in “c:/wamp/www/file.txt” location if it exists or not.

To import data from a file into a table within MySQL database, the syntax is:

load data local infile "file" into table table_name;

Example,

We have an empty table named table3 with the columns as “empid”, ”name” and “salary” and the file “file.txt” we exported into our system as described above. By using “LOAD DATA LOCAL INFILE”, you can load the data from that “file.txt” into table3 as follows:

load data local infile “c:/wamp/www/file.txt” into table table3′);

Using PHP:

<?php
$link=mysql_connect('localhost','root','your_password');
mysql_select_db('mysql');
echo "connected to mysql<br>";
$q=mysql_query('load data local infile "c:/wamp/www/file.txt" into table table3');
if($q)
{
                echo "file import successful";
}
else
{
                echo "unsuccessful";
}
?>

 

The output is:

If you have any queries, please leave a comment below.

Share.

Leave A Reply