Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Use XML to Build Services Cheaply Using PHP and MySQL : Page 5

Using the open source technologies of PHP and MySQL you can create server-side applications that abstract databases and return XML. Get all the flexibility of XML without laying out cash, and show your boss a cheap alternative to J2EE or .NET Web services in a runtime environment.


advertisement
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.



Laurence Moroney is a freelance enterprise architect who specializes in designing and implementing service-oriented applications and environments using .NET, J2EE, or (preferably) both. He has authored books on .NET and Web services security, and more than 30 professional articles. A former Wall Street architect, and security analyst, he also dabbles in journalism, reporting for professional sports. You can find his blog at http://www.philotic.com/blog.
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap