[Devel] r268 - branches/dui

[svn AT agendadulibre.org]

Author: ldayot
Date: Tue May  1 19:16:45 2007
New Revision: 268

Log:
Deplacement des tags dans une table tags.
Adaptation des scripts d'affichage, de saisie et de moderation pour 
cela.
L'afichage de la liste des tags est modifiee en consequence et la taille 
des tags (selon le nombre d'evemenents concerne) est automatique (plus 
besoin de seuils manuels).
Le schema de la base de donnees est modifiee. Ajout des trois tables 
tags, tags_events qui fait la liaison et tags_categories qui est pour 
une utilisation a venir.



Modified:
   branches/dui/funcs.inc.php
   branches/dui/listevents.php
   branches/dui/moderate.php
   branches/dui/schema.sql
   branches/dui/showevent.php
   branches/dui/submit.php
   branches/dui/tags.php

Modified: branches/dui/funcs.inc.php
==============================================================================
--- branches/dui/funcs.inc.php  (original)
+++ branches/dui/funcs.inc.php  Tue May  1 19:16:45 2007
@@ -921,6 +921,7 @@
   {
     $return .= " <option value='{$row->id}' ". ($row->id==$region ? 
"selected='selected' " : ""). ">{$row->name}</option>";
   }
+  $db->freeResult($ret);
   $return .= " </select>\n";
   return $return;
 } // end function selectPlaceHTML()
@@ -930,7 +931,6 @@
 // output if HTML string select part of form
 function selectDurationHTML($daylimit)
 {
-  global $db;
   $return = _("P�riode :") ." ";
   $return .= "<select name='daylimit'>\n";
   $aDurations = array("30"=>_("1 mois"), "91"=>_("3 mois"), "365"=>_("un 
an"), "0"=>_("sans limite"),);
@@ -942,4 +942,30 @@
   return $return;
 }
 
+
+
+// Get tags name for one event
+// Filter for one category, all if null
+function eventTagsList($event_id, $category_id=null)
+{
+  global $db;
+  $query = "SELECT name FROM tags_events LEFT JOIN tags ON tags.id=tag_id 
WHERE event_id='{$event_id}' ". ($category_id!==null ? "AND 
category_id='{$category_id}" : "");
+  $result = $db->query ($query);
+  if ($result == FALSE)
+  {
+    error (_("Erreur lors de la recherche des tags"));
+    put_footer();
+    exit;
+  }
+
+  $return = "";
+  while ($record = $db->fetchObject($result))
+  {
+    $return .= " ".$record->name;
+  }
+  $db->freeResult($result);
+  $return = substr($return, 1);
+  return $return;
+}
+
 ?>

Modified: branches/dui/listevents.php
==============================================================================
--- branches/dui/listevents.php (original)
+++ branches/dui/listevents.php Tue May  1 19:16:45 2007
@@ -58,12 +58,22 @@
   put_footer();
   exit;
 }
+// Find tag in tags table
+$result = $db->query("SELECT id FROM tags WHERE name='{$_GET['tag']}'");
+if (! $record_tag=$db->fetchObject($result)) {
+  error (_("tag introuvable"));
+  put_footer();
+  exit;
+}
+$tag_id=$record_tag->id;
+$db->freeResult($result);
 
 echo "<h2>Les �v�nements <i>" . $_GET['tag'] . "</i></h2>\n";
 $hasevent = FALSE;
 
 $sql = "select id, title, city, start_time, end_time from events " .
-       "where (tags like '%" . $_GET['tag'] . "%') and (start_time > NOW()) 
and (moderated=1) order by start_time ";
+       "LEFT JOIN tags_events ON event_id=events.id ".
+       "where (tag_id='{$tag_id}') and (start_time > NOW()) and 
(moderated=1) order by start_time ";
 $events = $db->query($sql);
 if (! $events)
 {

Modified: branches/dui/moderate.php
==============================================================================
--- branches/dui/moderate.php   (original)
+++ branches/dui/moderate.php   Tue May  1 19:16:45 2007
@@ -67,8 +67,8 @@
     "locality=" .    $db->quote_smart ($locality)                     . ", ".
     "url=" .         $db->quote_smart ($url)                          . ", ".
     "contact=" .     $db->quote_smart ($contact)                      . ", ".
-    "submitter=" .   $db->quote_smart ($submitter)                    . ", ".
-    "tags=" .        $db->quote_smart ($tags)                         . "  ".
+    "submitter=" .   $db->quote_smart ($submitter)                    . /*", 
".
+    "tags=" .        $db->quote_smart ($tags)                         . "  
".*/
     "where id=" .    $db->quote_smart ($id);
 
   $ret = $db->query ($sql);
@@ -79,6 +79,36 @@
       return -1;
     }
 
+
+  // about tags
+  $event_id = $id;
+  // delete old
+  $query = "DELETE FROM tags_events WHERE event_id='$event_id'";
+  $db->query($query);
+
+  // add new
+  // manual tags
+  $aTags = explode(" ", $tags);
+  $aTagsKnown = array();
+  // get tags which already exist
+  $query = "SELECT id, name FROM tags WHERE name IN ('". implode("', '", 
$aTags). "')";
+  $result = $db->query($query);
+  while ($record = $db->fetchObject($result))
+  {
+    $aTagsKnown[]=$record->name;
+  }
+  // Insert new tags
+  if (count($aTagsNew = array_diff($aTags, $aTagsKnown))>0)
+  {
+    $query = "INSERT INTO tags (name) VALUES ('". implode("'), ('", 
$aTagsNew). "')";
+    $db->query($query);
+  }
+  // make link between event and tags
+  $query = "INSERT INTO tags_events (event_id, tag_id) SELECT {$event_id}, 
id FROM tags WHERE name IN ('". implode("','", $aTags). "')";
+  $result = $db->query($query);
+
+  // may check if old tags are almost used, if not, delete them
+
   $oldevent_str =
     format_ascii_event ($db, $oldevent->title, 
date_mysql2timestamp($oldevent->start_time),
                        date_mysql2timestamp($oldevent->end_time),
@@ -287,7 +317,7 @@
                  $event->url,
                  $event->contact,
                  $event->submitter,
-                 $event->tags);
+                 eventTagsList($id));
       echo "</form>\n";
     }
 
