PHP Class to Export/Import MySQL database

4 Dec

Last night I was getting fed up on updating my remote database from my local. It’s a pain to login hosting control panel each time then import data.

Then I thought, why not I develop a small script for that. Finally I came up with a PHP class to import/export MYSQL Database. Let me share with you.

FEATURES

– PHP 5 and DOM XML.
– Truncate all previous data.
– Do not Drop/Alter database table
– Database Schema and Data save in XML format.

DOWNLOAD

Version 0.1.1 (New):
Download Link
svn checkout http://backupdatabase.googlecode.com/svn/trunk/

Version 0.1.0:
Download the script from box.net.

Example:

/* Include the lib file */
include('dbimexport.php');

/* Dabase configuration */
$db_config = Array
            ( 
                'dbtype'    => "MYSQL",
                'host'      => "DB_HOST_NAME",
                'database'  => "DB_NAME",
                'user'      => "DB_USER",
                'password'  => "DB_PASSWORD",
            );

// Initiate  main class
$dbimexport = new dbimexport();
$dbimexport->addValue("db_config",$db_config);

/* Download the file from browser */
$dbimexport->addValue("download_path","")
                ->addValue("download",true)
                ->addValue("file_name","MyBackUp.xml")
                ->export();


/* Save Database file in disc */
$dbimexport->addValue("download_path","auto_save.xml")
                ->addValue("download",false)
                ->export();

/* Import database */
$dbimexport->addValue("import_path","auto_save.xml")
                ->import();

Advertisements

20 Responses to “PHP Class to Export/Import MySQL database”

  1. Ehinarr December 11, 2009 at 1:29 pm #

    Hi, great work!

    I tried your example from phpclasses but it generates a MySQL error.

    Any ideas?

  2. Rubel December 11, 2009 at 1:53 pm #

    It might be for your database configuration. Download Box.net version where you will get an example in the zip file.

  3. Ehinarr December 11, 2009 at 5:50 pm #

    Hello,
    I downloaded the file. Zip. It was the same error with the sample file. You can check the architecture of my database by downloading the PrestaShop (www.prestashop.com). My intention is to turn your work in a module for it.

  4. Rubel December 11, 2009 at 6:46 pm #

    @Ehinarr
    Hey I have check the it with the database form http://www.prestashop.com it’s exporting data fine. You may get some problem during import because that database contain some NON UTF-8 Characters.

    You can simply solve it with following function in inside “dbimexport”. Here I am replacing some NON-UTF-8 characters. This is a quick function that I have added in the class and will update it in my break time.

    function text2normalize($str = "")
    {
        $arr_busca = array();
        $arr_susti = array();
        return trim(str_replace($arr_busca, $arr_susti, $str));
    }
    

    Downlaod your test copy from here.

    Send me a copy of the script if you update it. I will add that block as your contribution. 🙂

  5. ceasar December 12, 2009 at 1:30 pm #

    Nice work 🙂
    Still some problems with the UTF-8 I’m afraid

    This is a warning during import
    Warning: DOMDocument::load(): Input is not proper UTF-8, indicate encoding ! Bytes: 0xE4 0x66 0x65 0x72 in d:/apache/htdocs/db-backup/auto_save.xml, line: 4780

    On that line it says

    [PLUGIN:audio:Paulus%20Schäfer.mp3]

    Thanks for looking in

  6. Rubel December 12, 2009 at 1:37 pm #

    A good way will be, if you replace the NON-UTF-8 characters in UTF-8 during export.

    See function text2normalize($str = “”) in class files.

  7. ceasar December 12, 2009 at 1:49 pm #

    I understand. But these characters have to be place’d back during import. You cannot strip the data and replace it back. I will loose it’s functionality

  8. Rubel December 12, 2009 at 2:36 pm #

    We should not strip the characters. we have to get proper replacement or encoding.

    This article will hep you.
    http://www.phpwact.org/php/i18n/utf-8

  9. Junal December 15, 2009 at 6:16 am #

    Rubel bhai,

    Just went through the codes, cool stuff…it could be a good example for “Good Coding Practice” + OOP example with PHP 🙂

  10. Rubel December 15, 2009 at 6:38 am #

    😉

  11. tommy December 17, 2009 at 5:18 pm #

    Hi guys,

    i´ve installed the script with my own mysql database…export to browser is ok, but import and save to disk gives-me an error:

    Notice: Undefined variable: tmp_head in C:\config\include\dbimexport.php on line 305

    whats wrong???
    thanks in advance.

  12. Rubel December 17, 2009 at 5:46 pm #

    Hi tommy,
    Set valid path in following fields in example.php and try “Save in disc” link.

    $dbimexport->file_name = "";
    $dbimexport->import_path = "";
    

    See if it create the export file (auto_save.xml) correctly. It will create a valid Xml file.

    Then try to import.

  13. tommy December 17, 2009 at 6:24 pm #

    Hi Rubel,

    thanks for your help…the save to disk now works ( i think was the full path in windows..now is working)..
    but when import the same error : Undefined variable: tmp_head …
    i have php 5.2 runs on apache 2.2.3 and i have this php ini configs:

    magic_quotes_gpc = On

    register_globals = On

    could be that??? thanks anyway

  14. tommy December 17, 2009 at 6:32 pm #

    Hi Rubel, me again 🙂

    sorry, the script works…..i have php with visible errors in php.ini, instead it works….the ddatabase is imported,,,,,don´t see only qhy it gives-me that error…..but it works…thanks

  15. Rubel December 17, 2009 at 7:44 pm #

    😀 😀

  16. Rubel January 24, 2010 at 1:42 pm #

    Hi,
    I have update the code and NON-UTF8 character problem is solved

  17. Shaon February 25, 2010 at 7:48 am #

    Actually I am looking for this type of work for last few months. Thanks Rubel bhai for your great work. I am going with it today.

  18. dr.emi June 29, 2010 at 4:16 pm #

    great script! it was help me to finish my web application.

    thank you my bro!

  19. jaldesa November 10, 2011 at 7:00 am #

    Hi, thanks for the script. I have question,what if I wanted to Export the database in mysql from another website and import it to another website.Any suggestions?
    Thanks.

  20. Cecy BooM September 28, 2012 at 7:43 pm #

    Thanks, very useful! I would like to ask you how can i export just a table? =). Anyway, thanks a lot!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: