How to export some rows of a MySQL table from a WHERE clause?

13
votes

How to export some rows of a MySQL table with where clause from a PHP script?

I have a MySQL say test and I want to create a importable .sql file for rows where id are between 10 and 100, using PHP script.

I want to create a sql file say test.sql which can be imported to MySQL database.

Mycode:

$con=mysqli_connect("localhost", "root","","mydatabase");

$tableName  = 'test';
$backupFile = '/opt/lampp/htdocs/practices/phpTest/test.sql';
$query      = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName WHERE id BETWEEN 10 AND 500";

$result = mysqli_query($con,$query);

This creates a test.sql file, but when I try to import, it gives error #1064.
My script only creates a file with rows with columns name and table sturcute or insert query.

4
phpmysqlphpmyadmin
Is it absolutely necessary to do that from php? Is it a possibility to to use mysqldump? – peterm
Unless you want to build the insert statements yourself I don't believe this is possible. – shapeshifter
You could exec out to mysqldump though – shapeshifter

4 Answers

16
votes

As mentioned in the comments you can use mysqldump the following way.

mysqldump --user=... --password=... --host=... DB_NAME --where=<YOUR CLAUSE> > /path/to/output/file.sql

If you want this to be in your php file you can do the following 

exec('mysqldump --user=... --password=... --host=... DB_NAME --where=<YOUR CLAUSE> > /path/to/output/file.sql');
11
votes

In very simply way go to your phpMyAdmin select you database whose particular rows you want to export click on "SQL" (To Run SQL query/queries on database) Write sql query and execute it Like select * from test table limit 500 now what ever result come Just at the bottom see "Query results operations" just click on Export

All done :-)

1
votes

If you have exported data using OUTFILE then you have to import it using INFILE COMMAND

$con=mysqli_connect("localhost", "root","","mydatabase");

$tableName  = 'test';
$backupFile = '/opt/lampp/htdocs/practices/phpTest/test.sql';
$query      = "LOAD DATA INFILE '$backupFile' INTO TABLE $tableName";

$result = mysqli_query($con,$query);

OR you can make csv file using 

 $con=mysqli_connect("localhost", "root","","mydatabase");
 $tableName  = 'test';
 $backupFile = '/opt/lampp/htdocs/practices/phpTest/test.sql';
 $query      = 'SELECT * INTO OUTFILE '."'$backupFile'".'
                FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '.'\'"\'
                LINES TERMINATED BY \'\n\'
                FROM '.$tableName.' WHERE id BETWEEN 10 AND 500';

 $result = mysqli_query($con,$query);

and then import this file.

Hope this works...

1
votes

Mysql Shell command 

mysqldump -u user -p password -h host Database Table --where="Condition"> /path/exportFile.sql

Example

mysqldump -u user -p 123456 -h host Database Student --where="age > 10"> /path/exportFile.sql