Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > PHP/MySQL > PHP Databases
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
PHP Databases Using PHP in conjunction with databases. PHP questions not specific to databases should be directed to one of the other PHP forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the PHP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 4th, 2003, 10:33 PM
Registered User
 
Join Date: Dec 2003
Location: Tainan, -, Taiwan.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Exporting mysql to other formats

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

Graham.

Reply With Quote
  #2 (permalink)  
Old December 5th, 2003, 01:08 AM
richard.york's Avatar
Wrox Author
Points: 5,506, Level: 31
Points: 5,506, Level: 31 Points: 5,506, Level: 31 Points: 5,506, Level: 31
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Camby, IN, USA.
Posts: 1,706
Thanks: 0
Thanked 6 Times in 6 Posts
Default

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
:::::::::::::::::::::::::::::::::
Reply With Quote
  #3 (permalink)  
Old December 5th, 2003, 12:53 PM
Friend of Wrox
Points: 2,570, Level: 21
Points: 2,570, Level: 21 Points: 2,570, Level: 21 Points: 2,570, Level: 21
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: San Diego, CA, USA
Posts: 836
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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/
Reply With Quote
  #4 (permalink)  
Old December 5th, 2003, 01:28 PM
richard.york's Avatar
Wrox Author
Points: 5,506, Level: 31
Points: 5,506, Level: 31 Points: 5,506, Level: 31 Points: 5,506, Level: 31
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Camby, IN, USA.
Posts: 1,706
Thanks: 0
Thanked 6 Times in 6 Posts
Default

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
:::::::::::::::::::::::::::::::::
Reply With Quote
  #5 (permalink)  
Old December 5th, 2003, 01:34 PM
Friend of Wrox
Points: 2,570, Level: 21
Points: 2,570, Level: 21 Points: 2,570, Level: 21 Points: 2,570, Level: 21
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: San Diego, CA, USA
Posts: 836
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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/
Reply With Quote
  #6 (permalink)  
Old December 5th, 2003, 01:48 PM
richard.york's Avatar
Wrox Author
Points: 5,506, Level: 31
Points: 5,506, Level: 31 Points: 5,506, Level: 31 Points: 5,506, Level: 31
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Camby, IN, USA.
Posts: 1,706
Thanks: 0
Thanked 6 Times in 6 Posts
Default

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
:::::::::::::::::::::::::::::::::
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
difference between these three formats rajesh_css XSLT 5 October 22nd, 2008 03:26 AM
Date Formats carumuga SQL Server 2000 4 January 2nd, 2007 07:20 PM
Exporting owc chart to gif or jpeg formats sriram s General .NET 2 December 27th, 2004 12:39 AM
About Message formats abdul_owiusa C# 3 August 10th, 2004 12:46 PM
exporting mysql databases Endee PHP Databases 2 August 1st, 2003 02:15 PM



All times are GMT -4. The time now is 09:50 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.