Redid multiple tag system

This new tag system has a seperate table for tags. This way, a tag cloud can be made much more easily than if using the previous system.

This changeset requires manual maintinence.

6 files changed, 90 insertions, 26 deletions

@@ -115,9 +115,15 @@ function postBlogPost($title,$author,$tags,$content)
 {
         $slug = generateSlug($title,'updates');
 
-        $inspost = "INSERT INTO updates (title,slug,author,tags,text) VALUES (\"" . $title . "\",\"" . $slug . "\",\"" . $author . "\",\"" . $tags . "\",\"" . addslashes($content) . "\")";
+        $inspost = "INSERT INTO updates (title,slug,author,text) VALUES (\"" . $title . "\",\"" . $slug . "\",\"" . $author . "\",\"" . addslashes($content) . "\")";
         $inspost2 = mysql_query($inspost);
 
+        $getpost = "SELECT * FROM updates WHERE slug = \"" . $slug . "\"";
+        $getpost2 = mysql_query($getpost);
+        $getpost3 = mysql_fetch_array($getpost2);
+
+        addTags($getpost3['id'], $tags);
+
         $upconf = "UPDATE config SET value = \"" . date('md') . "\" WHERE name = \"lastUpdate\"";
         $upconf2 = mysql_query($upconf);
 
@@ -144,7 +150,7 @@ function postBlogPost($title,$author,$tags,$content)
                         if (preg_match('/<LINK REL="pingback" HREF="([^"]+)"/i',$page_data,$server))
                         {
                                 $client = new xmlrpc_client($server[1]);
-                                $msg = new xmlrpcmsg("pingback.ping", array(    new xmlrpcval('http://www.fourisland.com/blog/' . $slug . '/', 'string'),
+                                $msg = new xmlrpcmsg("pingback.ping", array(    new xmlrpcval('http://fourisland.com/blog/' . $slug . '/', 'string'),
                                                                                 new xmlrpcval($link, 'string')));
                                 $client->send($msg);
                         }
@@ -153,7 +159,7 @@ function postBlogPost($title,$author,$tags,$content)
 
         $client = new xmlrpc_client('http://rpc.pingomatic.com');
         $msg = new xmlrpcmsg("weblogUpdates.ping", array(       new xmlrpcval('Four Island', 'string'),
-                                                                new xmlrpcval('http://www.fourisland.com/', 'string')));
+                                                                new xmlrpcval('http://fourisland.com/', 'string')));
         $client->send($msg);
 }
 
@@ -215,4 +221,34 @@ function verifyUser($username, $password)
         return (($_POST['username'] != '') && ($getuser3['username'] == $_POST['username']));
 }
 
+function getTags($id, $type = 'published')
+{
+        $gettags = "SELECT * FROM tags WHERE post_id = " . $id . " AND post_type = \"" . $type . "\"";
+        $gettags2 = mysql_query($gettags);
+        $i=0;
+        $tags = array();
+        while ($gettags3[$i] = mysql_fetch_array($gettags2))
+        {
+                $tags[] = $gettags3[$i]['tag'];
+                $i++;
+        }
+
+        return $tags;
+}
+
+function addTags($id, $tags, $type = 'published')
+{
+        foreach ($tags as $tag)
+        {
+                $instag = "INSERT INTO tags (post_id,post_type,tag) VALUES (" . $id . ",\"" . $type . "\",\"" . $tag . "\")";
+                $instag2 = mysql_query($instag);
+        }
+}
+
+function removeTags($id, $type = 'published')
+{
+        $deltags = "DELETE FROM tags WHERE post_id = " . $id . " AND post_type = \"" . $type . "\"";
+        $deltags2 = mysql_query($deltags);
+}
+
 ?>
@@ -40,7 +40,7 @@ if ((!isset($disablePendingQueue)) && (date('j') != 'Sat'))
                         $getpost2 = mysql_query($getpost);
                         $getpost3 = mysql_fetch_array($getpost2);
 
