Reading a SQL database from within Wordpress.
Whilst designing my website, as with any designer worth there salt, I came accross a few interesting challenges that needed to beaten in order for my ideal layout to be achieved. Most of these challenges were overcome by Googling and trial and error, and I can say without a shadow of doubt that this has been a great learning curve for me.
There was however one particular problem with my layout that was harder to rectify. In my plans, I had hoped to have a centre column on most pages which showed the latest photo albums that had been uploaded to my site. I began researching into photo album solutions which I could integrate into my site, and after lots of coffee and searching, I came accross SlideshowPro.
SlideshowPro is a flash based photo album. There is an optional component that you can purchase for it, namely SlideshowPro Director, which acts as a web-based management tool for the gallery, and I have to say, it’s awesome. After having compiled my gallery into a flash movie, I uploaded it to my site, along with Director, and began adding albums. Easy as pie.
Now, Director uses an XML file approach to organising information about your galleries. This is great I thought, as I know there are some excellent resources for programming XML with PHP on the web, the most common of which seemed to be the SimpleXML function. As I read further into the function, I realised how simple this whole thing could be, and whipped up a script in about an hour. The script went through the XML file, picked out the latest 6 albums to be added, and returned the thumbnail image for each album to the page, the result being the list of thumbnails you see on my site now.
Perfect.
Well, almost. Sitting there chuffed at the fact I had just handcrafted a nice bit of code for my site that would add a really nice feature, I uploaded it to my webspace, fired my URL into Internet Explorer 7, and low and behold, nothing. Not a damn thing. After several more hours of debugging, I realised that the reason it wasn’t working is SimpleXML requires PHP 5+, which wasn’t a problem for my test server, but my live server was running PHP 4.4.4. Nightmare.
After even more coffee and Googling, I decided to attemp some SQL integration, as my SlideshowPro database was in with my Wordpress database, I figuring there must be a way to read extra data from the database. So I headed on over to www.wordpress.org and had a look around the invaluable docs section, and came across the following function; $wpdb.
Using the $wpdb function allows you to easily read SQL data from the Wordpress database, without the need to re-authenticate or declare the database again. Perfect for the script I had in mind. Below is a sample of the script I used to access the albums in my SlideshowPro table.
 <?php
ÂÂ
  $limit = 8;
  $current = 0;
  $suffix = ‘http://www.tomwaller.co.uk/director/’;
ÂÂ
  $thumblinks = $wpdb->get_results(”SELECT * FROM ssp_albums ORDER BY updated_on DESC”);
  foreach ($thumblinks as $thumblink) {
     $current += 1;
     if ( $current <= $limit ) {
         $albumid = $thumblink->id;
         $albumtn = $thumblink->aTn;
         $albumdesc = $thumblink->name;
         echo ‘<a href=”http://www.tomwaller.co.uk/blog/photos.php#id=’ . $albumid . ‘”> <img src=”‘ . $suffix . $albumtn . ‘” width=”89px” height=”65px” alt=”‘ . $albumdesc . ‘” /> </a>’;
      }
  }
ÂÂ
  ?>
As you can see, I have created a variable called $limit which I have assigned the number 8 to, this is the amout of albums you want displayed on the home page. ‘id’, ‘aTn’ and ‘name’ are the names of the columns I am reading from the SSP table in the database. There is tons of extra info on the $wpdb class in the Wordpress website, but I figured some of you might find this snippet usefull.
