Create a Basic Web Service Using PHP, MySQL, XML, and JSON

Web services are taking over the world. I credit Twitter's epic rise to the availability of a simple but rich API. Why not use the same model for your own sites? Here's how to create a 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['user']) && intval($_GET['user'])) {

 /* soak in the passed variable or set our own */
 $number_of_posts = isset($_GET['num']) ? intval($_GET['num']) : 10; //10 is the default
 $format = strtolower($_GET['format']) == 'json' ? 'json' : 'xml'; //xml is the default
 $user_id = intval($_GET['user']); //no default

 /* connect to the db */
 $link = mysql_connect('localhost','username','password') or die('Cannot connect to the DB');
 mysql_select_db('db_name',$link) or die('Cannot select the DB');

 /* grab the posts from the db */
 $query = "SELECT post_title, guid FROM wp_posts WHERE 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 one master array of the records */
 $posts = array();
 if(mysql_num_rows($result)) {
  while($post = mysql_fetch_assoc($result)) {
   $posts[] = array('post'=>$post);
  }
 }

 /* output in necessary format */
 if($format == 'json') {
  header('Content-type: application/json');
  echo json_encode(array('posts'=>$posts));
 }
 else {
  header('Content-type: text/xml');
  echo '';
  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),'';
      }
     }
     echo '';
    }
   }
  }
  echo '';
 }

 /* disconnect from the db */
 @mysql_close($link);
}

Copy this code to the clipboard

1/* require the user as the parameter */

2if(isset($_GET['user']) && intval($_GET['user'])) {

3

4 /* soak in the passed variable or set our own */

5 $number_of_posts = isset($_GET['num']) ? intval($_GET['num']) : 10; //10 is the default

6 $format = strtolower($_GET['format']) == 'json' ? 'json' : 'xml'; //xml is the default

7 $user_id = intval($_GET['user']); //no default

8

9 /* connect to the db */

10 $link = mysql_connect('localhost','username','password') or die('Cannot connect to the DB');

11 mysql_select_db('db_name',$link) or die('Cannot select the DB');

12

13 /* grab the posts from the db */

14 $query = "SELECT post_title, guid FROM wp_posts WHERE post_author = $user_id AND post_status = 'publish' ORDER BY ID DESC LIMIT $number_of_posts";

15 $result = mysql_query($query,$link) or die('Errant query: '.$query);

16

17 /* create one master array of the records */

18 $posts = array();

19 if(mysql_num_rows($result)) {

20 while($post = mysql_fetch_assoc($result)) {

21 $posts[] = array('post'=>$post);

22 }

23 }

24

25 /* output in necessary format */

26 if($format == 'json') {

27 header('Content-type: application/json');

28 echo json_encode(array('posts'=>$posts));

29 }

30 else {

31 header('Content-type: text/xml');

32 echo '';

33 foreach($posts as $index => $post) {

34 if(is_array($post)) {

35 foreach($post as $key => $value) {

36 echo '<',$key,'>';

37 if(is_array($value)) {

38 foreach($value as $tag => $val) {

39 echo '<',$tag,'>',htmlentities($val),',$tag,'>';

40 }

41 }

42 echo ',$key,'>';

43 }

44 }

45 }

46 echo '';

47 }

48

49 /* disconnect from the db */

50 @mysql_close($link);

51}

With the number of persons hitting your web service (hopefully), you'll need to do adequate validation before attempting to connect to the database to avoid injection attacks. Once we get the desired results from the database, we cycle through the results to populate our return results array. Depending upon the response type desired, we output the proper header and content in the desired format.

Take the following sample URL for example:

http://mydomain.com/web-service.php?user=2&num=10

Copy this code to the clipboard

1http://mydomain.com/web-service.php?user=2&num=10

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

The XML Output

 
  SSLmatic SSL Certificate Giveaway Winners
  http://davidwalsh.name/?p=2304
 
 
  MooTools FileManager
  http://davidwalsh.name/?p=2288
 
 
  PHPTVDB: Using PHP to Retrieve TV Show Information
  http://davidwalsh.name/?p=2266
 
 
  David Walsh: The Lost MooTools Plugins
  http://davidwalsh.name/?p=2258
 
 
  Create Short URLs Using U.Nu
  http://davidwalsh.name/?p=2218
 
 
  Create Bit.ly Short URLs Using PHP
  http://davidwalsh.name/?p=2194
 
 
  Represent Your Repositories Using the GitHub Badge!
  http://davidwalsh.name/?p=2178
 
 
  ZebraTable
  http://davidwalsh.name/?page_id=2172
 
 
  MooTools Zebra Table Plugin
  http://davidwalsh.name/?p=2168
 
 
  SSLmatic: Quality, Cheap SSL Certificates and Giveaway!
  http://davidwalsh.name/?p=2158
 

Copy this code to the clipboard

1<posts>

2 <post>

