Dealing with Flickr Data

27 08 2010

I wanted to create a database on my local box of various flickr pics related primarily to flowers. My plan was to use it in creating a menu system that would be populated with the data. To implement the menu system, I created some tables with the primary one having the following schema:

--
-- Database: `flickrflowers`
--
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `flowers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `color` tinyint(20) NOT NULL,
  `type` tinyint(50) NOT NULL,
  `title` varchar(150) NOT NULL,
  `picid` varchar(50) NOT NULL,
  `url` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `color` (`color`,`type`,`title`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=28 ;

As you will note from the above, the flowers table has a field that is supposed to hold a url, namely a url so that you may embed a user’s flickr photo in a webpage. This sort of url needs to be created first and it is based on the picture’s image id. You can easily locate that id when you browse a picture on flickr and glance at its url.

Here is one such url I found after searching for butterflies on flickr:

http://www.flickr.com/photos/chrisser/198890974/

The numbers before the terminal slash mark represent the photo id which you can copy and use to get information about that picture. Stringing together selected pieces from the large amount of data returned will allow you to construct a url that you can use for embedding your flickr selection — more about that shortly.

Interestingly, wordpress apparently has functionality such that you can use a flickr url, like that above as is! By removing the code tags from the above url, automatically wordpress converts the url to the embedded url format and displays the image, as follows:

Pretty Monarch Butterfly 003 - Danaus Plexippus

If you right-click on the image (photo courtesy of Chrisser’s Photostream) and open the link in a new window, note its “farm” style url. This is a special url for interfacing with a flickr service using REST, an acronym that stands for Representational State Transfer. In short, you provide a url with certain parameters and flickr will give you back data.

If for example, you wanted to construct such a url with respect to the above picture, here’s one way to do it and retrieve the data:

$photo_id = '198890974';
$flickr_api_key = 'your flickr api key';

$url = 'http://api.flickr.com/services/rest/';
$url .= '?method=flickr.photos.getInfo';
$url .= '&photo_id=$photo_id';
$url .= '&api_key=$flickr_api_key';
$url .= '&format=php_serial';
echo '<pre>';
var_dump($url);

The information returned will contain more than what we need for our intended purpose. If you string together certain pieces to form a query string with a specified url format, the result will be the desired url for embedding the image. Doing this sort of thing manually, can be tedious. So, I’ve come up with a couple of scripts using PHP, MySQL and OOP to automate creating the embedded url and storing it along with a picture’s title in the database, as follows:

The Class

<?php
 /**
   * DataHandler
   * class to create flickr img url and store in database
 **/
class DataHandler
{
    private $arrPic_Ids;
	private $infoUrl = 'http://api.flickr.com/services/rest/';
	private $picData;
	private $imgUrl;
	private $title;
	private $photoID;

	/**
	  * __construct()
	  * @param $stmt PDO object
	  * initializes $arrPic_Ids with query results
	**/
	function __construct( $stmt )
	{
	   try {
 			if ( $stmt->execute() ) {
				while ( $row = $stmt->fetch( PDO::FETCH_OBJ ) )
	   			{
					$this->arrPic_Ids[] =  $row->picid;
	   			}
			}
			else
			{
				throw new PDOException("unexecutable query $sql");
			}
		} catch (PDOException $e) {
    		print "Error!: " . $e->getMessage() . "
";
    		die();
		}
	}

	/**
	  * getPicIds()
	  * returns all the pic ids in database
	**/
	function getPicIds()
	{
		return $this->arrPic_Ids;
	}

	/**
	  * buildDataRecord()
	  * @param $photo_id string
	  * uses $photo_id to construct a special url to get info about image
	  * and uses url to get the pic data and store it in $picData
	**/
	function buildDataRecord( $photo_id )
	{
		 $this->infoUrl .= '?';
		 $a   = array(	'method' => 'flickr.photos.getInfo',
	                    'photo_id' => $photo_id,
						'api_key' => 'your api key goes here',
						'format' => 'php_serial'
						);
		 foreach ($a as $key => $value)
		 {
		     $this->infoUrl .= $key  . '=' . $value . '&';
		 }
		 $this->picData = unserialize( file_get_contents( $this->infoUrl ) );
	}

	/**
	  * getPicData()
	  * returns the built out data in $picData
	**/
	function getPicData()
	{
	    return $this->picData;
	}

	function imgURLConstructor()
	{
	        $data = $this->getPicData();
			$imgUrl = 'http://farm';
			$photoData = $data['photo'];
			$pieces   = array($photoData['farm']. '.static.flickr.com/',
		                  $photoData['server'] . '/',
						  $photoData['id'] . '_',
						  $photoData['secret']. '_m.jpg');

			foreach ($pieces as $p) {
		   		$imgUrl .= $p;
			} // end foreach
        	echo 'Created Image Url ' . $imgUrl . '
';
			$this->photoID = $photoData['id'];
			$this->imgUrl = $imgUrl;
			$this->title = $photoData['title']['_content'];
	}
	function getImgTitle()
	{
	      return array($this->imgUrl, $this->title);
	}
	function updateDataBase ($stmt)
	{
			$stmt->bindParam(':title',$this->title,PDO::PARAM_STR, 200);
			$stmt->bindParam(':url',$this->imgUrl,PDO::PARAM_STR,200);
			$stmt->bindParam(':pid',$this->photoID,PDO::PARAM_STR,100);
			if ( $stmt->execute() ) {
				echo '<p>Record updated</p>';
			}
			else
			{
				echo '<p>Unable to update record.</p>';
			}
	}
}

The above script assumes that you have a flickr API key. If you don’t have one, you can get one for free at flickr.com.

The Client Code

<?php
require("./DataHandler.class.php");

$user = 'root';
$pass = '';

$DataHandler = null;

$sql = "SELECT `picid` FROM `flowers` WHERE url IS NULL or trim(url) = ''";

try {
        // get all the pic ids missing img url ...
    	$conn = new PDO('mysql:host=localhost;dbname=flickrflowers', $user, $pass);
		$stmt = $conn->prepare( $sql );
		$DataHandler = new DataHandler( $stmt );

		// update ea incomplete record
		 $sql = 'UPDATE flowers SET title=:title, url=:url where picid=:pid';
		foreach ( $DataHandler->getPicIds() as $photo_id)
		{
			$DataHandler->buildDataRecord(  $photo_id );
            $DataHandler->imgURLConstructor();
			list($title,$imgUrl) = $DataHandler->getImgTitle();
			$stmt = $conn->prepare( $sql );
			$DataHandler->updateDataBase( $stmt );
		}// end for
		$conn = null;
} catch (PDOException $e) {
    	print "Error!: " . $e->getMessage() . "
";
    	die();
}

The above script clearly shows the beauty of object oriented programming (OOP). I’ll confess that initially I wrote the code in a procedural form and while it worked, I felt it was confusing to follow. OOP let’s you improve code organization and at the same time simplifies the code. I have far fewer lines returning values or passing parameters than when I wrote the procedural version.

You may wonder why I pass around $stmt but not $conn. The first variable is an object so you can pass it around easily. However, the next one contains a reference to the database connection which is a resource and resources cannot be passed.

All that is need to do after copying the schema above is to view a picture on flickr, note its id and copy that id into the picid field. If you use phpMyAdmin, you may not see any difference after running the scripts. If no error occurred, then the most likely reason is that you need to refresh your browser for phpMyAdmin to show the changes.

One of my favorite things to do is code refactoring, so if you have any suggestions about how you would suggest improving the code in either script, please leave a comment.

This work is licensed under a Creative Commons License
.

Advertisements

Actions

Information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




%d bloggers like this: