Use XML to Build Services Cheaply Using PHP and MySQL

Use XML to Build Services Cheaply Using PHP and MySQL

eb services are all about two things: XML and WSDL. When you build a Web service in a commercial platform such as Microsoft .NET or J2EE, you are building a smart server that can describe itself with WSDL, be invoked using a HTTP-Get, or SOAP, and return an XML payload.

But these platforms are expensive, of course. Some companies have (and will continue to) balk at the expense of implementing Web services. The problem is that, as XML and Web services are generally thought to go hand-in-hand, many ill-informed managers will dismiss excellent, low-cost opportunities to capitalize on XML.

It’s a grave mistake: You can build XML delivery servers without writing or deploying Web services. And you can leverage the magic of representing data in XML, meaning that, amongst other things, you can provide many different presentation styles without changing the data. You simply use an XSLT stylesheet to generate the HTML as desired. For example, if you have customers that want to see the same data but present it in their own styles, you do not need towrite two different applications?you can simply write one to retrieve the required data and present it as XML and then use two different XSLT stylesheets to present the data to each customers.

This article will step you through building such an XML delivery server using PHP and MySQL, both of which are open source and free. In addition, the software will run on an Apache platform, giving you an end-to-end open source experience.

The example application I’ll build in this article is for a company that provides sports data and statistics to the Web sites of different newspapers and other media companies, who want to present league standings in a way that is appropriate to their audience and style.

Step 1. Download and Install MySQL and Set Up the Data
Download MySQL 4.0 production release, and extract the zip file to a new directory. Launch the file setup.exe to install it. Once the application is installed, reboot. If the server isn’t running (check your task list for mysqld-nt.exe), you can run it from c:mysqlin.

Download MySQL Control Center (MySQLCC), extract the files, and run setup.exe. MySQL Control Center will install and place an icon on your desktop. Double click this icon to view the control center. On the left side of the screen you should see a list of MySQL servers. If the list is empty, right click the list and select “New.” A dialog will appear. Set the “Name” to “ContentManager” and the “Host” to “Localhost.”

Click “Add” and you will be returned to the previous screen. You should now see “ContentManager” on the list with a red button indicating that it has stopped. Right click “ContentManager” and select “Connect.” The database will start and you will be connected to it. (In the next section, you will set up a table for the content manager.)

This solution builds a very simple database structure using the MySQLCC. You can easily extrapolate from these instructions to build applications of your own. If you have MySQLCC open and you are connected to the ContentManager database, open the ContentManager node to view the ‘Databases,’ ‘Server Administration,’ and ‘User Administration’ nodes.

Open the ‘Databases’ nodes to view the database available on this server. It will probably be populated with ‘mysql’ and ‘test’ databases.

Right click the ‘Databases’ node, and select ‘Create New Database.’
Name the database ‘Content’ and click OK.
Right click the new ‘Content’ database and select ‘Connect.’ Open the node, and select ‘Tables.’
Right click ‘Tables’ and select ‘New Table.’

Add the following fields in the Table manager:



































Select the TeamID field, and click the ‘key’ icon to make TeamID the key field for the table. Also, make sure that AUTO_INCREMENT is checked, and make sure that TeamID is added as an Index on the ‘Indexes’ tab. Save the table using the name ‘Standings.’

You can now enter data on your table. Double click the table to access the table editor. If you prefer, some sample data is included in the download that comes with this article (see left column) as a CSV file. Check your MySQL documentation as to how to import the data from the MySQL command prompt.

Finally, you must give the root user (the default user set up by MySQL) a password. Double click the User administration node, and select root@localhost. Set the password to ‘root’ in the password box and click Apply. This step does not ensure a secure system. For a production system, you should set strong passwords for your database on a properly secured server.

Step 2. Download and Install Apache and the PHP Hypertext Processor
Download and install the version of Apache that is appropriate for your platform.

You can test to see if the server is running by typing http://localhost: where is the port that you selected when installing the web server, e.g. if you select 8080 as the port, test the server by pointing your browser to http://localhost:8080.

If the server is properly running, you should see the default Apache Web page.

The PHP hypertext parser may be downloaded from the PHP download page at For this article, I used PHP 4.3.2. Documentation is available on the PHP home page.

Unzip the PHP file to a directory on your hard driver (for example C:php4). In this directory, you will find a file called php.ini-dist. Rename it to php.ini and move it to your Windows directory. In addition, take the files MSVCRT.DLL and PHP4TS.DLL and move them to your WindowsSystem directory.

In your Program FilesApache GroupApacheconf directory, you will find a file called httpd.conf. You will need to make three modifications to this file.

First, in the ScriptAlias section, add the following line:

