[Devel] r268 - branches/dui
svn at agendadulibre.org
svn at agendadulibre.org
Mar 1 Mai 19:16:46 CEST 2007
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 " ";
-}
+} // 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();
Plus d'informations sur la liste de diffusion Devel