3 <post_title>SSLmatic SSL Certificate Giveaway Winners</post_title>

4 <guid>http://davidwalsh.name/?p=2304

5 </post>

6 <post>

7 <post_title>MooTools FileManager</post_title>

8 <guid>http://davidwalsh.name/?p=2288

9 </post>

10 <post>

11 <post_title>PHPTVDB: Using PHP to Retrieve TV Show Information</post_title>

12 <guid>http://davidwalsh.name/?p=2266

13 </post>

14 <post>

15 <post_title>David Walsh: The Lost MooTools Plugins</post_title>

16 <guid>http://davidwalsh.name/?p=2258

17 </post>

18 <post>

19 <post_title>Create Short URLs Using U.Nu</post_title>

20 <guid>http://davidwalsh.name/?p=2218

21 </post>

22 <post>

23 <post_title>Create Bit.ly Short URLs Using PHP</post_title>

24 <guid>http://davidwalsh.name/?p=2194

25 </post>

26 <post>

27 <post_title>Represent Your Repositories Using the GitHub Badge!</post_title>

28 <guid>http://davidwalsh.name/?p=2178

29 </post>

30 <post>

31 <post_title>ZebraTable</post_title>

32 <guid>http://davidwalsh.name/?page_id=2172

33 </post>

34 <post>

35 <post_title>MooTools Zebra Table Plugin</post_title>

36 <guid>http://davidwalsh.name/?p=2168

37 </post>

38 <post>

39 <post_title>SSLmatic: Quality, Cheap SSL Certificates and Giveaway!</post_title>

40 <guid>http://davidwalsh.name/?p=2158

41 </post>

42</posts>

Take this next sample URL for example:

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

Copy this code to the clipboard

1http://mydomain.com/web-service.php?user=2&num=10&format=json

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

The JSON Output

{"posts":[{"post":{"post_title":"SSLmatic SSL Certificate Giveaway Winners","guid":"http:\/\/davidwalsh.name\/?p=2304"}},{"post":{"post_title":"MooTools FileManager","guid":"http:\/\/davidwalsh.name\/?p=2288"}},{"post":{"post_title":"PHPTVDB: Using PHP to Retrieve TV Show Information","guid":"http:\/\/davidwalsh.name\/?p=2266"}},{"post":{"post_title":"David Walsh: The Lost MooTools Plugins","guid":"http:\/\/davidwalsh.name\/?p=2258"}},{"post":{"post_title":"Create Short URLs Using U.Nu","guid":"http:\/\/davidwalsh.name\/?p=2218"}},{"post":{"post_title":"Create Bit.ly Short URLs Using PHP","guid":"http:\/\/davidwalsh.name\/?p=2194"}},{"post":{"post_title":"Represent Your Repositories Using the GitHub Badge!","guid":"http:\/\/davidwalsh.name\/?p=2178"}},{"post":{"post_title":"ZebraTable","guid":"http:\/\/davidwalsh.name\/?page_id=2172"}},{"post":{"post_title":"MooTools Zebra Table Plugin","guid":"http:\/\/davidwalsh.name\/?p=2168"}},{"post":{"post_title":"SSLmatic: Quality, Cheap SSL Certificates and Giveaway!","guid":"http:\/\/davidwalsh.name\/?p=2158"}}]}

Copy this code to the clipboard

1{"posts":[{"post":{"post_title":"SSLmatic SSL Certificate Giveaway Winners","guid":"http:\/\/davidwalsh.name\/?p=2304"}},{"post":{"post_title":"MooTools FileManager","guid":"http:\/\/davidwalsh.name\/?p=2288"}},{"post":{"post_title":"PHPTVDB: Using PHP to Retrieve TV Show Information","guid":"http:\/\/davidwalsh.name\/?p=2266"}},{"post":{"post_title":"David Walsh: The Lost MooTools Plugins","guid":"http:\/\/davidwalsh.name\/?p=2258"}},{"post":{"post_title":"Create Short URLs Using U.Nu","guid":"http:\/\/davidwalsh.name\/?p=2218"}},{"post":{"post_title":"Create Bit.ly Short URLs Using PHP","guid":"http:\/\/davidwalsh.name\/?p=2194"}},{"post":{"post_title":"Represent Your Repositories Using the GitHub Badge!","guid":"http:\/\/davidwalsh.name\/?p=2178"}},{"post":{"post_title":"ZebraTable","guid":"http:\/\/davidwalsh.name\/?page_id=2172"}},{"post":{"post_title":"MooTools Zebra Table Plugin","guid":"http:\/\/davidwalsh.name\/?p=2168"}},{"post":{"post_title":"SSLmatic: Quality, Cheap SSL Certificates and Giveaway!","guid":"http:\/\/davidwalsh.name\/?p=2158"}}]}

Creating a basic web service is very simple and encourages your users to spread the word about your website or service. Want more traffic? Want your website to grow without you putting in all the effort? Create a web service!