FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


How do I categorize content?





ixtra
I want to categorize my content so I added a categories field in my database table and when ever I post a new topic it asks for the category and stores it.

Now I was wondering how can I display the available categories with the topics?
alalex
Are you using any standard CMS or you are building your own?
In case you are building your own CMS specify the database structure so we can tell you how to query the db to show the categories Wink
ixtra
Well I am building my own cms
so here is the database structure
Code:
CREATE TABLE `cms` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(50) NOT NULL,
  `description` text NOT NULL,
  `content` text NOT NULL,
  `date` text NOT NULL,
  `avatar` text NOT NULL,
  `categories` varchar(50) NOT NULL,
  `views` int(11) NOT NULL default '1',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;
alalex
Ok, so if I understood correctly, each new post contains the list of categories or category in the colun category.
If that is the case and you want to display all categories in a list, I recommend you create another table containing an index of the categories, and everytime a new post is posted with new categories, this are added to the category list. The reason for this is that when you have a lot of posts it will take a very long time to iterate through all the rows looking for new categories...

If you want to stick with your current database structure you could use the following code to fetch all new categories:
Code:
<?php
//I'll suppose you have already done the connection to the db and all that
$query = 'SELECT `categories` FROM `cms`';
$result = mysql_query($query);
if(!$result){
   echo 'An error occured';
}else if($result<1){
   echo 'No categories found';
}else{
   $categories = array();
   while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
      if(!in_array($row['categories'],$categories){
         $categories[] = $row['categories'];
      }
   }
}
//Now you have array $categories containing all unique categories
//Sort it alphabetically:
sort($categories);
//print them: (Change this to a nicer display ;)
print_r($categories);
?>
imagefree
alalex wrote:
Ok, so if I understood correctly, each new post contains the list of categories or category in the colun category.
If that is the case and you want to display all categories in a list, I recommend you create another table containing an index of the categories, and everytime a new post is posted with new categories, this are added to the category list. The reason for this is that when you have a lot of posts it will take a very long time to iterate through all the rows looking for new categories...

If you want to stick with your current database structure you could use the following code to fetch all new categories:
Code:
<?php
//I'll suppose you have already done the connection to the db and all that
$query = 'SELECT `categories` FROM `cms`';
$result = mysql_query($query);
if(!$result){
   echo 'An error occured';
}else if($result<1){
   echo 'No categories found';
}else{
   $categories = array();
   while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
      if(!in_array($row['categories'],$categories){
         $categories[] = $row['categories'];
      }
   }
}
//Now you have array $categories containing all unique categories
//Sort it alphabetically:
sort($categories);
//print them: (Change this to a nicer display ;)
print_r($categories);
?>



ALARM ALARM!!!!!


Code is wrong! Didnt you forget something? Have a look again.
ixtra
I know there is just one tiny error over here
Code:
 if(!in_array($row['categories'],$categories){

there should be another close bracket for the if statement
Here is the correct line
Code:
 if(!in_array($row['categories'],$categories)){


Anyways

thanks alalex for the help worked really well Very Happy
rvec
maybe use distinct to get each category only once?
http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html

It's a lot faster than this code if you have only 2 categories and like 100 posts.
alalex
Embarassed Sorry for the error, I wrote it on the fly and didn't take a look at it

Hope it works now fine, but still I don't think it is a good idea to do it that way, it will slow down a lot the performance of your site and increase bandwidth a lot.
Instead try what I said previously, create another table containing the category names and other info you might want such as descriptions or whatever...
If you want to go that way I can rewrite your code to work it out
ixtra
rvec wrote:
maybe use distinct to get each category only once?
http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html

It's a lot faster than this code if you have only 2 categories and like 100 posts.


Well I cant really understand the code Sad

alalex wrote:

Sorry for the error, I wrote it on the fly and didn't take a look at it

Hope it works now fine, but still I don't think it is a good idea to do it that way, it will slow down a lot the performance of your site and increase bandwidth a lot.
Instead try what I said previously, create another table containing the category names and other info you might want such as descriptions or whatever...
If you want to go that way I can rewrite your code to work it out


Ok so I have to create another table "category" and add the id and title of the content each time I post new topic ?
alalex
Well the idea about using a category table would be sort of this:
Code:
ID (Optional) | Name | Description (Optional) | ... (Any other fields you might want, like tags)


Now every time you post something you could have something like a drop down with all category names, and at the bottom a "Other" or "New" option, and somewhere around the drop down a textfield to write the name of that "new" category. So this way you could select an existing category or create a new one if needed.

This way you could list the categories really easily and edit them without the hassle of searching through all of your posts to find the categories you want to edit/remove...

If you are unable to code this just say so and I'll make you something
ixtra
ok so I made this table

Code:
CREATE TABLE `category` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 50 ) NOT NULL
) ENGINE = MYISAM ;


what should I do next? Confused
alalex
Alright
Now you have to modify the posting code so that there is in the write page a drop down with all categories, for that use a select query, to select all categories, and loop through the results, with value the ID of the category and text the name. And at the bottom add in the html another option with something like "New category". If you want to allow multiple categories consider using a multiple select or a list of checkboxes instead.
The php for that should be easy, but post here if you run into any problems.

Now once the post is written and the catgories selected, just use the code you had before to store the code, and in the category column in the posts table put the ID if it is one, or the list of IDs separated by comas, spaces or whatever symbol you want.

This way you have categorized the posts, and you have the category index in another table so it is faster and easier to manage. Wink

Post here any problems you might have
ixtra
thanks for the detailed explanation of the category table , I have created a new table for category and have done what you have said Smile I have not finished everything but am sure should work

Thanks a lot! Very Happy
jasonf90
so im trying to do the same thing, i have already created the category table.

I already have the code that allows a user to ad new categories all i need to do is allow the user to select the category when they are creating a product.

im trying to use php. if you can give me any tips on how to write the code it will be appreciated.
Related topics
Mambo Content Problem
Google Adsense - *OFFICIAL THREAD*
ASP content management system
14 ways to add content to your web site and attract visitors
Content Management System Wanted
free content
Ha je suis content
Open Content
Your Favorite Content On CD or DVD?
Duplicate content and home page
Master Tutorial on SEO
One-Stop Online Content Shop
Content or how it looks?
Content is the King !!!
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.