TalkPHP - Category System Tutorial

TalkPHP (http://www.talkphp.com/forums.php)

- Tips & Tricks (http://www.talkphp.com/tips-tricks/)

- - Category System Tutorial (http://www.talkphp.com/tips-tricks/1579-category-system-tutorial.html)

Category System Tutorial

When I first started using PHP, and really still today, I used it for Content Management Systems. I got them to be pretty advanced, except there was one thing that always got me. Whenever I did categories, I was limited to however many fields I had in the "articles" table. I'm sure you PHP gurus already know how I'm going to solve this problem, so this article is mostly for some new comers.

The basic idea of this setup is selecting the categories from one table and list them. Then you insert each check box (category) into one field, separated with a comma. Then you explode the categories when you want to display it.

You need to first setup your tables. Make one table called Categories, with 2 fields. Primary ID, and Name. (You can do a 3rd - description, if you want). Then if you already have a posts table for your CMS, just make a new field called "cats" or "categories". On your page where you add a new post, add the following code.

PHP Code:


		
			
<?php
    $select = @mysql_query("SELECT title FROM `cats` ORDER by title DESC");
          while($row = mysql_fetch_array($select)) { 
               extract($row);        
               echo'<input type="checkbox" name="cat[]" value="'.$title.'" />'.$title.' <br />'; 
           }
?>

This code will get all the rows from the "cats" table, and list them next to a check box. The name of the check box is an array, so you can have multiple checks.
(Note: This needs to be inside the form you use to post your articles).

Now before you insert this data into the database, we need to link all the check boxes together. Heres how we can do it.

PHP Code:


		
			
// Make an array out of our name="cat[]" value on the checkbox.
$cats = array_map("mysql_real_escape_string",$cat);
// Implode (Opposite of explode) them together with a comma.
$imp = implode(",",$cats);

Now just insert the "$imp" variable into the database field that you made (probably cat) like normal. So if you checked "Site Updates" and "Random Info", it would insert "Site Updates, Random Info" into the single field.

Now its time to actually call the categories. Here is how we can do that:

PHP Code:


		
			
$cats = explode(",", $cats);
$totalcats = count($cats);
$i = 0;
while($i < $totalcats) { 
     echo'<a href="browse.php?view='.$cats[''.$i.''].'" >'.$cats[''.$i.''].'</a>';
     if($i == $totalcats - 1)
           echo "";
      else 
           echo ", ";
     $i = $i + 1;
}

All of that above code should be inside your loop that is getting your posts.

First we define the variable $cats as an explosion of the field "cats". Remember when we imploded it before we put it into the database, now we are doing the opposite. We are finding the "," between each one, and splitting it up into an array. Then we count it with $totalCats, so we know how many pieces it was exploded into. The $i variable is just set to 0, for counting purposes.

The next piece of code is called a while loop. It means this.

PHP Code:


		
			
while(something is true) {
     do this
}

So, while $i (0) is less than $totalCats, continue. The first echo in our loop echos out a link and the name of the category. $cats[''.$i.''] is getting whatever value is represented in our array by cat[0]. In arrays, the counting starts as 0 So that means it is echoing our first piece of exploded field. The next part:

PHP Code:


		
			
if($i == $totalcats - 1)
    echo "";
else 
   echo ", ";

This is a little cosmetics thing. Its saying that if $i (our number) is 1 less than the total number of exploded pieces, echo nothing. If it is not, echo a ", ". It needs to be one less, since arrays start counting at 0. This means if you have 3 categories, "Cat1", "Cat2", and "Cat3", it will be "Cat1, Cat2, Cat3" It didn't add that last comma, since it realized Cat3 was the last one.

The very last $i = $i + 1; is to increment our $i variable, so the while loop will move onto the next exploded piece.

That about does it. :) I know the tutorial may be sort of hard to follow, since I assumed you had a few things setup already, but I didn't have time to write out a whole CMS tutorial, since I am at school. :-P But if you have any questions on how to get it working, I'll be glad to answer them as best I can.

Good luck! ^^

Good job on the tutorial.

Thanks. :-)

Thanks for the tutorial! There is another way to get around this problem, and that is by normalising your database. That is, having a lookup table to store all the items. You list all your categories in one table (categories) and then you list all your items that require categories in another table (pictures), you then have a table in between those 2 called something like (pictures_categories) - your lookup table.

Allow me to demonstrate.

Code:

Table: categories



+-----+-------------+

| id  | name        |

+-----+-------------+

| 11  | psychology  |

| 12  | philosophy  |

| 13  | sociology   |

+-----+-------------+

Code:

Table: pictures



+-----+-------------+

| id  | name        |

+-----+-------------+

| 1   | image1      |

| 2   | image2      |

| 3   | image3      |

+-----+-------------+

Code:

Table: pictures_categories



+-------------+--------------+

| picture_id  | category_id  |

+-------------+--------------+

| 1           | 11           |

| 1           | 12           |

| 2           | 13           |

+-------------+--------------+

As you can see in our pictures_categories table, our image1 has 2 categories, and our image3 has 1 category, whereas our 3rd image has no categories. You would then perform a MySQL JOIN to get the categories that the images are associated with. Something like so:

Note: Not tested this query, but I'm 95% sure it'll be fine!

sql Code:

SELECT
    categories.name
FROM
    pictures
LEFT JOIN
    pictures_categories
ON
    pictures_categories.picture_id = pictures.id
LEFT JOIN
    categories
ON
    categories.id = pictures_categories.category_id
WHERE
    pictures.name = 'image1'

This would then return the names of all the categories that image1 has associated with it. It's a tough one to describe, but I hope you can at least extract some useful points! Also for any one interested, maybe Wikipedia'll provide more in-depth information.

But basically, our pictures_categories table contains 2 columns of IDs which reference each of the other 2 tables. You just match up the IDs and use the lookup table to store multiple categories.

Ah good idea Wildhoney. :) I guess in a way that would be a little more organized then the method I mentioned. 8-)

