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 '<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>';
}
/* disconnect from the db */
@mysql_close($link);
}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
Now, we can take a look at the possible results of the URL.
The XML Output
<posts> <post> <post_title>SSLmatic SSL Certificate Giveaway Winners</post_title> <guid>http://davidwalsh.name/?p=2304</guid> </post> <post> <post_title>MooTools FileManager</post_title> <guid>http://davidwalsh.name/?p=2288</guid> </post> <post> <post_title>PHPTVDB: Using PHP to Retrieve TV Show Information</post_title> <guid>http://davidwalsh.name/?p=2266</guid> </post> <post> <post_title>David Walsh: The Lost MooTools Plugins</post_title> <guid>http://davidwalsh.name/?p=2258</guid> </post> <post> <post_title>Create Short URLs Using U.Nu</post_title> <guid>http://davidwalsh.name/?p=2218</guid> </post> <post> <post_title>Create Bit.ly Short URLs Using PHP</post_title> <guid>http://davidwalsh.name/?p=2194</guid> </post> <post> <post_title>Represent Your Repositories Using the GitHub Badge!</post_title> <guid>http://davidwalsh.name/?p=2178</guid> </post> <post> <post_title>ZebraTable</post_title> <guid>http://davidwalsh.name/?page_id=2172</guid> </post> <post> <post_title>MooTools Zebra Table Plugin</post_title> <guid>http://davidwalsh.name/?p=2168</guid> </post> <post> <post_title>SSLmatic: Quality, Cheap SSL Certificates and Giveaway!</post_title> <guid>http://davidwalsh.name/?p=2158</guid> </post> </posts>
Take this next sample URL for example:
http://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"}}]}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!
Discussion
Be Heard!
Share your thoughts with fellow developers of all skill levels! I want to hear from you!
Hi David, yet another great post!
Although you may want to think about using mysql_real_escape_string on that $_GET['user'] string to prevent SQL Injection, it would be a shame to see all that hard work wasted otherwise.
Thanks,
Anthony.
I will also mention that it’s important to implement a caching system for web services. I will cover that at a later time.
David, this is absolutely brilliant.
I wouldn’t have known where to start with something like this — but now you have explained it I can give it a go.
Thank you!
@Anthony Sterling: In the case of the user ID, I’m using intval() to convert whatever is passed to a number. If someone tries a SQL injection using the $_GET['user'] var, their text simply gets changed to “0″ for the username. No problems there.
David, this is great … thank you for posting it up. I’ve been looking for some good code to do this for a while now. I’ve seen other approaches with obvious holes, so your well thought out solution is much appreciated. Thanks again!
Hi, what function log does? From php manual http://lt.php.net/manual/en/function.log.php its logarythmic function.
Maybe you wanted to write die() ?
@Manoakys: Updated — I have my own DB-logging class that I did a quick search/replace on. Thanks!
great post. Just a thing, tiny detail: when you test for parameters in the $_GET array, you should use the isset() function, otherwise you get a index warning if the parameter is not set:
$number_of_posts = isset($_GET['num'])? intval($_GET['num']) : 10; //10
I know, warnings are not the end of the world, but it’s better to avoid them when possible, so they don’t pollute the logs unecessarily and make you miss a real unexpected warning or error. And yeah I know warnings can be disabled, but I don’t recommand it, they can be useful
This is really nice work David, well done. I noticed a very minor typo, just FYI:
$result = mysql_query($query,$link) or die('Errant query: '.$query);;
There is one extra semicolon, no biggy! Thanks again!
-Drew
very good indeed David! thanks for everything.
Hey David, i wrote a related article about doing JSON requests to a wordpress database (mootools based). The php part is a bit shorter… The mootools part can also be found here ..
require(‘wp-blog-header.php’);
$categories = get_categories();
foreach ($categories as $category){
$posts = get_posts(“category=”.$category->cat_ID);
if( $posts ){
foreach( $posts as $post ){
setup_postdata( $post );
$jsonData[$post->ID]['name'] =$post->post_title;
$jsonData[$post->ID]['category'] =$category->name;
$jsonData[$post->ID]['permalink'] =$post->guid;
}
}
echo json_encode($jsonData);
regards Volkan
@olivier: That’s cleaner — most servers wouldn’t show the warning, but I’ve updated the header.
@Drew Douglass: Updated.
Is there a way to write the xml without hard write the tags?
@chocolim: You can use PHP’s SimpleXML library.
Great tutorial. Added to tutlist.com
Hi David,
Great post I’ve been wanting to do this for a couple of my sites. Have you considered a php tutorial on consuming these web services with cURL or other methods?
Hi David,
Thanks for the article. Very good explaination. I have a small doubt though : When I try to make some changes in the code, is it reflected for every output? I am using the same code to create my web service (which gives JSON output), but when I made some code changes, I did not get the new json output but the same old results. Instead, when I tested the output on a different browser, I got the expected new results. Am I doing something wrong?
@Mukesh: Could it be a caching issue?
David,
Nice article. Thought it might be good for you to take a look at one that i stumbled upon just the other day whilst researching this webservices area. this approach mimics the .net 3.5 scriptservice functionaility. i think (apart from the unobtrusive javascript anomoly) that it’s a great way to invoke asyncronys webservices:
http://www.codeproject.com/KB/net-languages/ScriptServiceInPHP.aspx
be interested to hear you thoughts on this approach.
cheers
jimi
You should also use htmlentities () to sanitize your $tag variables, just in case. MySQL considers XML special characters to be valid characters for field names, so if your database credentials were ever compromised, someone could ruin your day:
mysql> alter table wp_posts add `post><script src="evil.js"></post><post` tinyint;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
Hi David,
If you want to work in UTF-8, you should add:
mysql_query(“SET NAMES ‘utf8′”);
after the connection…
And in international context, htmlentities() should be removed because it prevent the XML to be well formed.
Regards
Would be nice to complete with handling an XML request or a Post of XML data.
Hi, Thanks. I created a variation of your script you might like.
it always cancels my post when i try to post the script. can i e-mail it to you ?
David,
Thanks for such a great tutorial… your tutorials on Mootools have saved my tail on a number of occasions… I plan on trying your solution soon!
Thanks!
Dennis
Ive been looking for an article like this, but i need a little more help…the rest of the code :) I need to create a webservice (jSON) for an iphone app that will read from and write to a server via php-xml-mySQL. I used to be an asp3 programmer so i know apps can communicate to db but im a bit lost at the moment because its been a while and jSON is new to me. Any help? Ideas? :)
i was on lookout for the most simple webservice article, and found one on your site, thanks
Hi David,
Really i m totally impressed with you.. your blog has helped me many time when i was in trouble during work on different APIs. Now again i have one issue. My question is : Is there any unofficial api for yahoomail/hotmail which doesn’t need 3rd party authentication? or Is there any web services by yahoomail/hotmail to retrieve a user inbox mail and display on my website after user login?
Regards,
Varun
@Varun Kumar Sen: I have same issue and looking for same library or PHP code.. but i think David is not interested to help us in this regard…
Good enough for the strarter.
Gr8 post david.. keep posting
This helped me. I actually used codeigniter for database queries but the logic of your post really helped a ton. Thanks!
If you’re just use this for some blog posts, why not use RSS. That is the format that is designed for it. If you use RSS people can read your posts in other readers to and do not have to create a custom script just to read some blog posts.
I understand that this is just an example how you could do it, and of course, for other data you can’t use the RSS format and must create your own.
@Arian: I think your second paragraph answered your own question. Also, this is the same way the XML for those RSS feeds are generated, soo….
Superb post for rest. I have a plan to implement this kind of solution to my site for admining remotely from my computer.
thanks for this post.
@mars: Hi Mars, I need to do almost exactly what you describe. Did you figure out the code needed? If so. can you share it with me?
Thanks
Ferris
if i want to make semantic web service, can i use PHP to make it??
because data in semantic database are based on ontology.
so if we use data in semantic we only need query.
@David Walsh: Hi david.. do you already have implement caching system for web service? If you have please let me know, thanks
Hello,
I have some error with french language:
Erreur d’analyse XML : entité non définie
The DB is UTF8…
How I can resolve issue ?
Thanks
@Rapid:
ok found:
$posts[] = array(‘post’=>array_map(‘utf8_encode’,$post));
thanks