@@ -398,7 +428,7 @@
                     $row->url,
                     $row->contact,
                     $row->submitter,
-                    $row->tags,
+                    eventTagsList($row->id),
                     TRUE);
   echo "<input type=\"submit\" name=\"__event_edit\" value=\"�diter\"/>";
   echo "<input type=\"submit\" name=\"__event_accept\" value=\"Accepter\"/>";

Modified: branches/dui/schema.sql
==============================================================================
--- branches/dui/schema.sql     (original)
+++ branches/dui/schema.sql     Tue May  1 19:16:45 2007
@@ -10,7 +10,6 @@
   `url` varchar(255) NOT NULL default '',
   `contact` varchar(255) NOT NULL default '',
   `submitter` varchar(255) NOT NULL default '',
-  `tags` varchar(255) NOT NULL default '',
   `moderated` int(11) NOT NULL default '0',
   PRIMARY KEY  (`id`)
 ) TYPE=MyISAM;
@@ -66,3 +65,33 @@
   url varchar(255) NOT NULL default '',
   PRIMARY KEY  (id)
 ) TYPE=MyISAM AUTO_INCREMENT=2 ;
+
+-- --------------------------------------------------------
+
+CREATE TABLE `tags` (
+  `id` int(11) NOT NULL auto_increment,
+  `category_id` int(11) NOT NULL,
+  `name` varchar(255) NOT NULL,
+  `description` text,
+  PRIMARY KEY  (`id`),
+  KEY `name` (`name`)
+) TYPE=MyISAM;
+
+-- --------------------------------------------------------
+
+CREATE TABLE `tags_categories` (
+  `id` int(11) NOT NULL auto_increment,
+  `name` varchar(255) NOT NULL,
+  `description` text,
+  PRIMARY KEY  (`id`),
+  KEY `name` (`name`)
+) TYPE=MyISAM;
+
+-- --------------------------------------------------------
+
+CREATE TABLE `tags_events` (
+  `event_id` int(11) NOT NULL,
+  `tag_id` int(11) NOT NULL,
+  PRIMARY KEY  (`event_id`,`tag_id`),
+  KEY `tag_id` (`tag_id`)
+) TYPE=MyISAM;

Modified: branches/dui/showevent.php
==============================================================================
--- branches/dui/showevent.php  (original)
+++ branches/dui/showevent.php  Tue May  1 19:16:45 2007
@@ -88,7 +88,7 @@
                     $event->url,
                     $event->contact,
                     $event->submitter,
-                    $event->tags);
+                    eventTagsList($event->id));
 }
 
 put_footer();

Modified: branches/dui/submit.php
==============================================================================
--- branches/dui/submit.php     (original)
+++ branches/dui/submit.php     Tue May  1 19:16:45 2007
@@ -150,7 +150,7 @@
     $submitter = $contact;
 
   /* Checks are done, add to database */
