Web Service Using PHP, MySQL, XML, and JSON

Today Web Services gain much popularity in web world. There are thousands of Web Services availble for updating E-Commerce, schools, stock market database etc. Actually Web services are just Web APIs that can be accessed over a network, such as Internet, and executed on a remote system hosting the requested services. There are three basic platform for We Services, these are SOAP, WSDL and UDDI.

Here we will discuss to create basic web service that provides an XML or JSON response using some PHP and MySQL.

The PHP / MySQL

/* require the user as the parameter */
if(isset($_GET['myuser']) && intval($_GET['myuser'])) {
$number_of_posts = isset($_GET['number']) ? intval($_GET['number']) : 20;
$format = strtolower($_GET['format']) == 'json' ? 'json' : 'xml';
$user_id = intval($_GET['myuser']);
/* connect to mysql database */
$link = mysql_connect('localhost','username','password') or die('Can not connect to the Database');
mysql_select_db('mydb_name',$link) or die('Can not select the Database');
/* select records from the database */
$query = "SELECT post_title, guid FROM posts WHERE wp_post_author = $user_id AND post_status = 'publish' ORDER BY ID DESC LIMIT $number_of_posts";
$result = mysql_query($query,$link) or die('Errant query:  '.$query);
/* create array of the records */
$posts = array();
if(mysql_num_rows($result)) {
while($post = mysql_fetch_assoc($result)) {
$posts[] = array('post'=>$post);
}
}
/* output in required format */
if($format == 'json') {
header('Content-type: application/json');
echo json_encode(array('posts'=>$posts));
}
else {
header('Content-type: text/xml');
echo '<posts>';
foreach($posts as $index => $post) {
if(is_array($post)) {
foreach($post as $key => $value) {
echo '<',$key,'>';
if(is_array($value)) {
foreach($value as $tag => $val) {
echo '<',$tag,'>',htmlentities($val),'</',$tag,'>';
}
}
echo '</',$key,'>';
}
}
}
echo '</posts>';
}
/* close database connection */
@mysql_close($link);
}

We will take the following sample URL for example:

http://mydomain.com/web-service.php?user=5&num=3

Below is the possible results of the above URL.
 

This is XML Output 

<posts>
<post>
<post_title>Now YouTube Video Player Uses HTML5</post_title>
<guid>http://www.phpzag.com/?p=1568</guid>
</post>
<post>
<post_title>PHP – Parse YouTube URLS</post_title>
<guid>http://www.phpzag.com/?p=1473</guid>
</post>
<post>
<post_title>What is HTML5?</post_title>
<guid>http://www.phpzag.com/?p=1399</guid>
</post>
</posts>

Now We will go for next sample URL for example:

http://mydomain.com/web-service.php?user=5&num=3&format=json

Now, we can take a look at the possible results of the URL.
 

The JSON Output

{"posts":[{"post":{"post_title":"Now YouTube Video Player Uses HTML5","guid":"http:\/\/phpzag.com\/?p=1568"}},{"post":{"post_title":"PHP – Parse YouTube URLS","guid":"http:\/\/phpzag.com\/?p=1473"}},{"post":{"post_title":"What is HTML5?","guid":"http:\/\/phpzag.com\/?p=1399"}}]}

XML-to-JSON convertor and remapper with jQuery

This is a jQuery plugin for converting data from XML to JSON format. However its real power lies in its ability to also remap data to a different structure. This can be useful when you have a plugin or application that requires JSON data, but the data is coming from an XML feed that you don’t have structural control over. Data is remapped to the structure you need.

To read more about this article Click Here

Client Testimonials Powered by PHP, XML and jQuery with Demo

In this tutorial we are going to build a XML backed testimonial viewer, which, along with jQuery and XSL transformations, can display the set on your product pages.

To read more about this article Click Here

For Demo Click Here

From MySQL to HTML with PHP and XML

In my post From MySQL to XML with PHP, I described how to generate XML from the MySQL database. Instead of printing out XML, generated XML can be buffered and transformed with XSL to the HTML. Maybe it sounds complicated for a simple process of displaying MySQL data on the WEB page. But if you set a WEB architecture this way, you will have a separated presentation layer from the database and business logic.



