How to export MYSQL to XML with mysqldump –xml

@_LOBO_Jun 22.2005

Hi,

~~[B]~~1)[/B] I want to export a MYSQL table to XML. How I can achieve this?

~~[B]~~2)[/B] I read that this can be achieve with mysqldump –xml but how I should use it with PHP ?

The goal of this is get the data throught ~~[url]~~http://www.serence.com[/url] KLIPFolio

Thank you on advance.
_Lobo_

to post a comment

PHP

20 Comments(s) _↴

@ShrineDesignsJun 22.2005 — #mysqldump is pretty much the only thing that will work, if each your tables has like 1000's of rows, scripts like phpmyadmin will not be capable of exporting large amounts of data

@_LOBO_authorJun 23.2005 — #Thank your for stop here ?

So the only hy to export all is mysqldump but how I should use it ? Can you make a samll sample for me creating a XML file with php ? or can you point me to a tutorial ?

Thank you on advance.

_Lobo_

mysqldump is pretty much the only thing that will work, if each your tables has like 1000's of rows, scripts like phpmyadmin will not be capable of exporting large amounts of data[/QUOTE]

@ShrineDesignsJun 23.2005 — #i don't know the exacts about running mysql-dump from the command-line, this should offer some assistance

http://dev.mysql.com/doc/mysql/en/index.html

@_LOBO_authorJun 23.2005 — #ok this si wath I have:

But im getting this error!

[B]Notice: Backup failed: 1 in e:wampwwwFA06admin4xmlxml.php on line 38[/B]

[B]line 38[/B]
[code=php]trigger_error ('Backup failed: '.$error);[/code]

[B]all my code:[/B]

[code=php]<?php
 $dbUser = 'root';               // db User 
 $dbPass = 'root';               // db User Password 
 $dbName = 'newland_tours';            // db name 
 $dest   = '/'; // Path to directory 
 
 class MySQLDump {
 /**
 * The backup command to execute
 * @private
 * @var string
 */
 var $cmd;
 
   /**
  * MySQLDump constructor
 * @param string dbUser (MySQL User Name)
 * @param string dbPass (MySQL User Password)
 * @param string dbName (Database to select)
 * @param string dest (Full destination directory path for backup file)
 * @access public
 */
 function MySQLDump ($dbUser,$dbPass,$dbName,$dest) {
 $fname = $dbName.'.xml'; 
 $this->cmd='mysqldump -X -u'.$dbUser.' -p'.$dbPass.' '.$dbName.
 ' >'.$dest.'/'.$fname;
 }
 
 
   /**
  * Runs the constructed command
  * @access public
  * @return void
  */
   function backup () {
   system ($this->cmd, $error);
   if($error)
   trigger_error ('Backup failed: '.$error);
   }
 }
 
 // Instantiate MySQLDump 
 $mysqlDump = new MySQLDump($dbUser, $dbPass, $dbName, $dest); 
 
 // Perform the backup 
 $mysqlDump->backup(); 
 
 ?>[/code]

[B]MySQLDump[/B]
[list]

[*] [B]There are three general ways to invoke mysqldump: [/B]

shell> mysqldump [options] db_name [tables]

shell> mysqldump [options] --databases DB1 [DB2 DB3...]

shell> mysqldump [options] --all-databases

[*] [B]--xml, -X [/B]

Write dump output as well-formed XML.

[*] [B]--password[=password], -p[password] [/B]

The password to use when connecting to the server. If you use the short option form (-p), you cannot have a space between the option and the password. If you omit the password value following the --password or -p option on the command line, you are prompted for one.

[*] [B]--user=user_name, -u user_name[/B]

The MySQL username to use when connecting to the server.

[/list]

@chrysJun 23.2005 — #Have you tried any debugging?

Echo out $this->cmd before you use the system call to it. Stuff like that, make sure everything is right.

@_LOBO_authorJun 23.2005 — #Have you tried any debugging?

Echo out $this->cmd before you use the system call to it. Stuff like that, make sure everything is right.[/QUOTE]

Hi Chris,

Can you show me how :o (Im very new with PHP).

@ShrineDesignsJun 23.2005 — #hmm... you should use empty() to check if there is an error actually occured

[code=php]    function backup()
 {
 system ($this->cmd, $error);
 
   if(!empty($error))
   {
   trigger_error ('Backup failed: '.$error);
   }
  }[/code]

@_LOBO_authorJun 23.2005 — #Ok now Im getting this:

[B]Parse error: syntax error, unexpected T_VARIABLE, expecting T_FUNCTION in e:wampwwwFA06admin4xmlxml.php on line 46[/B]

line 46: [B]$mysqlDump = new MySQLDump($dbUser, $dbPass, $dbName, $dest); [/B]

@ShrineDesignsJun 23.2005 — #i tested this and it works

