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 parameter
if($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.

Function	Purpose
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)
mysql_query(query)	Run the query on the current database and get the results.
mysql_fetch_array(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 "<record id='$col_value'>";
	print ("<rowitem");
	}
    else
	print (" $fieldname='$col_value'");
	$i++;
    }
  print ("/></record>");
}
print(" </xml>");

You can also see from the code above how the XML is generated. When $i is equal to zero, you output the tag <record id='current column value'><rowitem 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 ' /></record>', which closes the tag. Finally you output '</xml>' which closes the XML output.

Here is an example of the XML output.

<?xml version="1.0" ?> 
<xml>
   	<record id="10">
<rowitem TeamName="San Jose Earthquakes" TeamPlayed="11" TeamWon="6"
 TeamDrawn="3" TeamLost="2" TeamGF="16" TeamGA="13" TeamGAA="3" TeamPts="21" /> 
  		</record>
<record id="7">
<rowitem TeamName="Kansas City Wizards" TeamPlayed="12" TeamWon="5"
 TeamDrawn="5" TeamLost="2" TeamGF="24" TeamGA="19" TeamGAA="5" TeamPts="20" /> 
</record>
.
.
</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.