Export data from database to sql file with PHP

Question

I need to export data (only the data, not the tables nor database) from the database to a SQL file and save it. How to write a PHP function that does that? I want to call this function in specific circumstances when running specific queries (updating a blog post, for example).

Something like:

/*
* Get the data from the database (already specified) and export it to a SQL file
*
* @param  string $dir Where to store the SQL file. Must be a full path.
* @return void
*/
function export_database_data(string $dir) {
  // some code that gets the data and store it in $data
  // don't know what to do here

  // create/update the file
  file_put_contents("$dir/data_backup.sql", $data);
}

Why this?

(You don't need to read the following to help me solve the problem. I'm including this because I'm sure if don't include this, someone will ask it).

I want to do that because I want to have a backup of the data (only the data; the tables do not matter because I already know how to handle them for backup).

I'm doing this because I will use the PHP function exec to run git commands and commit the change in the SQL file. The end goal is to keep track of the data in the database, which won't be updated often. I need the data in a SQL file and I aim to use git because it is easy to access and to collaborate with others (I could not think a better solution, and creating my own tracking system would require a lot of work and time).

Why not use `mysqldump -u [user] -p[pass] --no-create-info mydb > mydb.sql`? Check more [here](https://stackoverflow.com/questions/5109993/mysqldump-data-only) — ReynierPM, Aug 23 '19 at 19:51
ReynierPm Can I run this command with PHP `exec` function? Is "mydb" the name of the database? — Student of Science, Aug 23 '19 at 19:53
Fantastic! I'll try it out! You should post it as the answer! — Student of Science, Aug 23 '19 at 19:54
Yes, you can run it using `exec` see [here](https://stackoverflow.com/questions/6750531/using-a-php-file-to-generate-a-mysql-dump) — ReynierPM, Aug 23 '19 at 19:54
Much better. https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html — ficuscr, Aug 23 '19 at 19:55

score 3 · Accepted Answer · answered Aug 23 '19 at 19:56

3

You can run a mysqldump command using PHP exec function to only export data. Ex:

exec('mysqldump -u [user] -p[pass] --no-create-info mydb > mydb.sql');

More info:

answered Aug 23 '19 at 19:56

ReynierPM

15,161
39
158
314

I'll try and let you know how that goes – Student of Science Aug 23 '19 at 20:02
I ran your code and, altough I got no error, it did not create the file. Can you help me again? Where I can post my code (here in the comments or on the post) ? – Student of Science Aug 23 '19 at 20:48
1

I figured out what I was doing wrong and your command worked !! Thanks!! – Student of Science Aug 23 '19 at 21:40

Export data from database to sql file with PHP

1 Answers1