ScriptAlias /php4/ "C:/php4/"

In the AddType section, comment out any lines that configure php3, and add the following lines instead:

AddType application/x-httpd-php .php .phtmlAddType application/x-httpd-php-source .phps

In the Action section, add the following line:

Action application/x-httpd-php /php4/php.exe

Now you can save the file and restart the Apache server. It should now be set up to run PHP. You can test the PHP functionality by creating a simple text file containing the following text, and saving it out as test.php in the htdocs directory.

 Phpinfo() ?>

If you navigate to the page using http://localhost:8080/text.php you should see a page containing a lot of information on PHP. Check your httpd.conf settings carefully on this page to see that they match the settings noted above.

Step 3. XML Document Structure
Now that the blocks have been downloaded and installed, it is time to build the PHP application. But first I need to describe the taxonomy of the data that this application is built to return, as it is optimized for size.

Any recordset is based upon a table or number of tables, and contains a number of indices that are used to uniquely identify the data points. To represent this in XML, it is most efficient to use a structure as below:

This way, the index values aren’t replicated in the record items, and, they are also separated from the record items for easier reading.

The record items can then be children of the tag as follows:

	column1_value	column2_value	.	.	columnN_value

The only problem is that if you have a large number of records, or a large number of columns, your XML document will grow very large, affecting the speed of the server to generate it; the speed of the network to transport it, and the speed of the client to render it.

Therefore, we can design the document to be more efficient for all three by changing its structure. The code below optimizes the XML record structure.


This change in XML structure means that large recordsets will be a fraction of the size they would be in the traditional way, as above.

Step 4. Generating the XML from the Database Using MySQL
The application fronts two different queries to the data. The first query type o returns partial standings where games played and points scored are the only data that we are interested in, and the second is where full standings information is returned. In both cases, the data is ordered according to the league rules, where teams are ranked in order of points first and goal difference (goals scored less goals conceded) second.

The caller of the service determines which type of report, full or partial standings, will be delivered by using the service= parameter on the url.

For example:
http://localhost/xml.php?service=fullstandings will return the full standings
http://localhost/xml.php?service=standings will return the partial standings.

For the sake of simplicity, the example in this article handles only one parameterservice=. It is trivial to apply the techniques in this example application to make a more complex application with multiple parameters.

To pull the service parameter from the URL you use the $_GET[‘param_name’] function:

// Pull the service type from the URL parameter service=whatever$condition = $_GET['service'];..// Generate the query to run based on the condition passed in the service parameterif($condition=="fullstandings")	$query = "SELECT * FROM standings ORDER BY TeamPts DESC_      , TeamGAA     DESC";else	if($condition=="standings")		$query = "SELECT TeamName, TeamPlayed, TeamPts FROM_             standings ORDER BY TeamPts DESC, TeamGAA DESC";

PHP comes with a number of built-in functions for managing MySQL. The table below shows the functions used and why.



mysql_connect(host, user, password)

Create a connection to the specified server using the username and password for authentication. Returns a variable for the connection instance.

mysql_select(dbname, instance)

On the instance, select the specified database (dbname)


Run the query on the current database and get the results.


Turn the results into an array.

mysql_field_name(results, index)

Return the name of the field at position $i in results.

These can be seen in action below:

// Open the database and select the soccerleague database$db = mysql_connect("localhost", "root" , "");@ mysql_select_db("soccerLeague", $db)    or die("Unable to Select Database");// Execute the query to get the results		$result = mysql_query($query) or die("Query Failed");// Iterate throught the result rows$recordcount = mysql_numrows($result);while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {  $i=0;  foreach ($line as $col_value) {    $fieldname=mysql_field_name($result,$i);    if($i==0){      print "";	print ("");}print(" ");

You can also see from the code above how the XML is generated. When $i is equal to zero, you output the tag and leave the tag open. For all other columns ($i is nonzero), you output $fieldname=’$col_value’ which outputs a string containing column_name=column_value. Finally, when you have finished iterating through the column, you output the value ‘ />, which closes the tag. Finally you output ‘‘ which closes the XML output.

Here is an example of the XML output.

    	 TeamDrawn="3" TeamLost="2" TeamGF="16" TeamGA="13" TeamGAA="3" TeamPts="21" />   		 TeamDrawn="5" TeamLost="2" TeamGF="24" TeamGA="19" TeamGAA="5" TeamPts="20" /> ..</xml>

In this article you developed a simple PHP service that returns XML from a MySQL database. As this doesn’t support SOAP or WSDL it isn’t a full-fledged Web service but it will give you a good entry point into the language of Web services?XML?and allow you to develop XML-driven Web sites using leading open source technologies.


Share the Post: