[Phpbbkb-checkins] SF.net SVN: phpbbkb: [24] root

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

Revision: 24
          http://svn.sourceforge.net/phpbbkb/?rev=24&view=rev
Author:   softphp
Date:     2006-11-25 18:49:26 -0800 (Sat, 25 Nov 2006)

Log Message:
-----------
Fairly big commit here:
- Recoded the multiple categories part, and introducing a new database table. It works nice.
- Corrected minor template mistake.
- Corrected some other stuff in ucp_class.php, especially the categories part, now including a lot of loops, could it be done easier?

TODO:
- Edit Article <-- Yes I worked on it now, and the user interface part works, but the db works hasn't been done, so no testing has commenced.
- Preview when posting <-- I'll add this after I've added the view article part I think.
- View Article <-- Not a single line has been coded

Modified Paths:
--------------
    root/kb/constants.php
    root/kb/ucp_class.php
    root/kb.php
    root/kb_install.php
    root/templates/subSilver/kb_viewcat.tpl

Modified: root/kb/constants.php
===================================================================

--- root/kb/constants.php	2006-11-24 14:46:46 UTC (rev 23)
+++ root/kb/constants.php	2006-11-26 02:49:26 UTC (rev 24)
@@ -27,5 +27,6 @@
 // DB Tables
 define('KB_CATEGORIES_TABLE', $table_prefix . "kb_categories");
 define('KB_ARTICLES_TABLE', $table_prefix . "kb_articles");
+define('KB_ARTICLECATS_TABLE', $table_prefix . "kb_articlecats"); // For Multiple cats
 
 ?>
\ No newline at end of file

