Skip to content
Advertisement

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

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.

Advertisement

Answer

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');
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement