Difference between revisions of "CakePHPAjaxTagging"

Latest revision as of 23:26, 4 November 2007

about

this is the code we use in http://search.freegamedev.net to make the ajax based tagging system

upper row : assigned tags. click on - removes them instantly with ajax, click on name can open a custom search page

middle row : textentry field : press add to assign a new custom tag, automatically updates upper row using ajax.

bottom row : suggested tags, this list is updated automatically during typing in the middle row, and displays up to twenty tags starting with the string in the textentry field.

LICENSE : this code is available under MIT-License

sql

CREATE TABLE `myitems` (
  `id` int(10) unsigned NOT NULL auto_increment,
  .... YOUR FIELDS ....
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

CREATE TABLE `myitems_tags` (
  `myitem_id` int(10) unsigned NOT NULL,
  `tag_id` int(10) unsigned NOT NULL,
  `is_auto_assigned` tinyint(4) NOT NULL,
  PRIMARY KEY  (`myitem_id`,`tag_id`),
  KEY `is_auto_assigned` (`is_auto_assigned`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


CREATE TABLE `tags` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ;

app/views/layouts/default.thtml

add this to the head section of your layout after installing the js lib from http://script.aculo.us/ into app/webroot/js/

<?php if (isset($javascript)) echo $javascript->link('prototype'); ?>
<?php if (isset($javascript)) echo $javascript->link('scriptaculous.js?load=effects'); ?>
<?php if (isset($javascript)) echo $javascript->link('controls'); ?>

views/elements/taglist.thtml

you'll need images for the + and - , here named app/webroot/img/add.png and app/webroot/img/remove.png

<div class='taglist'>
<?php
// ensure that action is only add or del
if($action != "add" && $action != "del")$action = "add";

// its possible to give only one tag as a string instead of a list
if(!is_array($tags))$tags = array($tags);

// icon
if($action == "add")$img = $html->image("add.png", array("title"=>"add", "alt"=>"add", "class"=>"icon"));
else $img = $html->image("remove.png", array("title"=>"remove", "alt"=>"remove", "class"=>"icon"));

foreach($tags as $tagname){
	echo "<div class='tag'>";
	echo $ajax->link($img,"/myitems/".$action."Tag/".$id."/".$tagname,array('update'=>$id_taglist),null,false);
	echo $html->link($tagname,"/myitems/tag/".$tagname,array('update'=>$id_taglist));
	echo "</div>";
}

?>
</div>

app/views/myitems/view.thtml

<?php
	require_once("mylib.php");
?>

... YOUR DISPLAY CODE ...

<?php 
	$tagref_id = $myitem['Myitem']['id'];
	
	echo '<div id="ID_taglist">';
	echo $this->renderElement('taglist',
		array("tags"=>$tags,"id"=>$tagref_id,"action"=>"del","id_taglist"=>"ID_taglist"));
	echo '</div>';

	echo $ajax->form(array("action"=>"/myitems/addTag/".$tagref_id),'post',array('update'=>"ID_taglist"))."\n";
	echo $html->input("Myitem/newTagName")."\n";
	echo $html->submit('Add Tag')."\n";
	echo $ajax->observeField("MyitemNewTagName",
		array("frequency"=>1,"update"=>"ID_tagsuggestlist","url"=>"/myitems/tagAutoComplete/".$tagref_id))."\n";
	echo "</form>"."\n";

	echo '<div id="ID_tagsuggestlist"></div>';
?>

app/controllers/myitems_controller.php

<?php
require_once("mylib.php");

uses('sanitize');

class MyitemsController extends AppController 
{
	var $name = "Myitems";
	
	var $components	= array('Pagination','RequestHandler','Cloud'); // Added
	var $helpers	= array('Pagination','Html', 'Javascript', 'Ajax'); // Added 

	function beforeFilter(){
		if ($this->RequestHandler->isAjax()) {$this->layout = 'ajax';}
	}
	
    function view($id = null) {
		$id = intval($id);
		
		// .... YOUR DATA READ CODE   ... $this->set('myitem',SOMETHING); ....
		
		// read out taglist and sets them
		$tagref_id = $id;
		$this->listTags($tagref_id);
    }
	
	function tagAutoComplete ($id) {
		$search = addslashes(trim($this->params["form"]["data"]["Myitem"]["newTagName"]));
		$this->set("id",$id);
		$this->set("tags",sqlgetonetable("SELECT `name` FROM `tags` WHERE `name` LIKE '".$search."%' LIMIT 20"));
	}
	
	function listTags ($myitem_id) {
		$myitem_id = intval($myitem_id);
		$this->set("id",$myitem_id);
		$this->set("tags",sqlgetonetable("SELECT `tags`.name FROM `tags`,`myitems_tags`
			WHERE `tags`.id = `myitems_tags`.tag_id AND `myitems_tags`.`myitem_id` = $myitem_id"));
	}
	
	function addTag ($myitem_id,$tag_name=null) {
		if ($tag_name == null) $tag_name = $this->params["form"]["data"]["Myitem"]["newTagName"];
		if ($tag_name) SetTag($myitem_id,$tag_name);
		$this->listTags($myitem_id);
	}
	
	function delTag ($myitem_id,$tag_name) {
		$tag_id = sqlgetone("SELECT * FROM `tags` WHERE ".sqlAND(array("name"=>$tag_name)));
		if ($tag_id) sql("DELETE FROM `myitems_tags` WHERE ".sqlAND(array("myitem_id"=>$myitem_id,"tag_id"=>$tag_id)));
		$this->listTags($myitem_id);
	}
	
	// .... YOUR OTHER CONTROLLERS METHODS ....
};
?>

mylib.php

<?php
function SanitizeTagName	($tag_name) { return ereg_replace("[^a-z0-9_]","_",strtolower(trim($tag_name))); }
function GetOrCreateTagID	($tag_name)	{ return sqlCreateOrUpdate("tags",array("name"=>SanitizeTagName($tag_name))); }

function SetTag ($myitem_id,$tag_name,$bAutoAssigned=false)	{ 
	$tag_id = GetOrCreateTagID($tag_name);
	$update = $bAutoAssigned?"is_auto_assigned=1":false;
	sqlCreateOrUpdateNoID("myitems_tags",array("myitem_id"=>$myitem_id,"tag_id"=>$tag_id),$update); 
}

$gRobLibMysqlConnected = false;

/// list($host,$user,$pass,$db) = RobGetMySQLConfig();
function RobGetMySQLConfig () {
	$cfg = new DATABASE_CONFIG(); // cakephp
	$arr = $cfg->default;
	return array($arr["host"],$arr["login"],$arr["password"],$arr["database"]);
}

function array2object	($arr) {
	$r = false;
	foreach($arr as $key => $val) $r->{$key} = $val;
	return $r;
}
function array2obj		($arr) { return array2object($arr); }
function arr2obj		($arr) { return array2object($arr); }
function object2array	($obj) { return get_object_vars($obj); }
function obj2array		($obj) { return get_object_vars($obj); }

// generate save sql assignment from object `c` = '6' , `d` = '7'
function obj2sql ($obj,$div=" , ") {
	if (!$obj) return "";
	return arr2sql(get_object_vars($obj),$div);
}

// generate save sql assignment from array `c` = '6' , `d` = '7'
function arr2sql ($arr,$div=" , ") {
	if (!$arr) return "";
	$parts = array();
	foreach($arr as $key => $val)
		if (!is_array($val) && !is_object($val))
			$parts[] = "`".$key."` = '".addslashes($val)."'";
	return implode($div,$parts);
}

function sqlAND ($arr) { return arr2sql($arr," AND "); }
function sqlSET ($arr) { return arr2sql($arr," , "); }

/// sqlquery, exit on failure
function sql	($query) {
	$r = sqltry($query);
	if (!$r) exit("MYSQL QUERRY FAILED : ####<br>".$query."<br>".mysql_error()."<br>####");
	return $r;
}

/// sqlquery, returns false on failure
function sqltry	($query) {
	global $gRobLibMysqlConnected;
	if (!$gRobLibMysqlConnected) {
		$gRobLibMysqlConnected = true;
		list($host,$user,$pass,$db) = RobGetMySQLConfig();
		mysql_connect($host,$user,$pass) or exit("Could not connect to database ".$host." with ".$user);
		mysql_select_db($db) or exit("Could not select database ".$db);
	}
	$r = mysql_query($query);
	return $r;
}

/// returns mysql_insert_id() or $idfield
/// $unique is encoded using arr2sql(), the fieldnames have to be a mysql-unique group to work correctly
/// if $update is an array, it is encoded using arr2sql() , it can also be an already encoded string or null
function sqlCreateOrUpdate ($tablename,$unique,$update=null,$idfield="id") {
	$tablename = "`".addslashes($tablename)."`";
	if ($update && is_array($update)) $update = arr2sql($update);
	if (sqltry("INSERT INTO $tablename SET ".arr2sql($unique).($update?" , $update":""))) return mysql_insert_id();
	if ($update) sql("UPDATE $tablename SET $update WHERE ".arr2sql($unique," AND "));
	return $idfield ? sqlgetone("SELECT `".addslashes($idfield)."` FROM $tablename WHERE ".arr2sql($unique," AND ")) : false;
}
// sqlCreateOrUpdate for tables not having an id field, e.g. associations
function sqlCreateOrUpdateNoID ($tablename,$unique,$update=null) { sqlCreateOrUpdate($tablename,$unique,$update,false); }

// get a whole sql table as array of objects
function sqlgettable ($query,$keyfield = false) {
	$r = sql($query);
	$arr = array();
	if ($keyfield)	while ($o = mysql_fetch_object($r)) $arr[$o->{$keyfield}] = $o;
	else			while ($o = mysql_fetch_object($r)) $arr[] = $o;
	return $arr;
}

// get a single sql object
function sqlgetobject ($query) { return mysql_fetch_object(sql($query)); }

// returns value of first column of first row
function sqlgetone ($query) {
	$r = sql($query);
	if (!$r) return false;
	$r = mysql_fetch_array($r);
	if (!$r) return false;
	return $r[0];
}


// get a whole sql table as array with the values of the first column
function sqlgetonetable ($query,$keyindex=false,$valueindex=0) {
	$r = sql($query);
	$arr = array();
	if ($keyindex !== false) 
			while ($o = mysql_fetch_array($r)) $arr[$o[$keyindex]]	= $o[$valueindex];
	else	while ($o = mysql_fetch_array($r)) $arr[]				= $o[$valueindex];
	return $arr;
}
?>