-  $sql = "INSERT INTO events 
(title,description,start_time,end_time,city,region,locality,url,contact,submitter,tags,moderated)
 values (" .
+  $sql = "INSERT INTO events 
(title,description,start_time,end_time,city,region,locality,url,contact,submitter,".
 /*"tags,".*/ "moderated) values (" .
     $db->quote_smart($title)                        . "," .
     $db->quote_smart($description)                  . "," .
     $db->quote_smart(date_timestamp2mysql ($start)) . "," .
@@ -161,7 +161,7 @@
     $db->quote_smart($url)                          . "," .
     $db->quote_smart($contact)                      . "," .
     $db->quote_smart($submitter)                    . "," .
-    $db->quote_smart($tags)                         . "," .
+    /*$db->quote_smart($tags)                         . "," .*/
     "'0')";
 
   $ret = $db->query ($sql);
@@ -172,6 +172,28 @@
       return -1;
     }
 
+  $event_id = $db->insertid();
+
+  // manual tags
+  $aTags = explode(" ", $tags);
+  $aTagsKnown = array();
+  // get tags which already exist
+  $query = "SELECT id, name FROM tags WHERE name IN ('". implode("', '", 
$aTags). "')";
+  $result = $db->query($query);
+  while ($record = $db->fetchObject($result))
+  {
+    $aTagsKnown[]=$record->name;
+  }
+  // Insert new tags
+  if (count($aTagsNew = array_diff($aTags, $aTagsKnown))>0)
+  {
+    $query = "INSERT INTO tags (name) VALUES ('". implode("'), ('", 
$aTagsNew). "')";
+    $db->query($query);
+  }
+  // make link between event and tags
+  $query = "INSERT INTO tags_events (event_id, tag_id) SELECT {$event_id}, 
id FROM tags WHERE name IN ('". implode("','", $aTags). "')";
+  $result = $db->query($query);
+
   alert_moderators ($db, $db->insertid(), $title, $start, $end, 
$description, $city,
                    $region, $locality, $url, $contact, $submitter, $tags);
 

Modified: branches/dui/tags.php
==============================================================================
--- branches/dui/tags.php       (original)
+++ branches/dui/tags.php       Tue May  1 19:16:45 2007
@@ -31,54 +31,51 @@
 
 $db = new db();
 
-$sql = "select tags from events where tags != '' and moderated=1";
-$events = $db->query($sql);
-if (! $events)
+// Build query to get tags and count of events
+$query = "SELECT tags.name as tag, COUNT(events.id) as nb FROM events LEFT 
JOIN tags_events ON event_id=events.id LEFT JOIN tags ON tags.id=tag_id WHERE 
events.moderated=1 GROUP BY tags.id ORDER BY tags.name";
+if (! $result = $db->query($query))
 {
   echo "<p>Erreur lors de la requ�te SQL.</p>";
   put_footer();
   exit;
 }
-
-$tags = array();
-while($event = $db->fetchObject($events))
+// get max count of events
+$maxCountEvent=0;
+while ($record = $db->fetchObject($result))
 {
-  foreach(split(" ", $event->tags) as $tag)
-    {
-      if (! isset($tags[$tag]))
-       $tags[$tag] = 1;
-      else
-       $tags[$tag]++;
-    }
+  if ($maxCountEvent < $record->nb) $maxCountEvent = $record->nb;
 }
+$db->freeResult($result);
+
+// max font size
+$maxFontSize = min($maxCountEvent, 5);
+
+// Replay query
+$result = $db->query($query);
 
 echo "<p style=\"text-align: center; margin-top: 40px; line-height: 
50px;\">";
-ksort($tags);
-foreach($tags as $tag => $count)
+while ($record = $db->fetchObject($result))
 {
-  if ($count < 2)
-    continue;
-
-  if ($count > 60)
-    echo "<font size=\"+5\">";
-  else if ($count > 40)
-    echo "<font size=\"+4\">";
-  else if ($count > 20)
-    echo "<font size=\"+3\">";
-  else
-    echo "<font size=\"+2\">";
-  echo "<a href=\"listevents.php?tag=" . $tag . "\">";
+  $count = $record->nb;
+  $tag   = $record->tag;
+  if ($tag=="" || $count < 2) continue;
+
+  $size = round($count/$maxCountEvent*$maxFontSize);
+  if ($size>1) echo "<font size=\"+{$size}\">";
+  echo "<a href=\"listevents.php?tag=" . $tag. "\">";
   echo $tag;
   echo "</a>";
   echo "<sub style=\"font-size: 10px;\">";
-  echo "<a style=\"font-size: 10px;\" href=\"rss.php?tag=" . $tag . 
"\">rss</a>/";
-  echo "<a style=\"font-size: 10px;\" href=\"ical.php?tag=" . $tag 
."\">ical</a>";
+  echo "<a href=\"rss.php?tag=" . $tag . "\">rss</a>/";
+  echo "<a href=\"ical.php?tag=" . $tag ."\">ical</a>";
   echo "</sub> ";
-  echo "</font>\n";
+  if ($size>1) echo "</font>\n";
   echo "&nbsp;&nbsp;";
-}
+} // end while
 echo "</p>";
 
+$db->freeResult($result);
+
 echo "<p>Seuls les tags portants sur plus d'un �v�nement sont affich�s dans 
cette liste.</p>";
 
 put_footer();