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"}}]}