[code=php]<?php
 class mysql_dump
 {
 var $cmd = '';
 
  function mysql_dump($user, $pass, $name, $path)
  {
   $this->cmd = "mysqldump -q -X -u {$user} -p{$pass} {$name} -r {$path}{$name}.xml";
  }
  function backup()
  {
   passthru($this->cmd, $retval);
 
   if(!empty($retval))
   {
   print_r($retval);
   }
  }
 }
 // initilize mysqldump
 $dump = new mysql_dump('root', '', 'test', './');
 // backup database
 $dump->backup();
 ?>[/code]

@_LOBO_authorJun 27.2005 — #Ok I test the file several time and don't work for me ? I send the file to a friend and he told me that the file was created but with nothing inside. Someone can confirm this please?

(Something that I found but dont have a clue how to use it someone can show me? http://www.adsworth.info/mysql2xml)

what is this? XMLDefinition( "localhost", "test", "root", "root", "mos_categories", [B]"rootnodename", "rownodename"[/B],"iso-8859-1")

@_LOBO_authorJun 28.2005 — #Ok someone tell me that the sript work, I don't know what im doin wrong:

$dump = new mysql_dump('root', 'root', 'mysql', './');

[IMG]http://img235.echo.cx/img235/3734/db6sl.gif[/IMG]

http://img235.echo.cx/img235/3734/db6sl.gif

? now you do it:

mysql_dump
hmm...

i have never been able to use it through php, only via the shell[/QUOTE]

@_LOBO_authorJul 05.2005 — #? anybody

@ShrineDesignsJul 05.2005 — #mysql is the database for the mysql engine

@_LOBO_authorJul 06.2005 — #Hi ShrineDesigns,

Thank you for stop here buddy, yes that is the DB from MYSQL but I try with other DB and nothing happen.

Im working on Windows Xp SP2 (WAMP server)

[B]Apache version :[/B] Apache/1.3.33 (Win32)

[B]PHP version : [/B] 5.0.4

[B]MySQL version : [/B] 4.1.10a-nt - extension : mysqli

Any ideas what is wrong ?

[COLOR=Blue]Thank you on advance![/COLOR]

[code=php]
 
 <?php 
 class mysql_dump 
 { 
 var $cmd = ''; 
 
  function mysql_dump($user, $pass, $name, $path) 
  { 
   $this->cmd = "mysqldump -q -X -u {$user} -p{$pass} {$name} -r {$path}{$name}.xml"; 
   //$this->cmd = "mysqldump -q -X -u $user -p$pass $name -r $path$name.xml"; 
  } 
  function backup() 
  { 
   passthru($this->cmd, $retval); 
 
   if(!empty($retval)) 
   { 
   print_r($retval); 
   } 
  } 
 } 
 // initilize mysqldump 
 $dump = new mysql_dump('root', 'root', 'newland_tours', './'); 
 // backup database 
 $dump->backup(); 
 ?> 
 
 [/code]

@chrysJul 06.2005 — #P.S. I've used PHPMyAdmin to dump a 45,000 line query into a textbox, lol, it worked, but it was BRUTALLY Slow. The import took 2 seconds from the command line.

@ShrineDesignsJul 06.2005 — #wow, you server is almost the same as mine lol

win 2k, apache 1.3.33, mysql 4.1.1, php 5.1

when, i first tested this, it didn't work, i realized that the mysql tools (mainly mysql_dump) was not registered, re-run the mysql config tool and chose the option to register the mysql tools or use the full path in the command (e.g. C:mysqlbinmysql_dump.exe)

@_LOBO_authorJul 07.2005 — #[B]mysql_dump was not registered[/B]

[B] 1.-[/B] re-run the mysql config tool: How on mysqlbin I don't have nothing called tools or something like that but I have a file called MySQLInstanceConfig.exe is that one ? and I should check this option ?

http://img14.imageshack.us/img14/1345/mysql7cj.gif

[B]2.-[/B]use the full path in the command: I should use full path on the script ?

Can you explaine me step by step please Im a bit lost :o Thank you very much on advance ShrineDesigns.

wow, you server is almost the same as mine lol

win 2k, apache 1.3.33, mysql 4.1.1, php 5.1

when, i first tested this, it didn't work, i realized that the mysql tools (mainly mysql_dump) was not registered, re-run the mysql config tool and chose the option to register the mysql tools or use the full path in the command (e.g. C:mysqlbinmysql_dump.exe)[/QUOTE]

@_LOBO_authorJul 08.2005 — #Someone can give a hand on how to register mysql_dump ?

@_LOBO_authorJul 11.2005 — #*up*

@ShrineDesignsJul 11.2005 — #

1. yes

2. if you register the mysql bin tools there is no need to use the full path in the command line, because windows knows where to find it

Also in #PHP _↴

Question about Radio Buttons and Redirect Query Mysql in PHP Javascript that executes php class

Success!

Help @_LOBO_ spread the word by sharing this article on Twitter...

Tweet This

How to export MYSQL to XML with mysqldump –xml

20 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

How to export MYSQL to XML with mysqldump –xml

20 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

20 Comments(s) _↴

Also in #PHP _↴