Extracting Data From WordPress Database and Convert Into XML Format

Recently I had to develop a dynamic website that lets the user update company information and upload product images to image gallery. I didn’t give a second that about which CMS I am going to use. The answer was obvious; wordpress. But I was faced with a problem. The site was costume designed by a web designer and the design was already approved. The photo gallery was designed keeping flash in mind. There for I couldn’t rely on the available plug-ins and I have never before developed a wordpress plugin. I didn’t seem to have time to learn to develop a plug-in. So I decide to take another root. I had to generate a XML file by directly querying the wordpress database. Then the flash could easily access the XML file to get all the information it needs to do its job.

Each album in the photo-gallery was a single post filed under a particular category; in my case under the category ‘Gallery’. All picture uploaded into a particular post was to be part of that album. The pictures had to only be uploaded to the post, it was not required to be placed in the post. That is, the post content was blank. The post only had a title (Album name) and uploaded files.

Understanding WordPress Database

To query the wordpress database, first its necessary to understand the wordpress database structure.

I have used slugs to identify the categories so that in case I have to change the category name I don’t have to change anything in the code. The slug defined in the ‘wp_terms’ table. I search for a slugs that I am interested and get back the ‘term_id’ from the ‘wp_terms’ table.

In my case I had 3 categories of photo gallery. Each one was a assigned a meaningful slug.

$_Gallery_Slugs = array();
$_Gallery_Slugs['0']["slug"] = "interiordesign"; //Categories for posts with photos of Interior Design Photos
$_Gallery_Slugs['1']["slug"] = "exhibitions";  //Categories for posts with photos of Exhibition Photos
$_Gallery_Slugs['2']["slug"] = "architectureoutdoor";  //Categories for posts with photos of Architecture and Out-Door Design Photos

//XML tags for each category
$_Gallery_Slugs['0']["XML"] = "Interior";
$_Gallery_Slugs['1']["XML"] = "Exhibition";
$_Gallery_Slugs['2']["XML"] = "Architecture";

require_once("../../../wp-config.php"); // Include the config file to get confing variables.

$DBLink = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD); // Connect to the database
	if (!$DBLink) {
		die('Could not connect: ' . mysql_error());
	}

	if(!mysql_select_db(DB_NAME))
	{
		die("Couldn't connect to database table");
	}

Steps to retrieve the post and post pictures belonging to specific slug

//Get the unique ID of all the slugs that are of interest to us.
for ($i=0; $i < count($_Gallery_Slugs); $i++) {
	$SqlTermId = "SELECT * FROM wp_terms WHERE slug='".$_Gallery_Slugs[$i]["slug"]."'";
	$ResTermId = Query_Database($SqlTermId);

        // Get the taxonomy ID for every Slug using the Term (Slug) ID
	while ($rowTermId = mysql_fetch_assoc($ResTermId)) {

                // Save the Slug IDs extracted in the parent/previous loop
		$_Gallery_Slugs[$i]["termId"] = $rowTermId["term_id"];
		$SqlTermTax = "SELECT * FROM wp_term_taxonomy WHERE term_id=".$_Gallery_Slugs[$i]["termId"];
		$ResTermTax = Query_Database($SqlTermTax);

		// Get the post IDs that belong to each of the slug/category
		while ($rowTermTax = mysql_fetch_assoc($ResTermTax)) {

			// term_taxonomy_id is retrieved in the previous query from table 'wp_term_taxonomy'
			$_Gallery_Slugs[$i]["termTax"] = $rowTermTax["term_taxonomy_id"];
			$SqlObjId = "SELECT * FROM wp_term_relationships WHERE term_taxonomy_id=".$_Gallery_Slugs[$i]["termTax"]."  ORDER BY object_id DESC";
			$ResObjId = Query_Database($SqlObjId);

			//var CntPosts=0;
			$PostsCnt = 0;

			// Get the post details
			while ($rowObjId = mysql_fetch_assoc($ResObjId)) {

				// Save the post IDs from the previous query from table wp_term_relationships
				$_Gallery_Slugs[$i]["PostIds"][$PostsCnt] = $rowObjId["object_id"];
				$SqlAlbum = "SELECT * FROM wp_posts WHERE ID=".$_Gallery_Slugs[$i]["PostIds"][$PostsCnt];//get the album post details
				$ResAlbum = Query_Database($SqlAlbum);
				while ($rowAlbum = mysql_fetch_assoc($ResAlbum)) {
					// Save the post title
					$_Gallery_Slugs[$i]['Album'][$PostsCnt] = $rowAlbum["post_title"];
				}
				$PostsCnt++;
			}

		}

	}

}

At the end of the above script the variable '$_Gallery_Slugs' would look some think like this.

