Dimuthu’s Blog

WordPress has a very simple database schema. And it is well documented. You can access the complete description of the wordpress core database from here, http://codex.wordpress.org/Database_Description.

Anyway first time I looked at the database I was confused with the term and the term_taxonomy table, why we need two tables for term and taxonomies. In fact in wordpress, the table ‘posts’ is associated with the table ‘term_taxonomy’ and not the table ‘term’ itself. In the term taxonomy table the terms are associated to a link category, post category or a tag. So the associations of posts to a tag or category is something like this.

wp_term_post association

So in a case you try querying for posts with a given tag it will be like this. (Note that I have skipped the optional database table prefix which is by default ‘wp_’)

SELECT post_title,
       post_content,
       post_date
FROM posts p,
     terms t,
     term_relationships r,
     term_taxonomy tt
WHERE p.post_status='publish' AND
      tt.taxonomy = 'post_tag' AND
      p.id=r.object_id AND
      r.term_taxonomy_id=tt.term_taxonomy_id AND
      tt.term_id = t.term_id AND t.name LIKE ?

It is really easy to write a widget to the wordpress blog. So I thought of writing my own widget to show Google ads in my Blog. Here is how I did it.

1. First generate the JavaScript code for your adsense account from the Google Adsense Page. You can do this by sigining in to the Dashboard of Adsense from https://www.google.com/adsense/. Then click the “Adsense Setup” tab and follow the wizard.
2. Inside the “wp-content/plugins” directory of your WordPress installation create a file for your plugin. (Say myadsense_widget.php)
3. Then first write the code that should be appeared in your widget.  In this case you can just echo the the code provide by the Google. Anyway In order to make your widget complaint with the current theme, you have to use the code similar to the following.

   // the function for the widget
   function widget_myadsense($args) {
   	// being aware of the theme
   	extract($args);
   	echo $before_widget;
   	echo $before_title . "Google Adsense". $after_title;
   
   	// here you just echo the code provided by the google 
   	echo <<<GOOGLE_JS
   		<!-- in this space you have to copy paste
                   the code provided by the google-->
   GOOGLE_JS;
   
   	// again being aware of the theme
   	echo $after_widget;
   }

4. Then write the code to register the above function as a widget with the following piece of code.

   // initiating widget
   function myadsense_init()
   {
   	register_sidebar_widget(__('My Adsense'), 'widget_myadsense');
   }
   
   // adding the action
   add_action("plugins_loaded", "myadsense_init");

5. We are almost done here, But don’t forget you can mention your information as the widget plugin author with a comment similar to the following template.

   /*
   Plugin Name: MyAdsense
   Plugin URI: http://dimuthu.org
   Description: Adsense Plugin for my blog
   Author: Dimuthu Gamage
   Version: 0.1
   Author URI: http://dimuthu.org
   */

6. That is all you have to code. Now just go the Plugins section of the wordpress from your Dashboard and enable the plugin (“myadsesne”) you just created.
7. Go to the Design->Widget section and add your Widget to the Sidebar and click “Save Changes”. And go to your blog URL and make sure that the Ads are shown in there.

With PHP DataServices it is just a matter of putting a little configuration php file to make your database available as a web service. I only needed few minutes to make a simple web service from my blog after figuring out my wordpress database structure, http://wpbits.wordpress.com/2007/08/08/a-look-inside-the-wordpress-database/. In this guide, I m exposing the title, date and the content of each of my blog for my service, But you can extend this more the way you prefer.

1. Download and install WSF/PHP 1.3.2 and PHP Data Services Library. If WSF/PHP 2.0 released by the time you are reading this, (it is to be released in this week), you only need to install WSF/PHP, since the DataServices library is packed with WSF/PHP from 2.0.
2. Drop the following file (Say WordPressService.php) in to any of your web server document directories.

   <?php
   
   // Make sure you put the DataService.php in your include path
   require_once("wso2/DataServices/DataService.php");
   
   // database configuraitons
   // you have to set your database configurations in here..
   // These entries can be copy and past from the wp-config.php in your wordpress installation
   
   $config = array(
   "db" => "mysql",
   "username"=>DB_USER,
   "password"=> DB_PASSWORD,
   "dbname"=>DB_NAME,
   "dbhost"=>DB_HOST);
   // output format, plese check the API from http://wso2.org/wiki/display/wsfphp/API+for+Data+Services+Revised
   $outputFormat = array("resultElement" => "Posts",
   "rowElement" => "post",
   "elements" => array( "title" => "post_title",
   "content" => "post_content",
   "date" => "post_date"));
   
   // sql statment to execute, note that I assume the table prefix is wp_ (so the table name is wp_posts)
   // just check $table_prefix variable in the wp-config.php of your wordpress installation
   $sql="select post_title, post_content, post_date from wp_posts where post_status='published'";
   
   // operations is consist of inputFormat (optional), outputFormat(required), sql(sql), input_mapping(optional)
   $operations = array("getPosts"=>array("outputFormat"=>$outputFormat, "sql"=>$sql));
   $my_data_service = new DataService(array("config"=>$config,"operations"=>$operations));
   
   $my_data_service->reply();
   ?>

3. It is all. Just access the above file from a web browser, you see your service is hosted. Here is the endpoint for my service. http://ws.dimuthu.org/blog/WordpressService.php. Since I m using WSF/PHP latest svn, I m able to retrieve the wsdl automatically from http://ws.dimuthu.org/blog/WordpressService.php?wsdl. Please wait for WSF/PHP 2.0 release for ?wsdl feature.
4. To verify whether your service deployed correctly, you may need to write a simple test client. Yea I too wrote one. Since I have the wsdl generated, I just needed to generate the code for the client from the wsdl using wsdl2php tool. There is an online version of the tool in the wsf/php demo site. Here is the generated code for my client, http://labs.wso2.org/wsf/php/wsdl2phptool.php?wsdl_url=http%3A%2F%2Fws.dimuthu.org%2Fblog%2FWordpressService.php%3Fwsdl. I added the following code to the TODO section in handling response,

       if(is_array($response->post)) {
           foreach($response->post as $post) {
   
               echo "<h2>".$post->title . " - ".$post->date."</h2>";
               echo "<p>";
               echo $post->content;
               echo "</p>";
   
               echo "<hr/>";
           }
       }

   Check my Web Service client for the above service here, http://ws.dimuthu.org/blog/WordpressClient.php.

Now I can call for my blog from any web service enabled platform.