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

Question

How to export certain rows of a MySQL table using where clause from a PHP script?

I have a MySQL say test and I want to use a PHP script to create an importable .sql file for rows with ids between 10 and 100.

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

My code:

$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 simply creates a file with rows containing column names and table structure or insert query.

Answer 1

Go to your phpMyAdmin in a very easy way Select the database whose specific rows you want to export Click SQL (run a SQL query on the database) Write sql query and execute it Just like select * from test table limit 500, what will be the result now? Just see "Query Result Operation" at the bottom Just click Export

Completed:-)

Answer 2

As mentioned in the comments, you can use mysqldump in the following ways.

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

If you want it to appear in your php file you can do the following

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