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');