Exporting mysql to other formats

fold1234 · December 4th, 2003, 10:33 PM

Does anyone know if it is possible to export mysql databases to other database formats using PHP?

Graham.

richard.york · December 5th, 2003, 01:08 AM

If you're resourceful enough you can design a script to export mysql data to virtually *any* format. So is it possible? Probably. You're going to have to be more specific to find out if anyone's already done what you'd like to do.

phpmyadmin is designed to interface with mysql using PHP via a web browser. Currently that software supports exporting MySQL data to SQL, LaTeX, CSV for Ms Excel data, CSV data and XML. XML, CSV and SQL are pretty simple to interpret and it wouldn't be very difficult to write a script that can parse information contained in one of those formats.

Take for instance this example of importing CSV data from Microsoft Outlook 2000.. This one happens to be a function I'm working on to import the Contacts data out of Outlook to insert into an address book for use in a web-based email program I designed.

It takes an uploaded file calls on a built in PHP CSV parsing function and builds the insert values part of an SQL query string. Granted this is only part of the process.. it doesn't show the upload form and I haven't completed the query that inserts the data into the address book table, but you get the idea.

Code:

// snip snip class declaration, constructor, etc.

        function import_from_outlook2000_csv()
        {        
            if (isset($_FILES["userfile"]["tmp_name"]) && file_exists($_FILES["userfile"]["tmp_name"]))
            {
                $file = fopen($_FILES["userfile"]["tmp_name"], "r");

                fgetcsv($file, 1000); // throw away header line (Nik's suggestion).

                for($i = 0; $row = fgetcsv($file, 1000); $i++)
                {

                    <s># Starts at 1 to avoid headers included in CSV file</s>

                    <s>if ($i > 0)</s>
                    <s>{</s>
                        $contact["title"]                    = $row[0];
                        $contact["first_name"]               = $row[1];
                        $contact["middle_name"]              = $row[2];
                        $contact["last_name"]                = $row[3];
                        $contact["suffix"]                   = $row[4];
                        $contact["company"]                  = $row[5];
                        $contact["department"]               = $row[6];
                        $contact["job_title"]                = $row[7];
                        $contact["business_street"]          = $row[8];
                        $contact["business_street2"]         = $row[9];
                        $contact["business_street3"]         = $row[10];
                        $contact["business_city"]            = $row[11];
                        $contact["business_state"]           = $row[12];
                        $contact["business_post"]            = $row[13];
                        $contact["business_country"]         = $row[14];
                        $contact["home_street"]              = $row[15];
                        $contact["home_street2"]             = $row[16];
                        $contact["home_street3"]             = $row[17];
                        $contact["home_city"]                = $row[18];
                        $contact["home_state"]               = $row[19];
                        $contact["home_post"]                = $row[20];
                        $contact["home_country"]             = $row[21];
                        $contact["other_street"]             = $row[22];
                        $contact["other_street2"]            = $row[23];
                        $contact["other_street3"]            = $row[24];
                        $contact["other_city"]               = $row[25];
                        $contact["other_state"]              = $row[26];
                        $contact["other_post"]               = $row[27];
                        $contact["other_country"]            = $row[28];
                        $contact["assistants_phone"]         = $row[29];
                        $contact["business_fax"]             = $row[30];
                        $contact["business_phone"]           = $row[31];
                        $contact["business_phone2"]          = $row[32];
                        $contact["callback"]                 = $row[33];
                        $contact["car_phone"]                = $row[34];
                        $contact["company_main_phone"]       = $row[35];
                        $contact["home_fax"]                 = $row[36];
                        $contact["home_phone"]               = $row[37];
                        $contact["home_phone2"]              = $row[38];
                        $contact["isdn"]                     = $row[39];
                        $contact["mobile_phone"]             = $row[40];
                        $contact["other_fax"]                = $row[41];
                        $contact["other_phone"]              = $row[42];
                        $contact["pager"]                    = $row[43];
                        $contact["primary_phone"]            = $row[44];
                        $contact["radio_phone"]              = $row[45];
                        $contact["ttytdd_phone"]             = $row[46];
                        $contact["telex"]                    = $row[47];
                        $contact["account"]                  = $row[48];
                        $contact["anniversary"]              = $row[49];
                        $contact["assistants_name"]          = $row[50];
                        $contact["billing_information"]      = $row[51];
                        $contact["categories"]               = $row[52];
                        $contact["children"]                 = $row[53];
                        $contact["directory_server"]         = $row[54];
                        $contact["email_address"]            = $row[55];
                        $contact["email_type"]               = $row[56];
                        $contact["email_display_name"]       = $row[57];
                        $contact["email_address2"]           = $row[58];
                        $contact["email_type2"]              = $row[59];
                        $contact["email_display_name2"]      = $row[60];
                        $contact["email_address3"]           = $row[61];
                        $contact["email_type3"]              = $row[62];
                        $contact["email_display_name3"]      = $row[63];
                        $contact["gender"]                   = $row[64];
                        $contact["government_id_number"]     = $row[65];
                        $contact["hobby"]                    = $row[66];
                        $contact["initials"]                 = $row[67];
                        $contact["internet_free_busy"]       = $row[68];
                        $contact["keywords"]                 = $row[69];
                        $contact["language"]                 = $row[70];
                        $contact["managers_name"]            = $row[71];
                        $contact["mileage"]                  = $row[72];
                        $contact["notes"]                    = $row[73];
                        $contact["office_location"]          = $row[74];
                        $contact["organizational_id_number"] = $row[75];
                        $contact["po_box"]                   = $row[76];
                        $contact["priority"]                 = $row[77];
                        $contact["private"]                  = $row[78];
                        $contact["profession"]               = $row[79];
                        $contact["referred_by"]              = $row[80];
                        $contact["sensitivity"]              = $row[81];
                        $contact["spouse"]                   = $row[82];
                        $contact["user_1"]                   = $row[83];
                        $contact["user_2"]                   = $row[84];
                        $contact["user_3"]                   = $row[85];
                        $contact["user_4"]                   = $row[86];
                        $contact["web_page"]                 = $row[87];

                        $insert[$i] = (string) "null, {$_SESSION["user_id"]},";

                        $n = 0;

                        foreach($contact as $field => $data)
                        {

                            # Use the data not the indice!
                            # And addslashes to avoid database errors from quotes

                            $insert[$i] .= "'".addslashes($data)."'";   

                            if ($n < 87)    $insert[$i] .= ",";   # (added missing indice!)

                            $n++;
                        }    

                        # Doing something with the insert variable

                        $result = $this->db->query("INSERT INTO `contacts` VALUES ({$insert[$i]})");

                    <s>}</s>
                }

                fclose($file);
                unset($file);
                unlink($_FILES["userfile"]["tmp_name"]);        

            } 

            else

                $this->library->result_response("Error: failed opening uploaded file.");        

        }

// snip snip class closing

Probably more information than you wanted, but it gives you an idea of how ambiguous a CSV file is.

You can download the latest version of phpmyadmin from http://www.phpmyadmin.net. There a few configuration changes that you need to make in at least one of the files.. things like setting up the database user, password, authentication scheme. Which should all be explained in the documentation.

: )
Rich

:::::::::::::::::::::::::::::::::
Smiling Souls
http://www.smilingsouls.net
:::::::::::::::::::::::::::::::::

nikolai · December 5th, 2003, 12:53 PM

Hey Rich? I was just reading through that code, and something didn't make sense to me. You have the lines:

Quote:

quote:
# Starts at 1 to avoid headers included in CSV file
if ($i == 1)

Shouldn't this be "if ($i > 0)"? The way it's written, you'll iterate over ALL the lines in the CSV file but only process your first one.

Also, I don't understand where $insert comes in to play... Where is it used, and what does it do? Is it an array or a string? It's used in both contexts:

Quote:

quote:
  $n = 0;
  foreach($contact as $field => $data)
  {
      $insert[$i] .= "'".$data."'"; Array context!

      if ($n < 87) $insert .= ","; String context!
      $n++;
   }

Anyway, the simplest way to export from MySQL to another database is just to dump the table. You don't need PHP to do it, either -- mysql can handle it on it's own. mysqldump is an executable file (mysqldump.exe on windows) that dumps the structure and contents of any database or table in SQL format. One major roadblock here is that MySQL's data types and function names don't always match what other database engines might support.

For this reason, there are many conversion tools or instructions available between most major databases:

SQL Server: http://www.microsoft.com/technet/pro...rate/mysql.asp
Postgres: http://archives.postgresql.org/pgsql...1/msg00211.php

etc. Search on google for "mysql to <target RDBMS>" for more examples.

HTH!

Take care,

Nik
http://www.bigaction.org/

richard.york · December 5th, 2003, 01:28 PM

Quote:

quote:
Shouldn't this be "if ($i > 0)"? The way it's written, you'll iterate over ALL the lines in the CSV file but only process your first one.

Also, I don't understand where $insert comes in to play... Where is it used, and what does it do? Is it an array or a string? It's used in both contexts:

lol.. yeah that doesn't work does it! I guess that's what happens when I use an example I'm still working on and haven't debugged. Thanks for pointing that out!

: )
Rich

:::::::::::::::::::::::::::::::::
Smiling Souls
http://www.smilingsouls.net
:::::::::::::::::::::::::::::::::

nikolai · December 5th, 2003, 01:34 PM

Here's another suggestion. It makes for slightly sloppier code, but it's much more efficient.

function import_from_outlook2000_csv()
{
    if (isset($_FILES["userfile"]["tmp_name"]) && file_exists($_FILES["userfile"]["tmp_name"]))
    {
        $file = fopen($_FILES["userfile"]["tmp_name"], "r");

        fgetcsv($file, 1000); // throw away header line.

        for($i = 0; $row = fgetcsv($file, 1000); $i++)
        {
            $contact["title"] = $row[0];
            $contact["first_name"] = $row[1];
            $contact["middle_name"] = $row[2];
            // etc...

See what we do? We call fgetcsv() outside of the loop once to discard your header line, which frees us from the burden of checking if($i > 0) every iteration of the loop. That condition is guaranteed to be met every single iteration of the loop but the first. If you have a csv file of a thousand lines, then you're executing an unnecessary if() conditional 999 times.

HTH!

Take care,

Nik
http://www.bigaction.org/

richard.york · December 5th, 2003, 01:48 PM

Quote:

quote:
then you're executing an unnecessary if() conditional 999 times.

That makes more sense too. For that matter the whole $contact variable could go as well... which is really up to the programmer, on one hand it isn't neccessary and just creates extra execution time but on the other it clarifies what each comma delimited value represents (which could be done in comments as well) and still yet on the third hand if I needed to build an UPDATE query instead of an INSERT then each field is already there as an indice in the array, assuming I've been very efficient and named each MySQL field the same as what appears in the $contact array.

Another error I should point out is I also originally had the indice instead of the data getting put into that query.

I went back and edited my code for clarification..

: )
Rich

:::::::::::::::::::::::::::::::::
Smiling Souls
http://www.smilingsouls.net
:::::::::::::::::::::::::::::::::