-                        postBlogPost($getpost3['title'], $getpost3['author'], $getpost3['tags'], $getpost3['text']);
+                        postBlogPost($getpost3['title'], $getpost3['author'], explode(',', $getpost3['tags']), $getpost3['text']);
 
                         $delpost = "DELETE FROM pending WHERE id = " . $getpost3['id'];
                         $delpost2 = mysql_query($delpost);
@@ -37,17 +37,19 @@ if (isLoggedIn())
                         {
                                 $template = new FITemplate('admin/write');
                         } else {
-                                $tags = mysql_real_escape_string(serialize(explode(',', $_POST['tags'])));
+                                $tags = explode(',', $_POST['tags']);
 
                                 if ($_POST['type'] == 'draft')
                                 {
-                                        $insdraft = "INSERT INTO drafts (title,author,text,tags,slug) VALUES (\"" . addslashes($_POST['title']) . "\",\"" . sess_get('uname') . "\",\"" . addslashes($_POST['text']) . "\",\"" . $tags . "\",\"" . generateSlug($_POST['title'],'updates') . "\")";
+                                        $insdraft = "INSERT INTO drafts (title,author,text,slug) VALUES (\"" . addslashes($_POST['title']) . "\",\"" . sess_get('uname') . "\",\"" . addslashes($_POST['text']) . "\",\"" . generateSlug($_POST['title'],'updates') . "\")";
                                         $insdraft2 = mysql_query($insdraft);
 
                                         $getdraft = "SELECT * FROM drafts ORDER BY id DESC LIMIT 0,1";
                                         $getdraft2 = mysql_query($getdraft);
                                         $getdraft3 = mysql_fetch_array($getdraft2);
 
+                                        addTags($getdraft3['id'], $tags, 'draft');
+
                                         $template = new FITemplate('admin/draftSuccess');
                                         $template->add('ID', $getdraft3['id']);
                                 } else if ($_POST['type'] == 'instant')
@@ -88,9 +90,11 @@ if (isLoggedIn())
                                                 generateError(404);
                                         }
 
-                                        $inspending = "INSERT INTO pending (id,title,author,text,tags,slug) VALUES (" . $id . ",\"" . addslashes($_POST['title']) . "\",\"" . sess_get('uname') . "\",\"" . addslashes($_POST['text']) . "\",\"" . $tags . "\",\"" . generateSlug($_POST['title'],'updates') . "\")";
+                                        $inspending = "INSERT INTO pending (id,title,author,text,slug) VALUES (" . $id . ",\"" . addslashes($_POST['title']) . "\",\"" . sess_get('uname') . "\",\"" . addslashes($_POST['text']) . "\",\"" . generateSlug($_POST['title'],'updates') . "\")";
                                         $inspending2 = mysql_query($inspending);
 
+                                        addTags($id, $tags, 'pending');
+
                                         $template = new FITemplate('admin/pendingSuccess');
                                         $template->add('ID', $id);
                                 }
@@ -122,16 +126,19 @@ if (isLoggedIn())
                                         $template = new FITemplate('admin/editDraft');
                                         $template->add('ID', $_GET['id']);
                                         $template->add('TEXT', $getdraft3['text']);
-                                        $template->add('TAGS', implode(',', unserialize($getdraft3['tags'])));
+                                        $template->add('TAGS', implode(',', getTags($getdraft3['id'], 'draft')));
                                         $template->add('TITLE', $getdraft3['title']);
                                 } else {
-                                        $tags = mysql_real_escape_string(serialize(explode(',', $_POST['tags'])));
+                                        $tags = explode(',', $_POST['tags']);
+                                        removeTags($_GET['id'], 'draft');
 
                                         if ($_POST['type'] == 'draft')
                                         {
-                                                $setdraft = "UPDATE drafts SET title = \"" . addslashes($_POST['title']) . "\", text = \"" . addslashes($_POST['text']) . "\", tags = \"" . $tags . "\" WHERE id = " . $_GET['id'];
+                                                $setdraft = "UPDATE drafts SET title = \"" . addslashes($_POST['title']) . "\", text = \"" . addslashes($_POST['text']) . "\" WHERE id = " . $_GET['id'];
                                                 $setdraft2 = mysql_query($setdraft);
 
+                                                addTags($_GET['id'], $tags, 'draft');
+
                                                 $template = new FITemplate('admin/draftSuccess');
                                                 $template->add('ID', $_GET['id']);
                                         } else if ($_POST['type'] == 'instant')
@@ -178,6 +185,8 @@ if (isLoggedIn())
                                                 $inspending = "INSERT INTO pending (id,title,author,text,tags,slug) VALUES (" . $id . ",\"" . addslashes($_POST['title']) . "\",\"" . sess_get('uname') . "\",\"" . addslashes($_POST['text']) . "\",\"" . $tags . "\",\"" . generateSlug($_POST['title'],'updates') . "\")";
                                                 $inspending2 = mysql_query($inspending);
 
+                                                addTags($id, $tags, 'pending');
+
                                                 $deldraft = "DELETE FROM drafts WHERE id = " . $_GET['id'];
                                                 $deldraft2 = mysql_query($deldraft);
 
@@ -206,6 +215,8 @@ if (isLoggedIn())
                                         $deldraft = "DELETE FROM drafts WHERE id = " . $_GET['id'];
                                         $deldraft2 = mysql_query($deldraft);
 
+                                        removeTags($_GET['id'], 'draft');
+
                                         $template = new FITemplate('admin/deletedDraft');
                                 }
                         } else {
@@ -234,7 +245,7 @@ if (isLoggedIn())
                                                                         'RATING' => 0,
                                                                         'TEXT' => parseBBCode($getdraft3['text'])));
 
-                                $tags = unserialize($getdraft3['tags']);
+                                $tags = getTags($getdraft3['id'], 'draft');
                                 foreach ($tags as $tag)
                                 {
                                         $template->adds_ref_sub(0, 'TAGS', array('TAG' => $tag));
@@ -271,14 +282,17 @@ if (isLoggedIn())
                                         $template = new FITemplate('admin/editPending');
                                         $template->add('ID', $_GET['id']);
                                         $template->add('TEXT', $getpending3['text']);
-                                        $template->add('TAGS', implode(',', unserialize($getdraft3['tags'])));
+                                        $template->add('TAGS', implode(',', getTags($getpending3['id'], 'pending')));
                                         $template->add('TITLE', $getpending3['title']);
                                 } else {
-                                        $tags = mysql_real_escape_string(serialize(explode(',', $_POST['tags'])));
+                                        $tags = explode(',', $_POST['tags']);
 
-                                        $setpending = "UPDATE pending SET title = \"" . addslashes($_POST['title']) . "\", text = \"" . addslashes($_POST['text']) . "\", tags = \"" . $tags . "\" WHERE id = " . $_GET['id'];
+                                        $setpending = "UPDATE pending SET title = \"" . addslashes($_POST['title']) . "\", text = \"" . addslashes($_POST['text']) . "\" WHERE id = " . $_GET['id'];
                                         $setpending2 = mysql_query($setpending);
 
+                                        removeTags($_GET['id'], 'pending');
+                                        addTags($_GET['id'], $tags, 'pending');
+
                                         $template = new FITemplate('admin/pendingSuccess');
                                         $template->add('ID', $_GET['id']);
                                 }
@@ -303,6 +317,8 @@ if (isLoggedIn())
                                         $delpending = "DELETE FROM pending WHERE id = " . $_GET['id'];
                                         $delpending2 = mysql_query($delpending);
 
