hands on xml-rpc : copying msql tables

I don’t have anything to blog on, so I will bore you all with a quick generic function to copy mysql tables from one host to another, using xml-rpc.

I use the Incutio xml-rpc library on both hosts, to handle the tedious stuff (xml formatting and parsing). That leaves only some snippets to send and receive table data and store it on a mysql database.

First : how to handle the table data on the sending end:

  • I take an associative array from a mysql query
  • I make an array to hold the records
  • I add each row as array
  • I make an IXR-client.
  • I add some general parameters
  • I hand these and the entire table array to my IXR-client.
  • send…
//the snippet with the client is at the bottom of the post
$ThisClient = New SerpClient('http://serp.trismegistos.net/db/xmlrpc.php', 'user', 'pass', 'sender');

$tablename = "serp_tags_keys";
$tableid = "id";
$result = $serpdb->query("SELECT * FROM ".$tablename);
$recordcount = mysql_num_rows($result);

while($row=mysql_fetch_assoc($result)) {
	foreach($row as $key => $value) $record[$key]=$value;

$ThisClient->putTable($tablename, $recordcount, $tableid, $records);

I consider some additional fields necessary for basic integrity checks : I add “ID” as key field, so on the receiving end the server knows which field is my table’s auto-increment field. Other fields are a username, password, tablename and the batch recordcount.

The IXR_Client then generates a tangled mess of xml-tags holding the entire prodecure call and data. (you can put the client on ‘debug’, then it dumps the generated xml to the screen).

The first part of the xml file contains the single parameters :

  • username
  • password
  • tablename
  • recordcount
  • id-field


Then the entire table is sent as one parameter in the procedure call.

That parameter is built from an array containing the table rows as ‘struct’. If I want to use the routine for any table, I need the fieldname-value pairs to compose a standard mysql insert statement. A struct type allows me to use key-value pairs in the xml-file that can be parsed back into an array.



<member><name>cat</name><value><string>alexa ranking seo internet ranking internet positi</string></value></member>

<member><name>cat</name><value><string>firm seo</string></value></member>



That was the last of the param holding the table, so the entire tag-mess is closed :


Then the second part : on the receiving end the Incutio class parses the whole tag-mess, and hands an array of the param sections as input to my function putTable.

	function putTable($args) 
		$user 	 = $args[0];
		$pass 	 = $args[1];
		$tname 	 = $args[2];
		$tcount	 = $args[3];
		$id 	         = $args[4];	
		$table 	 = $args[5];

$table is a straightforward array holding as items an array ($t) created from the struct with the pairs of fieldname-value. I turn the recordsets key-value struct into a mysql INSERT query :
$query = “INSERT INTO `”.$tname.”` (” field, field… “) VALUES (” fieldvalue, fieldvalue “)”;

All I have to do is add the fieldnames and fieldvalues to the mysql insert query.

		foreach($table as $t) {

//the fixed parts
				$query0 = 'INSERT INTO `'.$tname.'` (';
				$query2 .=") VALUES (";

//make the (`fieldname`, `fieldname`, `fieldname`) query-bit 
//and the ('fieldvalue', 'fieldvalue', 'fieldvalue') query-bit :

				foreach($t as $key=>$value) {
					if($key!=$id) {	
						$query1 .="`".$key."`, ";
						$query3 .="'".$value."', ";

//remove the trailing ", "
				$query1=substr($query1, 0, strlen($query1)-2);
				$query3=substr($query3, 0, strlen($query3)-2);

//glue em up and add the final ")"
				$query0 .= $query1.$query2.$query3.")";


//reset the strings

that generates mysql queries like
INSERT INTO `serp_tags_keys` (`tag`, `cat`, `date`) VALUES (‘ranking’, ‘alexa ranking’, ‘200901’) and copies the entire table.

That is how I handle the table data.

Of course I have to define two custom classes to process the serp.putTable procedure itself, using the Incutio class.

First the class for the sending script, which is pretty straight forward :

  • make an IXR_Client instance
  • hand the record set to it
  • have it formatted and sent
//include the library

//make a custom class that uses the IXR_client
Class SerpClient 
	var $rpcurl;         //endpoint
	var $username;   //you go figure
	var $password;
	var $bClient;      //incutio ixr-client instance
	var $myclient;  //machine/host-id
	   function SerpClient($rpcurl, $username, $password, $myclient)
	$this->rpcurl	= $rpcurl;
    if (!$this->connect()) return false; 

    	//Standard variables to send in the message
	$this->rpcurl	= (string) $rpcurl;
    	$this->username = (string) $username;
    	$this->password = (string) $password;
	$this->myclient = (string) $myclient;
    	return $this;
   		function connect() 
//basic client, it takes the endpoint url, tests and returns true if it exists
    	if($this->bClient = new IXR_Client($this->rpcurl)) return true;
//the function I use to send the data
		function putTable($tablename, $recordcount, $tableid, $array) 
//first parameter is always the methodname, then the parameters, which are
//added sequential to the xml-file (with the appropriate tags for datatypes.
//the script figures that out. note : it uses htmlentities on strings.
		$this->bClient->query('serp.putTable', $this->username, $this->password, $tablename, $recordcount, $tableid, $array);


I use it in the snippets above with :

$ThisClient = New SerpClient('http://serp.trismegistos.net/db/xmlrpc.php', 'user', 'pass', 'sender');
$ThisClient->putTable($tname, $tcount, $tableid, $records);

Then, on the receiving end, my program has to know how to handle the xml containing the remote procedure call.

I define an extension on IXR_server and pass serp.putTable as new ‘method’ (callback function).

//go away cookie...
$_COOKIE = array();

//make sure you get the posted crap, the ixr instances grabs it input from it
if ( !isset( $HTTP_RAW_POST_DATA ) ) $HTTP_RAW_POST_DATA = file_get_contents( 'php://input' );

//include the library

//make an extended class
class serp_xmlrpc_server extends IXR_Server {

//use the same function name...

	function serp_xmlrpc_server() {

//build an array of methods : 
//first the procedurename you use in the xml-text,
//then which function in the extended class (this one) it maps to 
//to be used as $this->method

		$this->methods = array('serp.putTable'	 => 'this:putTable');

//hand em to the IXR server instance that will map it as callback

//now IXR_Server instance uses ($this->)putTable 
//to process incoming xml-text 
//containing serp.putTable as methodname

		function putTable($args) 
//(for routine : see the snippet above to store the xml data in mysql)

//make the class instance like any regular get-post php program, 
//the only actual program line, that instantiates the extended class,
//which handles the posted xml 

$serp_xmlrpc_server = new serp_xmlrpc_server();

That’s all. I am not going to list a cut-and-paste version. You have to build some stuff with it, then you will come up with lots of stuff you can do with it.

WordPress and iPhone built a plugin that receives pictures from iPhone. WordPress uses Incutio so you can ‘piggyback’ on that and have an iPhone plugin for your own website in two days flat using an ajax lightbox gallery script. Or go monetize small websites with some seo oriented ‘optimisation’ functions like ChangeFooterLinks(array($paidurl, $anchortext)) :) or whatever… boring, isn’t it ?

google trends II

I wanted to reply to a question elsewhere on the site, but a ‘comment’ box isn’t fit for it so I’ll put the reply here. The question was about creating ‘search engine friendly’ descriptive URL’s based on keywords from the Google Trends atom feed, listing pages a graph of the trend.

You can get a site to list http://domain.com/trend_title.html type url’s by using mod_rewrite, an apache module.

In the server directory of the application you can use an .htaccess file to set rules for file access in these folders. When the server gets request from browsers or servers it applies any rewriting rules you define in .htaccess to these requests.

I tried this one :

	RewriteEngine On
	RewriteCond %{REQUEST_FILENAME} !-f
	RewriteCond %{REQUEST_FILENAME} !-d
        RewriteRule ^(.*).html /trendinfo.php?title=$1 

RewriteEngine On
sets the rewrite mechanism on

RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d

tell the apache server that rewriteconditions apply to file-requests that are not an existing file (F) or directory (D). If the requested filename is anywhere in the servers file table, the server dishes out that file, otherwise it will try to apply a RewriteRule. Applying the rule generates a new request, if that returns anything, the server dishes that out, otherwise it returns an htpp-404 ‘file not found’.

The actual url rewrite rule is :
RewriteRule ^(.*).html /trendinfo.php?title=$1
which means :

  • if any filename is requested that satisfies the mask ^(.*).html then
  • take everything before .html
  • add that as variable $1 to trendinfo.php?title=$1
  • see if it sticks

If the browser requests http://domain.com/bob+bowersox.html, the server will assert it is not a file or directory on the server, and test the available rules. When it notices it the requested file ends with .html, it applies the rewrite rule and tries to access http://domain.com/trendinfo.php?title=bob+bowersox.

A browsing user does not notice a thing.

In trendinfo.php I wrote some code to handle the ‘new’ request :

if(!isset($_REQUEST['title'])) {
//if there is no $1, added as title, fake a 404 "file not found" message 
        echo 'the emptiness...';
} else {
//get the title from the request
  $mytitle=htmlentities($_REQUEST['title'], ENT_QUOTES, "UTF-8");
//put the google trends graph url together
  $graphurl = 'http://www.google.com/trends/viz?hl=&q=';
  $graphurl .= urlencode($mytitle);
  $graphurl .= '&date=';                        //leave date blank to get the current graph
  $graphurl .= '&graph=hot_img&sa=X';
  echo "";

…that outputs the Google trend graph on the url http://domain.com/bob+bowersox.html

You can also put this in index.php :

		$feed = simplexml_load_file('http://www.google.com/trends/hottrends/atom/hourly');
		$children =  $feed->children('http://www.w3.org/2005/Atom');
		$parts = $children->entry;
		foreach ($parts as $entry) {
		  	$details = $entry->children('http://www.w3.org/2005/Atom');
	 	 	 $dom = new domDocument(); 
		  	 $anchors = $dom->getElementsByTagName('a'); 
				foreach ($anchors as $anchor) { 
		 			$url = $anchor->getAttribute('href'); 
	 				$urltext = $anchor->nodeValue;
					echo ''.$urltext.' ';

That lists the current 100 google trends with a link. If you use the .htaccess rewrite rules, the server reroutes all the links to trendinfo.php with descriptive urls.

I hope that helps.

an xml rpc endpoint

(geek content:) Integrating the Incutio xml rpc class into a phpLinkDirectory install opens a lot of possibilities for running remote control automated networks. For a basic example I took the submit routine of phpLD and the xml-rpc routine from wordpress, deleted all nonsense, and ended up with a simple xml-rpc endpoint for my link directory.

On the sender side, I make an xml file that holds the methodName (which is the function I want to execute remotely : phpld.SubmitLink), and the array values I want to pass to the function as parameters. I attach the xml-string as post to a curl call and fire it at the xmlrpc endpoint.

function getmyxml() {

//make the $data array
//normally phpLd makes it when someone submits a site

//put the data array in an xml string to post to the xmlrpc endpoint

//make the xml header
	$myxml='< ?xml version="1.0" encoding="UTF-8"?>';
	$myxml.='< methodCall>';
	$myxml.=	'< methodName>phpld.SubmitLink< /methodName>';
	$myxml.='< params>';
//loop to add the $data elements as param-tags
	foreach($data as $d) {
		$myxml.='< param>';
		$myxml.='< value>< string>'.trim($d).'< /string>< /value>';
		$myxml.='< /param>';

//finish the xml file :
	$myxml.='< /params>';
	$myxml.='< /methodCall>';

//return it
	return $myxml;

//make the call to the endpoint 
    $ch = curl_init('http://www.domain.com/xmlrpc.php');
//use content-type text/xml as extra header
    curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type: text/xml'));
//get the xml-string and use it as post var
    curl_setopt($ch, CURLOPT_POSTFIELDS, getmyxml());
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
    curl_setopt($ch, CURLOPT_TIMEOUT, 1);
    $return = curl_exec($ch);

So much for the sending end, now the receiving end : the xmlrpc.php endpoint file. I need the Incutio xml handler and the phpLinkdirectory database class, with the proper settings, and a function SubmitLink to add the data in the posted xml-file as a record to the database.

Some general settings :

define('XMLRPC_REQUEST', true);
$_COOKIE = array();
if ( !isset( $HTTP_RAW_POST_DATA ) ) $HTTP_RAW_POST_DATA = file_get_contents( 'php://input' );

I include the Incutio class file, which is a general xml client/server class, and I include init.php from the phpLD script. In the init.php script the AdoDb database instance is declared, that handles the mysql connection, as well as the basic phpLd environment.

$xmlrpc_logging = 0;

Let’s put them both to work for me : the IXR_Server has to know it has to run the SubmitLink function if that function is set as methodName in the xml file. In php you can add methods dynamically to a class, and in the function call($methodname, $args) the IXR_Server handles callbacks.

In my phpLD_xmlrpc_server class, which extends IXR_Server, I add a function SubmitLink that stuffs the data in the phpld database, and I make an array with xml.methodName=>class:function as key=>value pair and pass that to the IXR_Server, that’s enough.

class phpLD_xmlrpc_server extends IXR_Server {

	var $methods=array();

	function phpLD_xmlrpc_server() {

//mapping the custom methods
	   $this->methods['phpld.SubmitLink'] = 'this:SubmitLink';

//handing the custom methods to the base class

When the IXR class parses the posted xml and finds the phpld.SubmitLink methodName, it executes the custom method ($this->)SubmitLink with the data posted in the xml param tags as input :

function SubmitLink($params) {

//use db and tables from include(init.php)
	global $db;
	global $tables;

//map the param-values passed to the $data array 
//pass the data array to the adodb $db instance's Replace function : 
		if ($db->Replace($tables['link']['name'], $data, 'ID', true) > 0) {
			return $data['DESCRIPTION']." entered";
		} else {
			return " refused";

The AdoDb function Replace maps key-value to tablefield-value, so I use the database field names as keys for the $data array, and assign the $param-values to them. I pass the $data array to the adoDb function and that takes care of the rest.

After adding the includes, settings, and classes,
all I have to do is add a final call at the end to start a new instance of the extended class to handle the posted xml-data :

$phpLD_xmlrpc_server = new phpLD_xmlrpc_server();

…and I have an xml-rpc endpoint :

define('XMLRPC_REQUEST', true);
$_COOKIE = array();
if ( !isset( $HTTP_RAW_POST_DATA ) ) $HTTP_RAW_POST_DATA = file_get_contents( 'php://input' );

$xmlrpc_logging = 0;

class phpLD_xmlrpc_server extends IXR_Server {

	var $methods=array();
	function phpLD_xmlrpc_server() {
	   $this->methods['phpld.SubmitLink'] = 'this:SubmitLink';

	function SubmitLink($args) {

	global $db;
	global $tables;

	$data['STATUS']         = $args[0];
	$data['IPADDRESS']      = $args[1];
	$data['VALID']          = $args[2];
	$data['LINK_TYPE']      = $args[3];

		if ($db->Replace($tables['link']['name'], $data, 'ID', true) > 0) {
			return $data['DESCRIPTION']." entered";
		} else {
			return " refused";
$phpLD_xmlrpc_server = new phpLD_xmlrpc_server();

Short and sweet.

The actual strength of xml-rpc is in defining a set of standard methodNames and parameters passed with the methodCall (for instance for small website maintenance tasks and statistics reporting, an rpc.sms protocol as successor to Twitter, or standard socialgraph functions for ajax/javascript/widgets) for developing standard API’s.