@ Haris - No problem! ^^

All part of the learning process, squire! :-) If you'd not have written the article you wouldn't have found the other way of going about it. Not today anyway. The good thing about my way is it's really fast to find the categories and even search within the categories.

Article much appreciated!

Quote:

Originally Posted by Wildhoney (Post 5123)

Thanks for the tutorial! There is another way to get around this problem, and that is by normalising your database. That is, having a lookup table to store all the items. You list all your categories in one table (categories) and then you all your items that require categories in another table (pictures), you the have a table in between those 2 called something like (pictures_categories).

Allow me to demonstrate.

Code:

Table: categories



+-----+-------------+

| id  | name        |

+-----+-------------+

| 11  | psychology  |

| 12  | philosophy  |

| 13  | sociology   |

+-----+-------------+

Code:

Table: pictures



+-----+-------------+

| id  | name        |

+-----+-------------+

| 1   | image1      |

| 2   | image2      |

| 3   | image3      |

+-----+-------------+

Code:

Table: pictures_categories



+-----------+--------------+

| image_id  | category_id  |

+-----------+--------------+

| 1         | 11           |

| 1         | 12           |

| 2         | 13           |

+-----------+--------------+

sql Code:

SELECT
    categories.name
FROM
    pictures
LEFT JOIN
    pictures_categories
ON
    pictures_categories.picture_id = pictures.id
LEFT JOIN
    categories
ON
    categories.id = pictures_categories.category_id
WHERE
    pictures.name = 'image1'

Lol, it was that easy. *!*

In my last script, I had like 3 queries to do simple tasks like these. :-!

So Wildhoney, on yours, each time you add something to the pictures database, you get just the id, and then put the id in the pictures_category table, with the id of category? Then when you want to query them.. How exactly are you matching them? Maybe I need to read up on table joining. :)

EDIT: Ahh yes.. I've just read a few short articles. Join and Left Join looks pretty interesting, I may have to start putting it to use. :)

Sorry, completely missed your last question, Gurnk! Basically it's all down to the JOINs:

Join picture.id to picture_categories.id based on WHERE clause (one to many);
Join picture_categories.category_id to categories.id (many to many);

Key:
Blue = Table
Green = Column

So for every ID that matches to the picture's image in our lookup table, get all the IDs that match up to a categories in the categories table.

Hum, sorry to be a burden.. but you should probably test queries before you suspect that they work, it could save time in the future ^^

[/TIP]

Otherwise, great article for the beginners!

Quote:

Originally Posted by Jay (Post 5713)

Hum, sorry to be a burden.. but you should probably test queries before you suspect that they work, it could save time in the future ^^

[/TIP]

Otherwise, great article for the beginners!

Was that aimed at me? Either way, yes I should. I was fairly confident though that it would work, but even still, you have a fair point!

Quote:

Originally Posted by Wildhoney (Post 5703)

Sorry, completely missed your last question, Gurnk! Basically it's all down to the JOINs:

Join picture.id to picture_categories.id based on WHERE clause (one to many);
Join picture_categories.category_id to categories.id (many to many);

Key:
Blue = Table
Green = Column

So for every ID that matches to the picture's image in our lookup table, get all the IDs that match up to a categories in the categories table.

Ah nice one! I didn't know you could do stuff like table.column. Thanks for the tips.^^

Quote:

Originally Posted by Wildhoney (Post 5123)

Code:

Table: categories



+-----+-------------+

| id  | name        |

+-----+-------------+

| 11  | psychology  |

| 12  | philosophy  |

| 13  | sociology   |

+-----+-------------+

Code:

Table: pictures



+-----+-------------+

| id  | name        |

+-----+-------------+

| 1   | image1      |

| 2   | image2      |

| 3   | image3      |

+-----+-------------+

Code:

Table: pictures_categories



+-------------+--------------+

| picture_id  | category_id  |

+-------------+--------------+

| 1           | 11           |

| 1           | 12           |

| 2           | 13           |

+-------------+--------------+

sql Code:

SELECT
    categories.name
FROM
    pictures
LEFT JOIN
    pictures_categories
ON
    pictures_categories.picture_id = pictures.id
LEFT JOIN
    categories
ON
    categories.id = pictures_categories.category_id
WHERE
    pictures.name = 'image1'

Can you use the same structure for categories and sub-categories? I am confused. :(

How do you mean, Haris? That structure can be used for anything although usually it's where you have a one-to-many relationship, you introduce the lookup table to normalise the database to allow for two one-to-one many relationships across 3 tables.

Code:

category

  id

  parent_id

  name

Is a pretty easy way to get category/sub-categories. Just use parent_id default of 0 for root-level categories. You can recurse through it as well if subcategories have their own children, etc as well.