+                                        removeTags($_GET['id'], 'pending');
+
                                         $template = new FITemplate('admin/deletedPending');
                                 }
                         } else {
@@ -331,7 +347,7 @@ if (isLoggedIn())
                                                                         'RATING' => 0,
                                                                         'TEXT' => parseBBCode($getpending3['text'])));  
 
-                                $tags = unserialize($getpending3['tags']);
+                                $tags = getTags($getpending3['id'], 'pending');
                                 foreach ($tags as $tag)
                                 {
                                         $template->adds_ref_sub(0, 'TAGS', array('TAG' => $tag));
@@ -384,12 +400,19 @@ if (isLoggedIn())
                                         $delpending = "DELETE FROM pending WHERE id = " . $_GET['id'] . " OR id = " . $otherPending['id'];
                                         $delpending2 = mysql_query($delpending);
 
-                                        $inspending = "INSERT INTO pending (id, title, author, text, tags, slug) VALUES (" . $_GET['id'] . ",\"" . $otherPending['title'] . "\",\"" . $otherPending['author'] . "\",\"" . $otherPending['text'] . "\",\"" . $otherPending['tags'] . "\",\"" . $otherPending['slug'] . "\")";
+                                        $inspending = "INSERT INTO pending (id, title, author, text, slug) VALUES (" . $_GET['id'] . ",\"" . $otherPending['title'] . "\",\"" . $otherPending['author'] . "\",\"" . $otherPending['text'] . "\",\"" . $otherPending['slug'] . "\")";
                                         $inspending2 = mysql_query($inspending);
 
-                                        $ins2pending = "INSERT INTO pending (id, title, author, text, tags, slug) VALUES (" . $otherPending['id'] . ",\"" . $getpending3['title'] . "\",\"" . $getpending3['author'] . "\",\"" . $getpending3['text'] . "\",\"" . $getpending3['tags'] . "\",\"" . $getpending3['slug'] . "\")";
+                                        $ins2pending = "INSERT INTO pending (id, title, author, text, slug) VALUES (" . $otherPending['id'] . ",\"" . $getpending3['title'] . "\",\"" . $getpending3['author'] . "\",\"" . $getpending3['text'] . "\",\"" . $getpending3['slug'] . "\")";
                                         $ins2pending2 = mysql_query($ins2pending);
 
+                                        $tags1 = getTags($_GET['id'], 'pending');
+                                        $tags2 = getTags($otherPending['id'], 'pending');
+                                        removeTags($_GET['id'], 'pending');
+                                        removeTags($otherPending['id'], 'pending');
+                                        addTags($_GET['id'], $tags2, 'pending');
+                                        addTags($otherPending['id'], $tags1, 'pending');
+
                                         $template = new FITemplate('admin/managePending');
 
                                         $getpending = "SELECT * FROM pending ORDER BY id ASC";
@@ -436,14 +459,17 @@ if (isLoggedIn())
                                         $template = new FITemplate('admin/editPost');
                                         $template->add('ID', $_GET['id']);
                                         $template->add('TEXT', $getpost3['text']);
-                                        $template->add('TAGS', implode(',', unserialize($getpost3['tags'])));
+                                        $template->add('TAGS', implode(',', getTags($getpost3['id'])));
                                         $template->add('TITLE', $getpost3['title']);
                                 } else {
-                                        $tags = mysql_real_escape_string(serialize(explode(',', $_POST['tags'])));
+                                        $tags = explode(',', $_POST['tags']);
 
-                                        $setpost = "UPDATE updates SET title = \"" . addslashes($_POST['title']) . "\", text = \"" . addslashes($_POST['text']) . "\", tags = \"" . $tags . "\" WHERE id = " . $_GET['id'];
+                                        $setpost = "UPDATE updates SET title = \"" . addslashes($_POST['title']) . "\", text = \"" . addslashes($_POST['text']) . "\" WHERE id = " . $_GET['id'];
                                         $setpost2 = mysql_query($setpost);
 
+                                        removeTags($_GET['id']);
+                                        addTags($_GET['id'], $tags);
+
                                         $template = new FITemplate('admin/postSuccess');
                                         $template->add('ID', $_GET['id']);
                                         $template->add('CODED', $getpost3['slug']);
@@ -469,6 +495,8 @@ if (isLoggedIn())
                                         $delpost = "DELETE FROM updates WHERE id = " . $_GET['id'];
                                         $delpost2 = mysql_query($delpost);
 
+                                        removeTags($_GET['id']);
+
                                         $template = new FITemplate('admin/deletedPost');
                                 }
                         } else {
@@ -74,7 +74,7 @@ if (isset($_GET['post']))
                                                         'RATING' => $getpost3['rating'],
                                                         'TEXT' => parseBBCode($getpost3['text'])));
 
