Tags:    php mysql

1. Introduktion

Denne artikel viser, hvordan et tagsystem kan implementeres i PHP.

Artiklen er opdelt i sektioner:

• Sektion 1: Introduktionen til tagsystemet, denne sektion beskriver tagsystemet og viser den hele koden for systemet. Og introducerer et eksempel der bruges i resten af artiklen.


• Sektion 2: Introducerer brugen af tags baseret på sæt teori.


• Sektion 3: Eksempler på brugen af systemet.


• Sektion 4: Beskriver implementeringen af systemet.

Hele koden for systemet er vist i 1.1 og koden for databasetilgangs implementeringen er vist i 1.2.

 <?php
 class Tag{
   public $id;
   public $name;
   public $appearances;
   public $weight;
   public function Tag($id,$name,$appearances){
     $this->id=$id;
     $this->name=$name;
     $this->appearances=$appearances;
   }
 }
 
 
 class AIterator implements Iterator, Countable{
   protected $source;
 
   public function key() {return key($this->source);}
 
   public function current(){
     return current($this->source);
   }
 
   public function next(){
     next($this->source);
     return $this->current();
   }
 
   public function rewind(){
     return reset($this->source);
   }
 
   public function valid(){
     if(!$this->current()){
       return false;
     }
     return true;
   }
 
   public function count(){
     return count($this->source);
   }
 }
 
 class TagFilter extends AIterator{
   private $filter = array();
   public function __construct(){
     $this->source = &$this->filter;
   }
 
   public function isIncluded($tag){
     return isset($this->filter[$tag->id]) && $this->filter[$tag->id] == TagSystem::INCLUDED;
   }
 
   public function includeTag($tag){
     $this->changeState($tag, TagSystem::INCLUDED);
   }
 
   public function isCouldcluded($tag){
     return isset($this->filter[$tag->id]) && $this->filter[$tag->id] == TagSystem::COULDCLUDED;
   }
   
   public function couldcludeTag($tag){
     $this->changeState($tag, TagSystem::COULDCLUDED);
   }
 
   public function isExcluded($tag){
     return isset($this->filter[$tag->id]) && $this->filter[$tag->id] == TagSystem::EXCLUDED;
   }
 
   public function excludeTag($tag){
     $this->changeState($tag, TagSystem::EXCLUDED);
   }
 
   public function isFree($tag){
     return !isset($this->filter[$tag->id]);
   }
 
   public function freeTag($tag){
     if(!$this->isFree($tag)){
       unset($this->filter[$tag->id]);
     }
   }
 
   private function changeState($tag, $state){
     $this->filter[$tag->id] = $state;
   }
 
   public function fromArray($arr){
     $this->filter = $arr;
   }
 
   public function toArray($arr){
     return $this->filter;
   }
 
   public function sessionField($field){
     if(!isset($_SESSION[$field])){
       $_SESSION[$field] = array();
     }
     $this->filter = &$_SESSION[$field];
     $this->source = &$this->filter;
   }
 }
 
 class TagSystem extends AIterator{
   const INCLUDED = 1;
   const EXCLUDED = 2;
   const COULDCLUDED = 3;
 
   private $_tagnames=array();
   private $_tagids=array();  
   public  $db;
   private $_tablePrefix = "";
   private $_entityId="entityid";
   private $totalTags=0;
   public $distribuition=array(0,10,30,60,90);
 
   public function __construct(){
     $this->source = &$this->_tagids;
   }
   
   public function makeFilter(){
     return new TagFilter();
   }
 
   public function setTableNamePrefix($prefix){
     $this->_tablePrefix.= $prefix;
   }
 
   public function setEntityId($id){
     $this->_entityId = $id;
   }
 
   public function getTagTableName(){
     return $this->_tablePrefix.'tags';
   }
 
   public function getTagUseTableName(){
     return $this->_tablePrefix.'taguses';
   }
 
   public function applyFilter($filter, $entityTable,$entityId='entityId',$getFields='*',$order=''){
     $res=$this->db->query(
            $this->getFilterSql($filter, 
                                $entityTable,
                                $entityId, 
                                $getFields, 
                                $order));
     return $res;
   }
 
   public function getRelatedEntitiesFilter($entityTable, $toid){
     $res = $this->getEntityTags($toid);
     $filter = $this->makeFilter();
     foreach($res as $tag){
       $filter->couldcludeTag($tag);
     }
     return $filter;
   }
 
   public function getRelatedEntities($entityTable, $toid,$entityId='entityId',$getFields='*'){
     $filter = $this->getRelatedEntitiesFilter($entityTable, $toid);
     if(count($filter) == 0){
       return array();
     }
 
     $sep = '';
     $cosql = '';
     foreach($filter as $id => $state){
       if(!$this->hasTagById($id)){
         continue;
       }
       $cosql .= $sep . '`tagid`='.$id;
       $sep = ' OR ';
     }
 
     $csql='SELECT count(*) AS c,`'.$this->_entityId.'` AS entityId FROM `'.$this->getTagUseTableName().'` WHERE '.$this->_entityId.' !='.$toid.' AND ('.$cosql.') GROUP BY entityId';
 
     $sql = 'SELECT o.'.str_replace(',',', o.',$getFields).' FROM `'.$entityTable.'` AS o'. 
            ' INNER JOIN ('.$csql.') AS c ON(c.entityId=o.'.$entityId.') WHERE c.c>0 ORDER BY c.c DESC';
 
     return $this->db->query($sql);
   }
 
   public function orphanCheck($entityTable, $entityId="entityId"){
     $entries_res = $this->db->query('SELECT utt.tagid AS id,count(*) AS c 
                                      FROM `'.$this->getTagUseTableName().'` AS utt 
                                      LEFT JOIN `'.$entityTable.'` AS et ON utt.'.$this->_entityId.' = et.'.$entityId.' 
                                      WHERE et.'.$entityId.' IS NULL GROUP BY utt.'.$this->_entityId);
     $entries=array();
     foreach($entries_res as $e){
       $entries[$e['id']]=$e['c'];
     }
     return $entries;
   }
 
   public function deleteOrphans($entityTable,$entityId="entityId"){
     $entries_res=$this->db->query('SELECT utt.tagid AS tagid,utt.'.$this->_entityId.' AS uid
                                    FROM `'.$this->getTagUseTableName().'` AS utt 
                                    LEFT JOIN `'.$entityTable.'` AS et ON `utt.'.$this->_entityId.'` = `et.'.$entityId.'` 
                                    WHERE `et.'.$entityId.'` IS NULL');
     foreach($entries_res as $e){
       $this->db->query('DELETE FROM '.$this->getTagUseTableName().'
                         WHERE tagid='.$e['tagid'].' AND `'.$this->_entityId.'`='.$e['uid']);
     }
     $entries_res=$this->db->query('SELECT tt.tagid AS tagid 
                                    FROM `'.$this->getTagTableName().'` AS tt 
                                    LEFT JOIN `'.$this->getTagUseTableName().'` AS utt ON tt.tagid=utt.tagid 
                                    WHERE utt.tagid IS NULL');
 
     foreach($entries_res as $e){
       $this->db->query('DELETE FROM `'.$this->getTagUseTableName().'` WHERE tagid='.$e['tagid']);
     }
   }    
 
   private function make($id,$name,$appear){
     $o=new Tag($id,$name,$appear);
     $this->_tagnames[$name]=&$o;
     $this->_tagids[$id]=&$o;     
   }
 
   public function load(){
     $this->totalTags=0;
     $res=$this->db->query('SELECT ht.tagid,ht.name,COUNT(htu.'.$this->_entityId.') AS appear 
                            FROM `'.$this->getTagTableName().'` AS ht
                            LEFT JOIN `'.$this->getTagUseTableName().'` AS htu ON(ht.tagid=htu.tagid)
                            GROUP BY ht.tagid');
     if($res === FALSE){
       return false;
     }
     foreach($res as $row){
       $this->make($row['tagid'],$row['name'],$row['appear']);
       $this->totalTags+=$row['appear'];
     }
 
     $this->calculateWeights();
     return true;
   }
 
   public function calculateWeights(){
     foreach($this->_tagids as $k=>&$v){
       $weight=0;
       $percent=$v->appearances/($this->totalTags/100);
       foreach($this->distribuition as $roof){
         if($percent>$roof){
           $weight++;
         }
       }
       $v->weight=$weight;
     }
   }
 
   public function getEntityTags($id){
     $res=$this->db->query('SELECT tagid FROM `'.$this->getTagUseTableName().'` WHERE `'.$this->_entityId.'`='.$this->db->make_safe($id));
 
     $arr=array();
     foreach($res as $row){
       $arr[]=$this->_tagids[$row['tagid']];
     }
     return $arr;
   }
 
   public function getEntitiesTags($ids){
     $str="";
     $sep="";
     foreach($ids as $id){
       $str.=$sep.'`'.$this->_entityId.'`='.$this->DB->make_safe($id);
       $sep=' OR ';
     }
     $res=$this->db->query('SELECT * FROM `'.$this->getTagUseTableName().'` WHERE '.$str);
 
     $arr=array();
     foreach($res as $row){
       if(!array_key_exists($row[$this->useId],$arr)){
         $arr[$row[$this->useId]]=array();
       }
       $arr[$row[$this->useId]][]=$this->tagids[$row['tagid']];
     }
     return $arr;
   }
 
   public function deleteEntityTags($id){
     $this->db->query('DELETE FROM '.$this->getTagUseTableName().' WHERE `'.$this->_entityId.'`='.$this->DB->make_safe($id));
     $res=$this->db->query('SELECT t.name,t.tagid,tu.c as c FROM `'.$this->getTagTableName().'` AS t
                            LEFT JOIN (SELECT tagid,count(*) as c FROM `'.$this->getTagUseTableName().'` 
                            GROUP BY tagid) AS tu ON(tu.tagid=t.tagid) 
                            WHERE tu.c IS NULL');
     $ids='';
     $ids_sep='';
     foreach($res as $row){
       $ids.=$ids_sep.'tagid='.$row['tagid'];
       $ids_sep=' OR ';
       unset($this->_tagids[$row['tagid']]);
       unset($this->_tagnames[$row['name']]);
     }
     $res=$this->db->query('DELETE FROM `'.$this->getTagTableName().'` WHERE '.$ids);
   }
 
   public function getEntityTagsAsString($id,$sep){
     $res=$this->db->query('SELECT tagid FROM `'.$this->getTagUseTableName().'` WHERE `'.$this->_entityId.'`='.$this->db->make_safe($id));
     $str='';
     $s='';
     foreach($res as $row){
       $str.=$s.$this->_tagids[$row['tagid']]->name;
       $s=$sep;
     }
     return $str;
   }
 
   public function associateTagsWith($id,$tagNameList){
     $sql='INSERT INTO `'.$this->getTagUseTableName().'`(tagid,`'.$this->_entityId.'`) VALUES';
     $sep='';
     foreach($tagNameList as $name){
       $sql.=$sep;
       if(array_key_exists($name, $this->_tagnames)&&$this->_tagnames[$name]!=null){
         $sql.=$this->db->make_tuple($this->_tagnames[$name]->id,$id);
       }else{
         $sql.=$this->db->make_tuple($this->addTag($name),$id);
       }
       $sep=',';
     }
     $this->db->query($sql);
   }
 
   public function addTag($name){
     $this->db->query('INSERT INTO `'.$this->getTagTableName().'`(name) VALUES'.$this->db->make_tuple($name));
     $id=$this->db->insertId();
     $this->make($id,$name,1);
     return $id;
   }
 
   private function getFilterSql($filter, $entityTable,$entityId, $getFields,$order){
     $sfilter=array();
     $efilter=array();  
     $cfilter=array();
     foreach($filter as $tagId => $state){
       if(!$this->hasTagById($tagId)){
         continue;
       }
       $str=" tagid='".$tagId."' ";
       switch($state){
         case 1:$sfilter[]=$str;break;
         case 2:$efilter[]=$str;break;
         case 3:$cfilter[]=$str;break;
       }
     }
 
     $selsql="";
     $exsql="";
     $csql="";
     $include_not_empty = 1;
     $exclude_not_empty = 2;
     $couldclude_not_empty = 4;
     $state=0;
     if(count($sfilter)>0){
       $state+=$include_not_empty;
       $selsql='SELECT count(*) AS c,`'.$this->_entityId.'` AS entityId FROM `'.$this->getTagUseTableName().'` WHERE '.implode(' OR ',$sfilter).' GROUP BY entityId';
     }
 
     if(count($efilter)>0){
       $state+=$exclude_not_empty;
       $exsql='SELECT count(*) AS c,`'.$this->_entityId.'` AS entityId FROM `'.$this->getTagUseTableName().'` WHERE '.implode(' OR ',$efilter).' GROUP BY entityId';
     }
 
     if(count($cfilter)>0){
       $state+=$couldclude_not_empty;
       $csql='SELECT count(*) AS c,`'.$this->_entityId.'` AS entityId FROM `'.$this->getTagUseTableName().'` WHERE '.implode(' OR ',$cfilter).' GROUP BY entityId';
     }
 
     $order=($order!='')?' ORDER BY o.'.$order.' DESC':'';
     $entityTableSQL = 'SELECT o.'.str_replace(',',', o.',$getFields).' FROM `'.$entityTable.'` AS o ';
 
     switch($state){
       case 0:
         $sql=$entityTableSQL.$order;
       break;
       case $couldclude_not_empty:
         $sql=$entityTableSQL.' INNER JOIN ('.$csql.') AS c ON(c.entityId=o.'.$entityId.') WHERE c.c>0'.$order;
       break;
       case $exclude_not_empty:
         $sql=$entityTableSQL.' LEFT JOIN ('.$exsql.') AS e ON(e.entityId=o.'.$entityId.') WHERE e.entityId IS NULL'.$order;
       break;
       case $exclude_not_empty & $couldclude_not_empty:
         $sql=$entityTableSQL.'
              INNER JOIN 
              (SELECT c.entityId FROM 
                 ('.$csql.') AS c 
                LEFT JOIN 
                ('.$exsql.') AS e 
                ON(e.entityId =c.entityId) 
                WHERE c.c>0 AND e.entityId IS NULL
              ) AS f 
              ON(f.entityId=o.'.$entityId.')'.$order;
       break;
       case $include_not_empty:
         $sql=$entityTableSQL.' INNER JOIN ('.$selsql.') AS s ON(s.entityId=o.'.$entityId.') WHERE s.c='.count($sfilter).$order;  
       break;
       case $couldclude_not_empty & $include_not_empty: 
         $sql=$entityTableSQL.
              'INNER JOIN 
               (SELECT s.entryid FROM 
               ('.$selsql.') AS s 
               LEFT JOIN 
                 ('.$csql.') AS c 
                 ON(c.entityId =s.entityId) 
               WHERE s.c='.count($sfilter).' AND c.c>0
               ) AS f 
               ON(f.entityId=o.'.$entityId.')'.$order;
       break;
       case $exclude_not_empty & $include_not_empty:
         $sql=$entityTableSQL.
              'INNER JOIN 
               (SELECT s.'.$this->entityId.' FROM 
               ('.$selsql.') AS s 
               LEFT JOIN 
               ('.$exsql.') AS e 
               ON(e.entityId = s.entityId) 
               WHERE s.c='.count($sfilter).' AND e.entityId IS NULL
                 ) AS f 
             ON(f.entityId = o.'.$entityId.')'.$order;
       break;
       case $couldclude_not_empty & $exclude_not_empty & $include_not_empty:
         $sql=$entityTableSQL.
              'INNER JOIN 
               (SELECT s.entityId FROM 
               ('.$selsql.') AS s 
               LEFT JOIN
               (SELECT c.entityId FROM 
               ('.$csql.') AS c 
               LEFT JOIN 
                 ('.$exsql.') AS e 
               ON(e.entityId = c.entityId) 
               WHERE c.c>0 AND e.entryid IS NULL
               ) AS p
               ON(p.entityId = s.entityId)
               WHERE s.c='.count($sfilter).'
             ) AS f 
             ON(f.entityId =o.'.$entityId.')'.$order;
       break; 
     }
     return $sql;
   }
 
   public function getFreeTagsInResult($filter, $entityTable,$entityId='entityId'){
     if(count($filter) == 0){
       return $this;
     }
 
     $res=$this->applyFilter($filter, $entityTable, $entityId, $entityId);
     $ids=array();
     foreach($res as $row){
       $ids[]=$this->_entityId.'=\''.$row[$entityId].'\'';
     }
     if(count($ids)== 0){
       return array();
     }
     $ntags= array();
     foreach($filter as $tag_id=>$state){
       $ntags[]="tagid!='".$tag_id."'";
     }
 
     $res=$this->db->query('SELECT tagid 
                            FROM `'.$this->getTagUseTableName().'` 
                            WHERE ('.implode(' OR ',$ids).')'.((count($ntags)>0)?' AND ('.implode(' AND ',$ntags).')':'').' 
                            GROUP BY tagid');
 
     $tags=array();
     foreach($res as $row){
       $tags[]=$this->_tagids[$row['tagid']];
     }
     return $tags;
   }
 
   public function getTagById($id){
     return $this->_tagids[$id];
   }
 
   public function hasTagById($id){
     return isset($this->_tagids[$id]);
   }
 
   public function getTagId($name){
     return $this->_tagnames[$name]->id;
   }
 
   public function hasTagByName($name){
     return isset($this->_tagnames[$name]);
   }
 
   public function install(){
     $res = $this->db->query('CREATE TABLE IF NOT EXISTS `'.$this->getTagUseTableName().'`(
                      `'.$this->_entityId.'` INT NOT NULL ,
                      `tagid` INT NOT NULL ,
                      PRIMARY KEY ( `'.$this->_entityId.'` , `tagid` )
                      );');
     if($res === false){
       return false;
     }
     $res = $this->db->query('CREATE TABLE IF NOT EXISTS `'.$this->getTagTableName().'` (
                     `tagid` int(11) NOT NULL AUTO_INCREMENT,
                     `name` varchar(50) NOT NULL,
                      PRIMARY KEY (`tagid`),
                      UNIQUE KEY `name` (`name`)
                      );');
                      
     return $res !== false;
   }
 }
 ?>

Kodeblok 1.1: Tagsystemet.

 <?php
 class MySQLResult implements Iterator{
   private $resource;
   private $row = null;
 
   public function __construct($resource){
     $this->resource = $resource;
   }
 
   public function key() { } // Not Implemented
 
   public function current(){
     if($this->row != null){
       return $this->row; 
     }
   }
 
   public function next(){
     $this->row= mysql_fetch_assoc($this->resource);
   }
 
   public function rewind(){
     if($this->count() != 0){
       mysql_data_seek($this->resource, 0);
     }
     return $this->next();
   }
 
   public function valid(){
     if($this->row == null) {
       return false;
     }
     return true;
   }
 
   public function count(){
     return mysql_num_rows($this->resource);
   }
 
 }
 
 class MySQL{
   private $link;
 
   public function query($sql){
     $res = mysql_query($sql, $this->link);
     if(mysql_errno()){
       return false;
     }
     if($res !== TRUE && $res !== FALSE){
       return new MySQLResult($res);
     }else{
       return $res;
     }
   }
   public function queryP($sql){
     print $sql;return $this->query($sql);
   }
   
   public function connect($host, $user, $pass, $db){
     $this->link = mysql_connect($host, $user, $pass);
     mysql_select_db($db);
     return mysql_errno() == 0;
   }
 
   public function make_safe($value){
     if(!is_numeric($value)){
       $value = '\'' . mysql_real_escape_string($value,$this->link) . '\'';
     }
     return $value;
   }
 
   public function make_tuple(){
     $tuple='';
     $tuple_sep='';
     foreach(func_get_args() as $arg){
       $tuple.=$tuple_sep.$this->make_safe($arg);
       $tuple_sep=',';
     }
     return '('.$tuple.')';
   }
 
   public function insertId(){
     return mysql_insert_id($this->link);
   }
 }
 ?>

Kodeblok 1.2: MySQL databasetilgangslaget.


1.1. Hvad er et tagsystem?
Et tagsystem er et system, der bruger tags til at beskrive en entitet. En entitet kan have mange tags og et tag kan blive tildelt til mange entiteter.

1.2. Hvad er et tag?
Et tag er et ord eller en sekvens af ord. I tilfældet af at det er flere ord bruger der normal et punktum (.) til at adskille de enkelte ord i stedet for et mellemrum.

1.3. Hvad er en entitet?
En entitet er en samling af data der beskriver et objekt.

1.4. Hvorfor bruge et tagsystem?

• Hvis tags bliver visualisere vægtet, så kan brugerne af systemet se hvilke tags der er mest og mindst brugte og dermed for et hurtigt indblik i hvad entiteterne der bliver tagget mest handler om.


• Tags kan bruges til at generer og raffinerer søgninger. Brugeren kan bestemme, hvilke tags der skal, kan eller ikke skal være i entiteterne i søgningen


• Tags fungerer som automatisk kategorisering af entiteter.

1.5. Eksempel billeder af biler.
Et brug at et tagsystem kunne være at give billeder af for eksempel biler tags. Dette er brugbart da brugerne så kan lave søgninger, så de kun ser billeder der opfylder visse kriterier

Uden et tagsystem eller et tilsvarende kategoriserings system ville det være tilnærmelsesvis umuligt at lave søgninger på billeder, da dette så ville kræve et system der skannede billederne.


Figur 1.1: Ferrari sportsvogn.

Figur 1.2: Porche sportsvogn.

Figur 1.3: VW familiebil.



Vedhæftede filer:

• source.rar (1195 hits)

Hvad synes du om denne artikel? Giv din mening til kende ved at stemme via pilene til venstre og/eller lægge en kommentar herunder.

Del også gerne artiklen med dine Facebook venner:  

Karma barometer (30 dage)

IT jobs | Kontakt | Om Udvikleren.dk | Om karma | IT nyheds arkiv | Annoncering

X