Home Facebook Graph API PHP Load Facebook Albums And Save To MySQL Database

PHP Load Facebook Albums And Save To MySQL Database

This tutorial simply uses Facebook FQL to load all albums of logged in Facebook or albums of friend (via querystring parameter) and save them in MySQL Database to optimize page loading for next visiting as people don’t create new album very often. However, you may need to have a plan to refresh the list.

In this case, I don’t use Facebook API because Facebook just returns 25 albums per request and we have to use previous or next parameter to access its paging. I want to get the whole list in one request.

After store albums in database, we will display them on web page with page navigation (with PHP & MySQL) and resize the height of the Facebook Canvas.

For each album, we will get id, name, number of photos and default photo. Below is the structure of the table we’re going to store those information:

CREATE TABLE IF NOT EXISTS `fb_albums` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `uid` BIGINT(50) NOT NULL,
  `album_id` BIGINT(50) NOT NULL,
  `name` VARCHAR(250) NOT NULL,
  `total_photos` INT(11) NOT NULL,
  `default_photo` VARCHAR(250) NOT NULL,
  `created_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  KEY `idx_fb_albums_uid` (`uid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

PHP Save & Load Facebook Albums

Below is the full source code for what I mention above include Facebook settings, database connection, load albums list with default photo and display them with paging.

Important parts will be highlighted.