-                $tags = unserialize($getpost3['tags']);
+                $tags = getTags($getpost3['id']);
                 foreach ($tags as $tag)
                 {
                         $template->adds_ref_sub(0, 'TAGS', array('TAG' => $tag));
@@ -135,7 +135,7 @@ if (isset($_GET['post']))
         if (isset($_GET['author']))
         {
                 $title = 'Author: ' . $_GET['author'] . ' - Blog Archive';
-                $getposts = "SELECT * FROM updates WHERE author = \"" . $_GET['author'] . "\" ORDER BY id DESC";
+                $getposts = "SELECT * FROM updates AS u WHERE author = \"" . $_GET['author'] . "\" ORDER BY id DESC";
                 $getbio = "SELECT * FROM bio WHERE username = \"" . $_GET['author'] . "\"";
                 $getbio2 = mysql_query($getbio);
                 $getbio3 = mysql_fetch_array($getbio2);
@@ -148,10 +148,10 @@ if (isset($_GET['post']))
         } elseif (isset($_GET['tag']))
         {
                 $title = 'Tag: ' . $_GET['tag'] . ' - Blog Archive';
-                $getposts = "SELECT * FROM updates WHERE tags LIKE '%s:" . strlen($_GET['tag']) . ":\"" . $_GET['tag'] . "\"%' ORDER BY id DESC";
+                $getposts = "SELECT * FROM updates AS u, tags AS t WHERE u.id = t.post_id AND t.post_type = \"published\" AND t.tag = \"" . $_GET['tag'] . "\" ORDER BY u.id DESC";
         } else {
                 $title = 'Blog Archive';
-                $getposts = "SELECT * FROM updates ORDER BY id DESC";
+                $getposts = "SELECT * FROM updates AS u ORDER BY id DESC";
         }
         $getposts2 = mysql_query($getposts);
         $i=0;
@@ -70,7 +70,7 @@ while ($getpost3 = mysql_fetch_array($getpost2))
                                                         'RATING' => $getpost3['rating'],
                                                         'TEXT' => parseBBCode($getpost3['text'])));
 
-        $tags = unserialize($getpost3['tags']);
+        $tags = getTags($getpost3['id']);
         foreach ($tags as $tag)
         {
                 $template->adds_ref_sub($curID, 'TAGS', array('TAG' => $tag));
@@ -118,7 +118,7 @@ if (!isset($_GET['mode']) || ($_GET['mode'] == 'blog'))
                 $getposts = "SELECT * FROM updates WHERE author = \"" . $_GET['author'] . "\" ORDER BY id DESC";
         } else if ($_GET['blog'] == 'tag')
         {
-                $getposts = "SELECT * FROM updates WHERE tags LIKE '%s:" . strlen($_GET['tag']) . ":\"" . $_GET['tag'] . "\"%' ORDER BY id DESC";
+                $getposts = "SELECT * FROM updates AS u, tags AS t WHERE u.id = t.post_id AND t.post_type = \"published\" AND t.tag = \"" . $_GET['tag'] . "\" ORDER BY u.id DESC";
         } else if (!isset($_GET['blog'])) {
                 $getposts = "SELECT * FROM updates ORDER BY id DESC";
         }