Here is example of final PHP source. You can see how it looks simple and clean. If you compare this code with PHP source from the previous post, you can notice two lines more: ob_start and bottom xsl.php include. ob_start starts output buffering while xsl.php uses this buffer and performs XSL transformation.



<? include('sql2xml.php') ?>
<? ob_start() ?>
<DOCUMENT>
    <? sql2xml('select a.alb_id, a.alb_name,
                                         s.sng_number, s.sng_name
                            from album a, song s
                            where a.alb_id = s.alb_id and   s.sng_number < 3
                            order by a.alb_id, s.sng_number', '2') ?>
</DOCUMENT>
<? include('xsl.php') ?>


Save file as test1.php and if you run it from the command line php test1.php, you should get the following HTML table:



<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
  <body>
    <table border="1">
      <tr><td colspan="2">Nevermind</td></tr>
      <tr><td>1</td><td>Breed</td></tr>
      <tr><td>2</td><td>Come As You Are</td></tr>
      <tr><td colspan="2">Band of Gypsys</td></tr>
      <tr><td>1</td><td>Who Knows</td></tr>
      <tr><td>2</td><td>Machine Gun</td></tr>
    </table>
  </body>
</html>


XSL transformation needs XML and XSL file. If you want to know how XML looks, please open From MySQL to XML with PHP post. Here is the XSL file that is used to transform generated XML. Please name it test1.xsl. It's important to have the same name as PHP file (except suffix), otherwise XSL file will not be found.

<?xml version="1.0"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="html" doctype-public="-//W3C//DTD HTML 4.01//EN"

            doctype-system="http://www.w3.org/TR/html4/strict.dtd" />

  <xsl:template match="DOCUMENT"><!-- main template -->

    <html>

    <body>

            <table border="1"><xsl:apply-templates select="ROW0"/></table><!-- album loop -->

    </body>

    </html>

  </xsl:template>

  <xsl:template match="ROW0"><!-- album template -->

      <tr><td colspan="2"><xsl:value-of select="ALB_NAME"/></td></tr>

      <xsl:apply-templates select="ROW1"/><!-- song loop -->

  </xsl:template>

  <xsl:template match="ROW1"><!-- song template -->

      <tr>

          <td><xsl:value-of select="SNG_NUMBER"/></td>

          <td><xsl:value-of select="SNG_NAME"/></td>

      </tr>

  </xsl:template>

</xsl:stylesheet>

Before you run php test1.php, make sure you have installed php-xml package or you will get XsltProcessor not founderror. To install php-xml package with the yum utility, run the following command:

yum install php-xml


After getting current buffer contents (XML), and preparing XSL file name, script will begin the XSL transformation and generate the final HTML. Before printing, HTML can be stored to the cache - APC. Next time, instead of database, HTML can be fetched from the cache and this is very fast!



<?
// get current buffer contents and delete current output buffer
$xml_data = ob_get_clean(); 

// define xsl file name from the script itself
$_name    = explode('.', basename($_SERVER['SCRIPT_NAME']));
$xsl_file = current($_name) . '.xsl';

// create XSLT processor
$xp = new XsltProcessor();
// load the xml document and the xsl template
$xml = new DomDocument;
$xsl = new DomDocument;
$xml->loadXML($xml_data);
$xsl->load($xsl_file);

// load the xsl template
$xp->importStyleSheet($xsl);

// do XSL transformation and print result
print($xp->transformToXML($xml));
?>

From MySQL to XML with PHP

PHP XML generator can be written in less then 100 lines. In this example, data from the MySQL database are converted to the XML. PHP function sql2xml has an optional second parameter to describe the XML hierarchy, while first parameter is SQL query. Second parameter has the form of integers separated by commas. 

For example, to describe XML structure in three levels, second parameter can be "2,3". Each integer defines number of columns from SQL in XML hierarchy. The last level is not necessary to specify.

Here is example of how to use the sql2xml PHP function.

<? include('sql2xml.php') ?>

<DOCUMENT>

    <? sql2xml('select a.alb_id, a.alb_name,

                                         s.sng_number, s.sng_name

                            from album a, song s

                            where a.alb_id = s.alb_id and   s.sng_number < 3

                            order by a.alb_id, s.sng_number', '2') ?>

</DOCUMENT>

PHP function will transform SQL to the XML and here is the result. Column names from the SQL are used for XML tags. This example has a hierarchical structure in two levels because only first level is defined. Two columns (alb_id and alb_name) are the first level, while the other columns from the SQL are in the second level.

<DOCUMENT>

    <ROW0>

        <ALB_ID>1</ALB_ID>

        <ALB_NAME>Nevermind</ALB_NAME>

        <ROW1>

            <SNG_NUMBER>1</SNG_NUMBER>

            <SNG_NAME>Breed</SNG_NAME>

        </ROW1>

        <ROW1>

            <SNG_NUMBER>2</SNG_NUMBER>

            <SNG_NAME>Come As You Are</SNG_NAME>

        </ROW1>

    </ROW0>

    <ROW0>

        <ALB_ID>2</ALB_ID>

        <ALB_NAME>Band of Gypsys</ALB_NAME>

        <ROW1>

            <SNG_NUMBER>1</SNG_NUMBER>

            <SNG_NAME>Who Knows</SNG_NAME>

        </ROW1>

        <ROW1>

            <SNG_NUMBER>2</SNG_NUMBER>

            <SNG_NAME>Machine Gun</SNG_NAME>

        </ROW1>

    </ROW0>

</DOCUMENT>

PHP function will make connection to the MySQL database and transform SQL to the XML. Please, don't forget to set MySQL username and password in mysql_pconnect line at the beginning of sql2xml().

/**

 * sql2xml prints structured XML

 *

 * @param string  $sql       - SQL statement

 * @param string  $structure - XML hierarchy

 * @param integer $indent    - starting indent (number of spaces)

 */

function sql2xml($sql, $structure=0, $indent=0){

    // init variables for row processing

    $row_current = $row_previous = null;

    // set MySQL username and password

    $db_cn = mysql_pconnect('localhost', 'username', 'password');

    mysql_select_db('test', $db_cn); // connect to the database test

  $result = mysql_query($sql, $db_cn);

    // get number of columns in result

  $ncols = mysql_num_fields($result);

  // is there a hierarchical structure

  if ($structure == 0) {$deep = -1; $pos = 0;}

  else{

    $hierarchy = explode(',', $structure); // hierarchy levels

    $deep      = count($hierarchy);        // number of levels

    // set flags for opened tags

    for ($i=0; $i <= $deep; $i++) $tagOpened[$i] = false;

    // set initial row

    for ($i=0; $i < $ncols; $i++) $rowPrev[$i] = microtime();

  }

  // loop through result set

  while($row = mysql_fetch_row($result)){

    // loop through hierarchy levels (data set columns)

    for ($level=0,$pos=0; $level<$deep; $level++){

      // prepare row segments to compare

      for ($i=$pos; $i < $pos+$hierarchy[$level]; $i++){

        $row_current  .= trim($row[$i]);

        $row_previous .= trim($rowPrev[$i]);

      }

      // test row segments between row_current and row_previous

      if ($row_current != $row_previous){

        // close current tag and all tags below

        for ($i=$deep; $i >= $level; $i--){

          if ($tagOpened[$i]) printf('%'.($i*2+$indent)."s</ROW%d>\n", '', $i);

          $tagOpened[$i] = false;

        }

        // reset the rest of rowPrev

        for ($i=$pos; $i < $ncols; $i++) $rowPrev[$i] = microtime();

        // set flag to open

        $tagOpened[$level] = true;

        printf('%'.($level*2+$indent)."s<ROW%d>\n", '', $level);

        // loop through hierarchy levels

        for ($i=$pos; $i < $pos+$hierarchy[$level]; $i++){

          $name = strtoupper(mysql_field_name($result, $i));

          printf('%'.(($level+1)*2+$indent)."s<%s>%s</%s>\n",

                        '', $name, trim(htmlspecialchars($row[$i],$i)), $name);

        }

      }

      // increment row position

      $pos += $hierarchy[$level];

      // reset row segments (part of columns)

      $row_current = $row_previous = '';

    }

    // print rest

        printf('%'.($level*2+$indent)."s<ROW%d>\n", '', $level);

    for ($i=$pos; $i < $ncols; $i++){

      $name = strtoupper(mysql_field_name($result, $i));

      printf('%'.(($level+1)*2+$indent)."s<%s>%s</%s>\n",

                    '', $name, trim(htmlspecialchars($row[$i],$i)), $name);

    }

    printf('%'.($level*2+$indent)."s</ROW%d>\n", '', $level);

    $rowPrev = $row; // remember previous row

  }

  // close opened tags

  for ($level=$deep; $level>=0; $level--)

    if ($tagOpened[$level])

        printf('%'.($level*2+$indent)."s</ROW%d>\n", '', $level);

}

Here are create table statements for tables album and song used in this example.

create table album (

alb_id int(11) not null auto_increment,

alb_name varchar(32) not null,

PRIMARY KEY (alb_id)

);

insert into album values (1,'Nevermind'), (2,'Band of Gypsys');

create table song (

sng_id int(11) not null auto_increment,

alb_id int(11) not null,

sng_number int(11) not null,

sng_name varchar(64) not null,

PRIMARY KEY (sng_id)

);

insert into song values

(1, 1, 1,'Breed'),

(2, 1, 2,'Come As You Are'),

(3, 1, 3,'Drain You'),

(4, 1, 4,'Endless, Nameless'),

(5, 1, 5,'In Bloom Lyrics'),

(6, 1, 6,'Lithium'),

(7, 1, 7,'Lounge Act'),

(8, 1, 8,'On A Plain'),

(9, 1, 9,'Polly'),

(10,1,10,'Smells Like Teen Spirit'),

(11,1,11,'Something In The Way'),

(12,1,12,'Stay Away'),

(13,1,13,'Territorial Pissings'),

(14,2, 1,'Who Knows'),

(15,2, 2,'Machine Gun'),

(16,2, 3,'Changes'),

(17,2, 4,'Power to Love'),

(18,2, 5,'Message to Love'),

(19,2, 6,'We Gotta Live Together');

In my next post From MySQL to HTML with PHP and XML, you can find how to transform XML from this example to the HTML. Sounds a bit complicated, but if the WEB architecture is set in this way, you will have a separate presentation layer with cleaner and simpler PHP code.

PHP: Export Database Schema as XML

Sometimes it can be useful to have a dump of the current database schema. The script below reads the schema from a MySQL database and outputs XML that describes the schema.

At first we connect to a MySQL database and use the SHOW TABLES command to return all the tables in the database. Next, we iterate over each table and return the fields for each table using the SHOW FIELDS command. Finally, we put all of the returned information into XML.

Have a look at the code:

<?php

// database constants

// make sure the information is correct

define("DB_SERVER", "localhost");

define("DB_USER", "root");

define("DB_PASS", "password");

define("DB_NAME", "tutorials");

// connection to the database 

$dbhandle = mysql_connect(DB_SERVER, DB_USER, DB_PASS) 

   or die("Unable to connect to MySQL"); 

// select a database to work with 

$selected = mysql_select_db(DB_NAME, $dbhandle) 

   or die("Could not select examples"); 

// return all available tables 

$result_tbl = mysql_query( "SHOW TABLES FROM ".DB_NAME, $dbhandle ); 

$tables = array(); 

while ($row = mysql_fetch_row($result_tbl)) { 

   $tables[] = $row[0]; 

} 

$output = "<?xml version=\"1.0\" ?>\n"; 

$output .= "<schema>"; 

// iterate over each table and return the fields for each table

foreach ( $tables as $table ) { 

   $output .= "<table name=\"$table\">"; 

   $result_fld = mysql_query( "SHOW FIELDS FROM ".$table, $dbhandle ); 

   while( $row1 = mysql_fetch_row($result_fld) ) {

      $output .= "<field name=\"$row1[0]\" type=\"$row1[1]\"";

      $output .= ($row1[3] == "PRI") ? " primary_key=\"yes\" />" : " />";

   } 

   $output .= "</table>"; 

} 

$output .= "</schema>"; 

// tell the browser what kind of file is come in

header("Content-type: text/xml"); 

// print out XML that describes the schema

echo $output; 

// close the connection 

mysql_close($dbhandle); 

?>

You should note that this code is specific to MySQL database. The commands such as SHOW TABLESand SHOW FIELDS are also available for other databases but are specified slightly differently