Exporting Database Records to Create XML Documents


Using PHP, you can export and convert the data stored in the database into an XML document. You can also manipulate the data stored in databases using various tools, such as XPath and XSLT.

To export the data from a database in an XML document:

  1. Establish a connection with the database.

  2. Retrieve data from the database by executing SQL queries.

  3. Create an XML document from the retrieved information in the memory of the system.

You can manipulate and parse the generated XML document using Document Object Model (DOM).

Connecting to a Database to Export Data

The first step in exporting data from a database into an XML document is to establish a connection with the MySQL database. PHP provides a specific set of APIs that perform various query operations on the MySQL database. The PHP functions that you use to work with MySQL database server are:

  • mysql_connect : Connects to the MySQL database. The syntax of the mysql_connect() function is:

     resource mysql_connect ( [string Host_Name [:port][:/path/to/socket]], string username [, string password ]]]) 

In the above syntax, you need to pass the hostname, username, and password parameters as arguments to the mysql_connect() function. You can also specify optional parameters, such as the port and path of the MySQL socket.

  • mysql_select_db : Selects a MySQL database from the specified link identifier. The syntax of the mysql_select_db() function is:

     bool mysql_select_db(string database, resource[link indentifier]) 

In the above syntax, the mysql_select_db() function returns the value, True, on establishing a connection with the database; and returns the value, False, when a connection is not established with the database.

  • mysql_query : Performs query operation on the currently selected database. The syntax of the mysql_query() function is :

     resource mysql_query ( string query [, resource link identifier]) 

In the above syntax, if no resource link identifier is specified, the query is generated from the currently opened linked.

  • mysql_fetch_object : Returns the result set of the query as an object. The syntax of the mysql_fetch_object() function is:

     object mysql_fetch_object(resource result, int [result _type]) 
  • mysql_close : Closes the connection to the database. The mysql_close() function returns a Boolean value. The syntax of the mysql_close() function is:

     bool mysql_close(resource[link identifier]) 
  • mysql_num_rows : Returns the number of rows in a result set. The syntax of the mysql_num_rows() function is:

     int mysql_num_rows(resource result) 
  • mysql_error : Returns the numerical value of the error message generated after running the MySQL statement. The syntax of the mySQL_error() function is:

     string msql_error(resource [link identifier ]) 
  • mysql_db_query : Selects a database and runs a query on the database. The syntax of the mysql_db_query() function is:

     resource mysql_db_query(string database, string query, resource[link identifier]) 

Listing 8-1 shows how to establish a connection with the database using the database access functions provided by PHP:

Listing 8-1: Creating a Connection to the Database
start example
 $connect = mysql_connect("localhost", "root", "root123") or die ("could not connect"); print("Successful Connection"); mysql_select_db("information") or die ("Unable to select database!"); $query = "SELECT * FROM employee"; $result = mysql_query($query) or die ("query failed, error code = : "  . mysql_errno()); 
end example
 

In the above listing:

  • The mysql_connect() function creates a connection to the MySQL database and returns value, True, when the connection is established.

  • You need to provide the hostname, username, and the password as arguments to the mysql_connect() function.

After connecting to a database, you can perform various operations such as create a table, and insert, update, and delete rows from the table.

Listing 8-2 shows how to create a table and insert rows in the MySQL database, information:

Listing 8-2: Creating the Employee table in MySQL
start example
 mysql> USE information -A; mysql> CREATE TABLE employee -> ( -> EmployeeName Varchar(50) NOT NULL, -> EmpID Varchar(5) NOT NULL, -> Salary Integer -> ); 
end example
 

The above listing creates the Employee table. After creating the Employee table, you can insert rows in the Employee table.

Listing 8-3 shows how to insert rows in the Employee table:

Listing 8-3: Inserting Rows in the Employee Table
start example
 mysql> INSERT INTO employee VALUES("Joseph", "1005", 20000); mysql> INSERT into employee -> VALUES("John", "1007",25000); mysql> INSERT into employee VALUES("Harry", "1008", 35000); 
end example
 

The above listing inserts three rows in the Employee table. You can retrieve data from the database using the SELECT command.

Figure 8-1 shows the output of the SELECT command:

click to expand: this figure shows the content of the employee table that is stored in the information database.
Figure 8-1: Contents of the Employee Table

You can export the content of the Employee table into an XML tree using the DOM parser. To do this, you need to first establish a connection with the database, and then retrieve data using the MySQL functions provided by PHP.

Creating an XML Document

After establishing the connection with a database, you need to create an XML document from the database. You can create an XML document from the data retrieved after querying the database, after implementing the DOM functions, such as new_xmldoc().

Listing 8-4 shows how to create an XML document from the data retrieved from the MySQL database:

Listing 8-4: Creating the XML Document
start example
 <?php $connection = mysql_connect("localhost", "root", "root123") or die ("Unable to connect!"); mysql_select_db("information") or die ("Unable to select database!"); $query = "SELECT * FROM employee"; $result = mysql_query($query) or die ("Error in query: $query. " . mysql_error()); if (mysql_num_rows($result) > 0) {    // Create the DomDocument object.    $doc = new_xmldoc("1.0");    // Add root node.    $root = $doc->add_root("EmployeeInfo");    // Iterate through result set.    while(list($EmployeeName, $EmpID, $Salary) = mysql_fetch_row($result))    {       // Create item node.       $rec = $root->new_child("employee", "");       $rec->set_attribute("EmpID", $EmpID);       $rec->new_child("EmployeeName", $EmployeeName);       $rec->new_child("Salary", $Salary);    }    // Print the tree.    echo $doc->dumpmem(); } ?> 
end example
 

In the above listing:

  • An XML document is created from the data obtained after querying the Employee table stored in the information database.

  • The new_xmldoc() function creates a new XML document.

  • The new_child() function allows you to add child nodes, such as EmpName and Salary, to the EmployeeInfo root node.

  • The dumpmem() function converts the generated XML document into a string that can be printed using the echo command.

Figure 8-2 shows the output of Listing 8-4:

click to expand: this figure shows the contents of the employee table in the form of an xml tree. the employee table is exported from the mysql database. in the above figure, employee is the root node that has various child nodes, such as employeename and salary. empid is the attribute node added to the employeename root node.
Figure 8-2: Contents of XML File

You can also export data from multiple tables simultaneously . In PHP, you can create an XML document from the data retrieved after performing the query operation on multiple tables. For example, you want to display the Employee designation and the department along with other personnel information stored in the Employee table. The Employee information, such as designation and department, are stored in the Employee_Info table in the information database.

Figure 8-3 shows the contents of the Employee_Info table created in the information database:

click to expand: this figure shows the contents of the employee_info table containing employee information, such as designation, department, and id.
Figure 8-3: Contents of Employee_Info Table

Listing 8-5 shows how to export the data retrieved from the database after querying the Employee table and Employee_Info table:

Listing 8-5: Exporting Data Retrieved from Multiple Tables
start example
 <?php $connection = mysql_connect("localhost","root","root123") or die ("Unable to connect!"); mysql_select_db("information") or die ("Unable to select database!"); $query = "SELECT * FROM employee"; $result = mysql_query($query) or die ("Error in query: $query. " . mysql_error()); if (mysql_num_rows($result) > 0) {    // Create the DomDocument object.     $doc = new_xmldoc("1.0");    // add root node    root = $doc->add_root("EmployeeInfo");    // Iterate through result set.    while(list($EmployeeName, $EmpID, $Salary) = mysql_fetch_row($result))    {       // Create item node.       $rec = $root->new_child("employee","");       $rec->set_attribute("EmpID", $EmpID);       $rec->new_child("EmployeeName", $EmployeeName);       $rec->new_child("Salary", $Salary);       // Add node from table Employee_Info.        $query2 = "SELECT Designation,Department FROM Employee_Info where ID = '$EmpID'";        $result2 = mysql_query($query2) or die ("Error in query: $query2. " . mysql_error());       // Print each track as a child node of <tracks>.       while(list($Designation,$Department) = mysql_fetch_row($result2))       {          $rec->new_child("Designation","$Designation");          $rec->new_child("Department","$Department");       }    }    // Dump XML document to a string. } $varf = $doc->dumpmem(); echo $varf; $fp = fopen("/var/www/html/out.xml", "w+"); fwrite($fp, $varf, strlen($varf)); ?> 
end example
 

The above listing shows how to export data retrieved from the database after querying the Employee and Employee_Info tables. In the above listing:

  • The new_xmldoc() function generates an XML document.

  • Data from the Employee and Employee_Info tables are inserted in the xml file created.

  • The nodes, such as designation, department, and employee are added to the root node after querying the Employee_Info table.

  • The dumpmem() function converts the XML document into a string, $varf.

  • The generated XML file is stored as a separate file using the fwrite() function.

Figure 8-4 shows the output of Listing 8-5:

click to expand: this figure shows the xml document with additional nodes, such as designation and department, added to the xml document created in listing 8-4. the additional nodes are added after querying the employee_info table.
Figure 8-4: Output of Exporting Data from Multiple Tables

Closing the Connection to a Database After Exporting Data

You need to close the MySQL connection after you have generated the result set from the MySQL database. The mysql_close() function lets you close the MySQL connection.

Listing 8-6 shows how to use the mysql_close() function to close database connection:

Listing 8-6: Closing Connection to MySQL Database
start example
 <?php $connection = mysql_connect("localhost", "root", "root123") or exit ("Could not connect"); print ("Connected successfully"); //Close connection. mysql_close($connection); ?> 
end example
 

In the above listing:

  • The mysql_close() function closes the connection to the MySQL database after generating the result.

  • The link identifier is specified as the argument to the mysql_close() function.

  • The mysql_close() function closes the last active link to the MySQL server by default.

Formatting XML Document into HTML Format

In addition to exporting data into an XML document, you can transform the data from the database into HTML format. To format a generated XML document into HTML format using the SAX parser:

  1. Generate the XML document after retrieving the records from the database.

  2. Transform the generated XML document into HTML pages.

You can transform an XML document into HTML format using the SAX parser. SAX is an event-based approach that parses an XML document in chunks , and processes the tags as the parser encounters them in the document. You can export the content of the Employee table into an XML file and then convert the XML document into HTML format.

Listing 8-7 shows how to generate the XML document from the database and convert the XML document into HTML:

Listing 8-7: Generating XML and Converting into HTML
start example
 <?php // Defining the database parameters. $hostname = "localhost"; $username = "root"; $password = "root123"; $database = "information"; $table = "employee"; //  Exporting the database records and generating a XML document.   // Querying the database. $connection = mysql_connect($hostname, $username, $password) or die ("Unable to connect!"); mysql_select_db($database) or die ("Unable to select database!"); $query = "SELECT * FROM $table"; $res = mysql_query($query) or die ("Error in query: $query. " . mysql_error()); if(mysql_num_rows($res) > 0) {    // Creating the DomDocument object.    doc = new_xmldoc("1.0");    // Adding the root node.    $root = $doc->add_root("table");    $root->set_attribute("name", $table);    // Creating nodes.    $struct = $root->new_child("struct", "");    $data = $root->new_child("data", "");    // Create elements for each field name, type and length.    $fields = mysql_list_fields($database, $table, $connection);    for ($t=0; $t<mysql_num_fields($fields); $t++)    {       $field = $struct->new_child("field", "");       $name = mysql_field_name($fields, $t);       $length = mysql_field_len($fields, $t);       $type = mysql_field_type($fields, $t);       $field->new_child("name", $name);       $field->new_child("type", $type);       $field->new_child("length", $length);    }    // Move on to getting the raw data (records).    // Iterate through result set.    while($row = mysql_fetch_row($res))    {       $record = $data->new_child("record", "");       foreach ($row as $field)       {          $record->new_child("item", $field);       }    }    // Dumping the XML tree as a string.    $xml_string = $doc->dumpmem(); } // Closing the database connection. mysql_close($connection); // Converting the XML document into an HTML page using SAX parser. // Array to hold HTML markup for starting tags. $startTags = array( 'TABLE' => '<html><head></head><body><table border="1" cellspacing="0"cellpadding="5">', 'STRUCTURE' => '<tr>', 'FIELD' => '<td bgcolor="silver"><font face="Times Roman" size="-1">', 'RECORD' => '<tr>', 'ITEM' => '<td><font face="Arial" size="-1">', 'NAME' => '<b>', 'TYPE' => '&nbsp;&nbsp;<i>(', 'LENGTH' => ', ' ); // Array to hold HTML markup for ending tags. $endTags = array( 'TABLE' => '</body></html></table>', 'STRUCTURE' => '</tr>', 'FIELD' => '</font></td>', 'RECORD' => '</tr>', 'ITEM' => '&nbsp;</font></td>', 'NAME' => '</b>', 'TYPE' => '', 'LENGTH' => ')</i>' ); // Call this when a start tag is found. function startElementHandler($parser, $name, $attributes) {    global $startTags;    if($startTags [$name])    {       // Look up array for this tag and print corresponding markup.       echo $startTags[$name];    } } // Call this when an end tag is found. function endElementHandler($parser, $name)  {    global $endTags;    if($endTags [$name])    {       // Look up array for this tag and print corresponding markup.       echo $endTags [$name];    } } // Call this when character data is found. function characterDataHandler($parser, $data) {    echo $data; } // Initialize the parser. $xml_parser = xml_parser_create(); // Set callback functions. xml_set_element_handler($xml_parser, "startElementHandler", "endElementHandler"); xml_set_character_data_handler($xml_parser, "characterDataHandler"); // Parse the XML document. if (!xml_parse($xml_parser, $xml_string, 4096)) {    $ec = xml_get_error_code($xml_parser);    die("XML parser error (error code " . $ec . "): " . xml_error_string($ec) .    "<br>Error occurred at line " . xml_get_current_line_number($xml_parser) . ", column "    .    xml_get_current_column_number($xml_parser) . ", byte offset " .    xml_get_current_byte_index($xml_parser)); } xml_parser_free($xml_parser); ?> 
end example
 

The above listing converts the content of the Employee table into an XML document using the DOM approach. The generated XML document is transformed into HTML format using the SAX parser.

You need to define various database parameters, such as hostname, username, and password, to establish a connection to the database. After establishing a connection with the database, you need to process queries to retrieve data from the database.

In the Listing 8-7:

  • The XML tree is created using the new_xmldoc() function, which belongs to the DOMDocument class.

  • The nodes are added to the XML tree after being retrieved from the result set.

  • The dumpmem() function lets you dump the generated XML tree in a string. You can also store the XML tree in a file. For example, in Listing 8-7, you use the following code to store the XML document in the export.xml file:

 $fp = fopen("/var/www/html/export.xml", "w+"); fwrite($fp, $doc->dumpmem(), strlen($doc->dumpmem())); 

Figure 8.5 shows the contents of the export.xml file:

click to expand: this figure shows the contents of the export.xml file, obtained after exporting the employee table using the dom approach.
Figure 8-5: The export.xml File

After the XML document is generated using the DOM approach, you need to close the database connection using the mysql_close() function.

Figure 8-6 shows the output obtained after running Listing 8-7 from the Web browser:

click to expand: this figure shows the contents of the employee table in the form of an html table.
Figure 8-6: Generating the Contents of Employee Table in HTML



Integrating PHP and XML 2004
Integrating PHP and XML 2004
ISBN: N/A
EAN: N/A
Year: 2004
Pages: 51

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net