/* - Example of how the variable might look after above script - */

$_Gallery_Slugs['0']["slug"] => "interiordesign";
$_Gallery_Slugs['1']["slug"] => "exhibitions";
$_Gallery_Slugs['2']["slug"] => "architectureoutdoor";

// Slug/Category term_id for each group
$_Gallery_Slugs['0']["XML"] => "Interior";
$_Gallery_Slugs['1']["XML"] => "Exhibition";
$_Gallery_Slugs['2']["XML"] => "Architecture";

// Slug/Category term_taxonomy_id for each group
$_Gallery_Slugs['0']["termId"] => 1;
$_Gallery_Slugs['1']["termId"] => 2;
$_Gallery_Slugs['2']["termId"] => 3;

// Slug/Category term_taxonomy_id for each group
$_Gallery_Slugs['0']["termTax"] => 5;
$_Gallery_Slugs['1']["termTax"] => 6;
$_Gallery_Slugs['2']["termTax"] => 7;

// post IDs belonging to each category
$_Gallery_Slugs['0']["PostIds"][0]=> 1;
$_Gallery_Slugs['0']["PostIds"][1]=> 3;
$_Gallery_Slugs['0']["PostIds"][2]=> 9;
$_Gallery_Slugs['0']["PostIds"][3]=> 14;

$_Gallery_Slugs['1']["PostIds"][0]=> 2;
$_Gallery_Slugs['1']["PostIds"][1]=> 4;
$_Gallery_Slugs['1']["PostIds"][2]=> 5;

$_Gallery_Slugs['2']["PostIds"][0]=> 2;
$_Gallery_Slugs['2']["PostIds"][1]=> 4;
$_Gallery_Slugs['2']["PostIds"][2]=> 5;
$_Gallery_Slugs['2']["PostIds"][3]=> 10;
$_Gallery_Slugs['2']["PostIds"][4]=> 11;
$_Gallery_Slugs['2']["PostIds"][5]=> 13;

Now that we have all the posts that are under the specific slugs/category, we have to construct the XML file.

// Construct the XML file
echo "\n\n";
for($i=0; $i < count($_Gallery_Slugs); $i++){
	// Loop through each category of photo gallery, in my case 3
	echo "\t<".$_Gallery_Slugs[$i]['XML'].">\n"; // Get the tagnames defined at the top
	for($j=0; $j < count($_Gallery_Slugs[$i]["PostIds"]); $j++){
		// for each post, get the attachments, which are also in the wp_posts table
		$SqlImgs = "SELECT * FROM wp_posts WHERE post_type='attachment' AND post_parent=".$_Gallery_Slugs[$i]["PostIds"][$j];

		$ResImgs = Query_Database($SqlImgs);
		echo "\t\t\n";

		while ($rowImgs = mysql_fetch_assoc($ResImgs)) {
			echo "\t\t\t\n";
				echo "\t\t\t\t".$rowImgs["guid"]."\n";
				echo "\t\t\t\t".htmlspecialchars($rowImgs["post_excerpt"])."\n";
			echo "\t\t\t\n";
		}

		echo "\t\t\n";
	}
	echo "\t\n";
}
echo "";

Now the output XML file would look like this

	
		
			
				http://www.TheWebsiteUrl/wp-content/uploads/2010/08/living_309-33.jpg
				home design
			
			
				http://www.TheWebsiteUrl/wp-content/uploads/2010/08/Living_309-43.jpg
				home design
			
			
				http://www.TheWebsiteUrl/wp-content/uploads/2010/08/Room1-4_resize1.jpg
				 
			
		
		
			
				http://www.TheWebsiteUrl/wp-content/uploads/2010/08/1.jpg
				offiec design
			
			
				http://www.TheWebsiteUrl/wp-content/uploads/2010/08/GM-room.jpg
				offiec design
			
		
		
			
				http://www.TheWebsiteUrl/wp-content/uploads/2010/07/P1040395_resize1.jpg
				apina house ปิ่นเกล้า
			
		
	
	
		
			
				http://www.TheWebsiteUrl/wp-content/uploads/2010/07/P1010156_resize-Small.jpg
				bildersmart
			
		
		
			
				http://www.TheWebsiteUrl/wp-content/uploads/2010/07/g3_resize-Small.jpg
				ธอส.
			
		
		
			
				http://www.TheWebsiteUrl/wp-content/uploads/2010/07/DSCN4963_resize-Small1.jpg
				wilson
			
		
	
	
		
			
				http://www.TheWebsiteUrl/wp-content/uploads/2010/07/1-copy_resize_resize1.jpg
				new design
			
		
	

This output of the php script file can now be loaded by flash like an XML file.