{"id":46,"date":"2006-08-19T18:59:49","date_gmt":"2006-08-19T18:59:49","guid":{"rendered":"http:\/\/www.dereckson.be\/blog\/2006\/08\/19\/php-mysql-quickly-import-and-export-data\/"},"modified":"2006-08-19T18:59:49","modified_gmt":"2006-08-19T18:59:49","slug":"php-mysql-quickly-import-and-export-data","status":"publish","type":"post","link":"https:\/\/www.dereckson.be\/blog\/2006\/08\/19\/php-mysql-quickly-import-and-export-data\/","title":{"rendered":"PHP\/MySQL :: Quickly import and export data"},"content":{"rendered":"

To import or export MySQL dumps, we can use mysql and mysqldump commands. Let’s call them from PHP.
\n
\n <?php
<\/span> \/\/ Import and export MySQL dump in PHP, using mysql client command line tools
\n \/\/ (c) 2006, S\u00e9bastien Santoro aka Dereckson, released in public domain<\/p>\n

    <\/span>\/\/Connections info (of course we'll use config.php or something variables, this is a sample)
    <\/span>$sql<\/span>[<\/span>'host'<\/span>] = <\/span>\"localhost\"<\/span>;
    <\/span>$sql<\/span>[<\/span>'login'<\/span>] = <\/span>\"root\"<\/span>; 
    <\/span>$sql<\/span>[<\/span>'pass'<\/span>] = <\/span>\"\"<\/span>;
    <\/span>$sql<\/span>[<\/span>'database'<\/span>] = <\/span>\"MySiteDatabase\"<\/span>; <\/p>\n

    <\/span>\/\/Import a dump from a file
    <\/span>function <\/span>import_dump <\/span>(<\/span>string $pathFile<\/span>) {
        global <\/span>$sql<\/span>;
        <\/span>system<\/span>(<\/span>\"mysql -h$sql[host] -u$sql[login] -p$sql[pass] $sql[database] < \" <\/span>+ <\/span>$pathfile<\/span>);
    }
    
    <\/span>\/\/Import a dump from a string
    <\/span>function <\/span>import_dump_string <\/span>(<\/span>string $dump<\/span>) {
        global <\/span>$sql<\/span>;
        <\/span>$handle <\/span>= <\/span>popen<\/span>(<\/span>\"mysql -h$sql[host] -u$sql[login] -p$sql[pass] $sql[database]\"<\/span>, <\/span>\"w\"<\/span>);
        if (<\/span>fwrite<\/span>(<\/span>$handle<\/span>, <\/span>$dump<\/span>) === <\/span>FALSE<\/span>) {
            <\/span>\/\/We've an error (not a sql error in our dump, but the process mysql isn't correctly opened)
        <\/span>}
        <\/span>pclose<\/span>(<\/span>$handle<\/span>);
    }
    
    <\/span>\/\/Export our database and returns dump filename
    \/\/Optionally, we can specify one or more tables to export (if not specified, all the database will be dumped)
    <\/span>function <\/span>export_dump <\/span>(<\/span>$tables <\/span>= <\/span>''<\/span>) {
      global <\/span>$sql<\/span>;
      <\/span>$filename <\/span>= <\/span>$sql<\/span>[<\/span>'database'<\/span>] . <\/span>'.' <\/span>. <\/span>time<\/span>() . <\/span>'.sql'<\/span>;
      <\/span>system<\/span>(<\/span>\"mysqldump -h$sql[host] -u$sql[login] -p$sql[pass] $sql[database] $tables > $filename\"<\/span>);
      return <\/span>$filename<\/span>;
    }
    
<\/span>?>
<\/span>
Dump the database and provide download link : <\/p>\n

<?php
  <\/span>\/\/Dump our database in whole in a file :
  <\/span>$dumpFile <\/span>= <\/span>export_dump<\/span>();
  <\/span>\/\/Link
    <\/span>echo <\/span>\"<a href='$dumpFile'>Download the backup of the $sql[database] database<\/a>\"<\/span>;
<\/span>?>
<\/span>
Execute posted dump : <\/p>\n

<?php
  <\/span>\/\/Dump our database in whole in a file :
  <\/span>import_dump_string<\/span>(<\/span>$_POST<\/span>[<\/span>sqldump<\/span>]);
<\/span>?><\/span> <\/span> <\/code> <\/p>\n

Of course, on Windows, you’ve to add your MySQL server bin directory in the path or replace system(“mysql …”) by system(“d:WebServerMySQLbinmysql.exe …”);<\/p>\n","protected":false},"excerpt":{"rendered":"

To import or export MySQL dumps, we can use mysql and mysqldump commands. Let’s call them from PHP.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[17],"class_list":["post-46","post","type-post","status-publish","format-standard","hentry","tag-php"],"_links":{"self":[{"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/posts\/46","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/comments?post=46"}],"version-history":[{"count":0,"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/posts\/46\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/media?parent=46"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/categories?post=46"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dereckson.be\/blog\/wp-json\/wp\/v2\/tags?post=46"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}