Modified: root/kb/ucp_class.php
===================================================================
--- root/kb/ucp_class.php	2006-11-24 14:46:46 UTC (rev 23)
+++ root/kb/ucp_class.php	2006-11-26 02:49:26 UTC (rev 24)
@@ -163,15 +163,31 @@
 			{
 				$current_time = time();
 			
-				$sql  = "INSERT INTO " . KB_ARTICLES_TABLE . " (article_id, cat_id, article_title, article_desc, article_author, article_authorname, article_time, article_edittime, article_hits, article_editby, article_status, enable_sig, enable_html, enable_bbcode, enable_smilies, article_text) VALUES
-															  ('', '$cat_id', '$article_title', '$article_desc', '$article_author', '$article_authorname', '$current_time', '$current_time', '0', '" . $userdata['user_id'] . "', '0', '$attach_sig', '$html_on', '$bbcode_on', '$smilies_on', '$message');";
+				$sql = "INSERT INTO " . KB_ARTICLES_TABLE . " (article_id, article_title, article_desc, article_author, article_authorname, article_time, article_edittime, article_hits, article_editby, article_status, enable_sig, enable_html, enable_bbcode, enable_smilies, article_text) VALUES
+															  ('', '$article_title', '$article_desc', '$article_author', '$article_authorname', '$current_time', '$current_time', '0', '" . $userdata['user_id'] . "', '0', '$attach_sig', '$html_on', '$bbcode_on', '$smilies_on', '$message');";
 				if (!$db->sql_query($sql))
 				{
 					message_die(GENERAL_ERROR, 'Error in adding article', '', __LINE__, __FILE__, $sql);
 				}
-			
+				
 				$article_id = $db->sql_nextid();
-			
+				// Now make the categories
+				foreach($cat_id as $i => $cat)
+				{
+					$sql = "INSERT INTO " . KB_ARTICLECATS_TABLE . " VALUES ('$article_id', '$cat');\n";
+					$sql2 = "UPDATE " . KB_CATEGORIES_TABLE . " SET cat_articles = cat_articles + 1 WHERE cat_id = '$cat';\n";							
+					
+					if (!$db->sql_query($sql))
+					{
+						message_die(GENERAL_ERROR, 'Error in adding articles categories.', '', __LINE__, __FILE__, $sql);
+					}
+					
+					if (!$db->sql_query($sql2))
+					{
+						message_die(GENERAL_ERROR, 'Error in adding updating categories articles count.', '', __LINE__, __FILE__, $sql);
+					}
+				}
+
 				$meta = '<meta http-equiv="refresh" content="3;url=' . append_sid('kb.' . $phpEx . '?pid=view_article&amp;id=' . $article_id) . '>"';
 				$return_message = $lang['kb_added'] . '<br /><br />' . sprintf($lang['kb_click_view_article'], '<a href="' . append_sid('kb.' . phpEx . '?pid=view_article&amp;id=' . $article_id) . '">', '</a>') . '<br /><br />' . sprintf($lang['kb_click_return_ucp'], '<a href="' . append_sid('kb.' . $phpEx . '?pid=ucp') . '">', '</a>');
 				
@@ -236,8 +252,16 @@
 			$smilies_on = ( $HTTP_POST_VARS['disable_smilies'] ) ? false : true;
 			
 			$form_action = append_sid("kb.php?pid=ucp&amp;action=post_article");
-			$hidden_form_fields = "";
 			
+			if($mode == "edit")
+			{
+				$hidden_form_fields = "";
+			}
+			else
+			{
+				$hidden_form_fields = "";
+			}
+			
 			if($error_msg != "")
 			{
 				$template->set_filenames(array(
@@ -251,15 +275,36 @@
 		}
 		else
 		{
+			if(empty($id))
+			{
+				message_die(GENERAL_ERROR, "No article defined.");
+			}
+			
 			$sql = "SELECT * 
 					FROM " . KB_ARTICLES_TABLE . "
-					WHERE id = '" . $id . "'";
+					WHERE article_id = '$id'";
 			if(!$result = $db->sql_query($sql))
 			{
 				message_die(GENERAL_ERROR, 'Could not query article data.', '', __LINE__, __FILE__, $sql);
 			}
+			
 			$article = $db->sql_fetchrow($result);
 			
+			// Now make an array over the cats
+			$sql = "SELECT cat_id 
+					FROM " . KB_ARTICLECATS_TABLE . "
+					WHERE article_id = '$id'";
+			if(!$result = $db->sql_query($sql))
+			{
+				message_die(GENERAL_ERROR, 'Could not query articlecats data.', '', __LINE__, __FILE__, $sql);
+			}
+			
+			$article_cats = array();
+			while($row = $db->sql_fetchrow($result))
+			{
+				$article_cats[] = $row;
+			}
+			
 			$article_title = $article['article_title'];
 			$article_text = $article['article_text'];
 			$article_desc = $article['article_desc'];
@@ -316,34 +361,47 @@
 		// Obtain categories structure
 		$cats = get_cats_structure();
 		
-		// First lets sort main cats
+		// First lets sort main cats, yes i know there is a lot of loops, but i can't find a better way :S
 		$s_cats = '<option value="0">-' . $lang['kb_main'] . '</option>';
-		if($preview || $mode == "edit")
+		if($mode == "edit")
 		{
 			for($i = 0; $i < count($cats); $i++)
 			{
-				$s_cats .= '<option value="' . $cats[$i]['cat_id'] . '">--' . $cats[$i]['cat_title'] . '</option>';
+				$selected = '';
+				for($k = 0; $k < count($article_cats); $k++)
+				{
+					if($article_cats[$k]['cat_id'] == $cats[$i]['cat_id'])
+					{
+						$selected = ' selected="selected"';
+					}
+				}
+				$s_cats .= '<option' . $selected . ' value="' . $cats[$i]['cat_id'] . '">&nbsp;&nbsp;--' . $cats[$i]['cat_title'] . '</option>';
 				
 				// Sort subcats
 				for($j = 0; $j < count($cats[$i]['subcats']); $j++)
 				{
-					$s_cats .= '<option value="' . $cats[$i]['subcats'][$j]['cat_id'] . '">--' . $cats[$i]['subcats'][$j]['cat_title'] . '</option>';
+					$selected = '';
+					for($k = 0; $k < count($article_cats); $k++)
+					{
+						if($article_cats[$k]['cat_id'] == $cats[$i]['subcats'][$j]['cat_id'])
+						{
+							$selected = ' selected="selected"';
+						}
+					}
+					$s_cats .= '<option' . $selected . ' value="' . $cats[$i]['subcats'][$j]['cat_id'] . '">&nbsp;&nbsp;&nbsp;&nbsp;--' . $cats[$i]['subcats'][$j]['cat_title'] . '</option>';
 				}
 			}
 		}
 		else
 		{
-			$var = ( $preview ) ? $HTTP_POST_VARS['cats'] : $article['cat_id'];
 			for($i = 0; $i < count($cats); $i++)
 			{
-				$selected = ( strstr($var, "," . $cats[$i]['cat_id'] . ",") ) ? ' selected' : '';
-				$s_cats .= '<option' . $selected . ' value="' . $cats[$i]['cat_id'] . '">--' . $cats[$i]['cat_title'] . '</option>';
+				$s_cats .= '<option value="' . $cats[$i]['cat_id'] . '">--' . $cats[$i]['cat_title'] . '</option>';
 				
 				// Sort subcats
 				for($j = 0; $j < count($cats[$i]['subcats']); $j++)
 				{
-					$selected = ( strstr($var, "," . $cats[$i]['subcats'][$j]['cat_id'] . ",") ) ? ' selected' : '';
-					$s_cats .= '<option' . $selected . ' value="' . $cats[$i]['subcats'][$j]['cat_id'] . '">--' . $cats[$i]['subcats'][$j]['cat_title'] . '</option>';
+					$s_cats .= '<option value="' . $cats[$i]['subcats'][$j]['cat_id'] . '">--' . $cats[$i]['subcats'][$j]['cat_title'] . '</option>';
 				}
 			}
 		}
@@ -483,12 +541,8 @@
 		}
 		
 		// Check categories
-		if(is_array($cat_id))
+		if(!is_array($cat_id))
 		{
-			$cat_id = implode(",", $cat_id);
-		}
-		else
-		{
 			$error_msg .= (!empty($error_msg)) ? '<br />' . $lang['kb_empty_cats'] : $lang['kb_empty_cats'];
 		}
 		return;

Modified: root/kb.php
===================================================================
--- root/kb.php	2006-11-24 14:46:46 UTC (rev 23)
+++ root/kb.php	2006-11-26 02:49:26 UTC (rev 24)
@@ -188,29 +188,31 @@
 			{
 				case "rating":
 					// Later
-					$sort = "ORDER BY article_edittime DESC";
+					$sort = "ORDER BY a.article_edittime DESC";
 					break;
 					
 				case "author":
 					// Later
-					$sort = "ORDER BY article_edittime DESC";
+					$sort = "ORDER BY a.article_edittime DESC";
 					break;
 				
 				case "title":
-					$sort = "ORDER BY article_title";
+					$sort = "ORDER BY a.article_title";
 					break;
 				
 				case "time":
 				default:
-					$sort = "ORDER BY article_edittime DESC";
+					$sort = "ORDER BY a.article_edittime DESC";
 					break;
 			}
 		}
 		else
 		{
-			$sort = "ORDER BY article_edittime DESC";
+			$sort = "ORDER BY a.article_edittime DESC";
 		}
 		
+		// This has been removed due to probably not working 
+		/*
 		$sql = "SELECT *
 				FROM " . KB_ARTICLES_TABLE . "
 				WHERE cat_id LIKE '$cat_id'
@@ -219,10 +221,21 @@
 		{
 			message_die(GENERAL_ERROR, 'Could not query articles list', '', __LINE__, __FILE__, $sql);
 		}
+		*/
 		
+		$sql = "SELECT a.*
+				FROM " . KB_ARTICLECATS_TABLE . " c, " . KB_ARTICLES_TABLE . " a
+				WHERE c.cat_id = '$cat_id'
+				AND c.article_id = a.article_id
+				$sort";
+		if( !($result = $db->sql_query($sql)) )
+		{
+			message_die(GENERAL_ERROR, 'Could not query articles.', '', __LINE__, __FILE__, $sql);
+		}
+		
 		$articles = array();
 		while($row = $db->sql_fetchrow($result))
-		{
+		{			
 			$articles[] = $row;
 		}
 		
@@ -305,7 +318,7 @@
 				
 				$sql = "SELECT username
 						FROM " . USERS_TABLE . "
-						WHERE user_id = '" . $articles[$i]['article_editby'];
+						WHERE user_id = '" . $articles[$i]['article_editby'] . "'";
 				if( !($result = $db->sql_query($sql)) )
 				{
 					message_die(GENERAL_ERROR, 'Could not query last edited by\'s username.', '', __LINE__, __FILE__, $sql);

Modified: root/kb_install.php
===================================================================
--- root/kb_install.php	2006-11-24 14:46:46 UTC (rev 23)
+++ root/kb_install.php	2006-11-26 02:49:26 UTC (rev 24)
@@ -62,7 +62,6 @@
 
 $sql[] = "CREATE TABLE " . $table_prefix . "kb_articles (
    article_id mediumint(8) UNSIGNED NOT NULL auto_increment,
-   cat_id varcharr(20) NOT NULL,
    article_title varchar(100) NOT NULL,
    article_desc varchar(255) NOT NULL,
    article_author mediumint(8) UNSIGNED NOT NULL,
@@ -77,10 +76,14 @@
    enable_bbcode smallint(1) UNSIGNED DEFAULT '0',
    enable_smilies smallint(1) UNSIGNED DEFAULT '0',
    article_text text,
-   PRIMARY KEY (article_id),
-   KEY cat_id (cat_id)
+   PRIMARY KEY (article_id)
 )";
 
+$sql[] = "CREATE TABLE " . $table_prefix . "kb_articlecats (
+	article_id mediumint(8) NOT NULL DEFAULT '0',
+	cat_id mediumint(8) NOT NULL DEFAULT '0'
+)";
+
 echo '<table width="100%" cellspacing="1" cellpadding="2" border="0" class="forumline">';
 echo '<tr><th>Updating the database</th></tr>';
 

Modified: root/templates/subSilver/kb_viewcat.tpl
===================================================================
--- root/templates/subSilver/kb_viewcat.tpl	2006-11-24 14:46:46 UTC (rev 23)
+++ root/templates/subSilver/kb_viewcat.tpl	2006-11-26 02:49:26 UTC (rev 24)
@@ -46,7 +46,7 @@
 	</tr>
 	<!-- END articlerow -->
 	<tr> 
-	  <td class="catBottom" align="center" valign="middle" colspan="4" height="28">&nbsp;</td>
+	  <td class="catBottom" align="center" valign="middle" colspan="5" height="28">&nbsp;</td>
 	</tr>
 </table>
 <!-- END switch_articles -->


This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.