<?php
	$start_process = (float) array_sum(explode(' ',microtime()));
 
	ini_set('display_errors', 1);
	error_reporting(E_ALL);
 
	require 'src/facebook.php';
 
	$facebook = new Facebook(array(
	  'appId'  => "_your_app_id_",
	  'secret' => "_your_app_secret_",
	));
 
	$user_id = $facebook->getUser();
 
	if($user_id == 0 || $user_id == "")
	{
		$login_url = $facebook->getLoginUrl(array(
		'redirect_uri'         => "http://apps.facebook.com/rapid-apps/",
		'scope'      => "email,publish_stream,user_hometown,user_location,user_photos,friends_photos,
					user_photo_video_tags,friends_photo_video_tags,user_videos,video_upload,friends_videos"));
 
		echo "<script type='text/javascript'>top.location.href = '$login_url';</script>";
		exit();
	}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
 
<body>
<div id="fb-root">
<p><a target="_top" href="http://apps.facebook.com/rapid-apps/">Back</a></p>
<p><a href="http://4rapiddev.com/facebook-graph-api/php-load-facebook-albums-and-save-to-mysql-database/" target="_blank">Check out the tutorial and source code</a></p>
<?php
	$db_host = "localhost";
    $db_name = "_your_db_name_";
    $db_username = "_your_db_user_";
    $db_password = "_your_db_pass_";
 
    $dbh = mysql_connect($db_host, $db_username, $db_password) or die("Unable to connect to MySQL");
    mysql_query('SET NAMES "utf8"');
    mysql_select_db($db_name, $dbh) or die("Could not select $db_name");
 
	echo "<p>Your Facebook Id: " . $user_id . "</p>";
 
	$total_albums = 0;
 
	$fb_userid = "";
	if(isset($_REQUEST["fb_userid"]) && $_REQUEST["fb_userid"] != "")
		$fb_userid = (int)$_REQUEST["fb_userid"];
 
	if($fb_userid != "")
		$uid = $fb_userid;
	else
		$uid = $user_id;
 
	$sql = "select * from fb_albums where uid='{$uid}'";
 
	$q = mysql_query($sql);
	if(!$r = mysql_fetch_array($q)){		
		$fql = "SELECT aid, name, photo_count, cover_object_id FROM album WHERE owner = '{$uid}'";
 
		$ret_obj = $facebook->api(array(
								   'method' => 'fql.query',
								   'query' => $fql,
								 ));
 
		$total_albums = sizeof($ret_obj);
 
		$album_ids = "";
		$query = "insert into fb_albums(uid, album_id, name, total_photos, default_photo)values";
		$values = "";
		for($i=0;$i<$total_albums;$i++)
		{			
			$photo_count = "";
			if(isset($ret_obj[$i]["photo_count"]) || $ret_obj[$i]["photo_count"] != ""){
				$photo_count = $ret_obj[$i]["photo_count"];
			}
 
			$cover_object_id = "";
			$cover_photo = "";
			$default_photo = "";
			if(isset($ret_obj[$i]["cover_object_id"]) && $ret_obj[$i]["cover_object_id"] != "0"){
				$cover_object_id = $ret_obj[$i]["cover_object_id"];
				$cover_photo = $facebook->api("/{$cover_object_id}");
				$default_photo = $cover_photo["picture"];
			}
			$values .= "('{$uid}', '" . $ret_obj[$i]["aid"] . "', '" . mysql_escape_string($ret_obj[$i]["name"]) . "', '" . $photo_count . "', '" . stripslashes($default_photo) . "')," ;
		}
 
		if($values != ""){
			$values = substr($values, 0, strlen($values) -1);
			$values .= ";";
		}
 
		$query .= $values;
		mysql_query($query);
	}
	else
	{
		$total_albums = mysql_num_rows($q);
	}
 
	echo "total_albums: " . $total_albums . "<br>";
 
	$page = 0;
 
	if(isset($_REQUEST["page"]) && $_REQUEST["page"] != "")
		$page = (int)$_REQUEST["page"];
	if($page == 0)$page = 1;
 
	$page_size = 5;
 
	$total_pages = ceil($total_albums/$page_size);
 
	if($page > $total_pages)
		$page = $total_pages;
 
	$start = $total_pages > 0 ? (( $page * $page_size ) - $page_size) : 0;
	$end = $start + $page_size;
	$start = $start <= 1 ? 0 : $start;
	$sql .=" LIMIT $start, $page_size";
 
	$sql = "select * from fb_albums where uid='{$uid}' LIMIT $start, $page_size";
 
	$q = mysql_query($sql);
	$album_table = "";
	$album_table .= "<table border='1'>";
	$album_table .= "<tr><th>Album ID</th><th>Name</th><th>Total Photos</th><th>Default Photo</th></tr>";
	while($r = mysql_fetch_assoc($q))
	{
		$album_table .= "<tr><td>" . $r["album_id"] . "</td><td>" . $r["name"] . "</td><td>" . $r["total_photos"] . "</td><td align='center'><img src='" . $r["default_photo"] . "'></td></tr>";
	}
	$album_table .= "</table>";
 
	echo "<h3>Your Facebook Album List ({$total_albums} albums)</h3>";
	echo $album_table;
 
	echo create_page_navigation($total_albums, $page, $page_size, "http://apps.facebook.com/rapid-apps/load-albums.php", "fb_userid=" . $uid);
 
	function create_page_navigation($total_record, $current_page_, $item_per_page, $url, $query_string)
    {
        $current_page = $current_page_ + 1;
        $pagerange = 10;
        $num_pages = ($total_record % $item_per_page == 0) ? ceil($total_record / $item_per_page) : ceil($total_record / $item_per_page);
 
        $rangecount = 0;
        $table = "<table>";
		$table .= "<tr>";
        $table .= "<td>{$current_page_ } / {$num_pages} </td>";
        if ($current_page_ > 1)
        {
            $tmp = $current_page_ - 1;
            $table .= "<td><a target='_top' href='" . $url . "?page=" . $tmp . "&&" . $query_string . "'><</a></td>";
        }
 
        if ($num_pages > 1)
        {
            $rangecount = ceil($num_pages / $pagerange);
 
            $startpage = 0;
            $count = 0;
            for ($i = 1; $i < $rangecount + 1; $i++)
            {
                $startpage = (($i - 1) * $pagerange) + 1;
                $count = min($i * $pagerange, $num_pages);
                if ((($current_page >= $startpage) && ($current_page <= ($i * $pagerange))))
                {
                    for ($j = $startpage; $j < $count + 1; $j++)
                    {
                        if ($j == $current_page_)
                        {
                            $table .= "<td><b>" . $j . "<b></td>";
                        }
                        else
                        {
                            $table .= "<td><a target='_top' href='" . $url . "?page=" . $j . "&&" . $query_string . "'>" . $j . "</a></td>";
                        }
                    }
                }
            }
        }
        if ($current_page_ < $num_pages)
        {
            $tmp2 = $current_page_ + 1;
            $table .= "<td><a target='_top' href='" . $url . "?page=" . $tmp2 . "&&" . $query_string . "'>></a></td>";
        }
        $table .= "</tr></table>";
        return $table;
    }
?>
<p><a href="http://4rapiddev.com/facebook-graph-api/php-load-facebook-albums-and-save-to-mysql-database/" target="_blank">Check out the tutorial and source code</a></p>
<script src="http://connect.facebook.net/en_US/all.js"></script>
<script type="text/javascript">
	FB.Canvas.setSize({ width: 520, height: 1500 });
</script>
</body>
</html>
<?php
	$end_process = (float) array_sum(explode(' ',microtime()));
	echo "<p>Execution time: ". sprintf("%.4f", ($end_process-$start_process))." seconds</p>";
?>
Facebook Albums List Paging

Facebook Albums List Paging

Note:

  • Line 10,11, 39-42: replace Facebook and database configurations with yours.
  • Line 53-54: check if load albums of logged in user or friend
  • Line 65-72: use Facebook FQL to load the albums list of a particular Facebook ID
  • Line 75,92,101: implement a insert SQL statement to store all albums into database
  • Line 80-82: ensure the number of albums is larger than 0 as some albums don’t have any photo
  • Line 87-91: similar with photo_count, default photo (cover photo) will not exist if the album has no photo. On line 89,90 we use another Facebook API function to get url of the default photo based on its ID
  • Line 141: display the albums in a table.
  • Line 143: display the page navigation based on number of albums of the user
  • Line 199: resize the height of the Canvas

+ View demonstration on my Facebook page – Save And Load Album List Link
+ Download the PHP source code above as well as the current Facebook PHP SDK

Hoan Huynh
PHP Get Likes Number Of Facebook PageC# Save Web Page URL To Image
You Might Also Like:
Hoan Huynh

Hoan Huynh is the founder and head of 4rapiddev.com. Reach him at [email protected]

9 years ago Facebook Graph API, , 377