Part 11: Categories

This tutorial will teach you how to add simple categories to your blog.


Please note: The Build-A-Blog series is an introduction to creating a simple blog script using PHP. These tutorials are meant to help you to learn PHP and MySQL and to use these to fetch and store data and display it on a web page. These tutorials should not be used ‘as is’ on a production website – especially if you are new to PHP and do not understand what you are doing. We would recommend that you try the B-A-B series on a safe, development environment – such as an offline installation of PHP and MySQL – so you can learn how everything works.

GWG and its staffers accept no responsibility for anything that may (or may not) happen to your site or server as a result of you using these tutorials – you do so AT YOUR OWN RISK.

  1. The first thing we need to do is to add a category table to our database. We could just list the categories in the entries table, but this can cause problems if you need to rename a category. Doing it this way means that the category will change everywhere if you edit it once.

    First, we need to connect to MySQL.

    <?php
    mysql_connect('localhost', 'db_username', 'db_password');

    (Remember to add your own details)

    Next we select the database to use:

    mysql_select_db('db_name');

    Second, we tell MySQL to add our table to the database. We will need two columns, one which will be the category ID number, and the other which will be the category name. We will give our category field a limit of 255 characters, since anything longer than that is unlikely. Our ID numbers will be automatically incremented and unique, so we must tell MySQL to do this for us. We will also tell it that the ID will be an unsigned number (i.e., it won’t be negative) and that the number won’t go over 9999 (4 digits, in other words).

    This is the code we will use:

    $result = mysql_query("CREATE TABLE php_blog_categories
    (`category_id` smallint(4) UNSIGNED AUTO_INCREMENT NOT NULL,
    `category_name` varchar(255) NOT NULL DEFAULT '',
    PRIMARY KEY (`category_id`))");

    Because each category ID number relates to the category name, we will assign it as our primary key. This helps MySQL to associate the different IDs to the category names, and this will help us to match entries to categories. It also makes sure that we never have a duplicate ID number.

    We will run that command through MySQL. To check if it worked or not, we will add a snippet to the script, which will check whether the query was successful (true) or not. If it wasn’t, we’d like MySQL to tell us why:

    if ($result == true) echo 'Table created successfully.';
    else 'Table could not be created. ' . mysql_error();

    Then we will close MySQL and PHP.

    mysql_close();
    ?>

    The complete table script should look like this:

    <?php
    mysql_connect('localhost', 'db_username', 'db_password');
    mysql_select_db('db_name');
    
    $result = mysql_query("CREATE TABLE php_blog_categories
    (`category_id` smallint(4) UNSIGNED AUTO_INCREMENT NOT NULL,
    `category_name` varchar(255) NOT NULL DEFAULT '',
    PRIMARY KEY (`category_id`))");
    
    if ($result == true) echo 'Table created successfully.';
    else 'Table could not be created. ' . mysql_error();
    
    mysql_close();
    ?>

    Run this just like you did when creating the initial table in part 1. If you get a success message, you are ready to move onto step 2.

  2. The next thing to do is to add a column to our entries table to show which category a post is in. We will be doing this using the category’s ID number, not the category name. Let’s say we have a category called "work", which has an ID of 1. All entries that are categorised under "work" will have the number 1 in their category column, and MySQL will translate that ID, using the table we just created, to its proper name. It sounds complicated, but it really isn’t!

    We will be altering the entries table just like we did for the password in part 4. We will run a quick snippet, just like the one above, to change the table. We need to tell the table that we would like a new column called category, and that it will have the same details as the category ID table (except that it does not need to be unique – one category can contain many entries). This is the code we will use:

    <?php
    mysql_connect('localhost', 'db_username', 'db_password');
    mysql_select_db('db_name');
    
    $result = mysql_query("ALTER TABLE php_blog ADD `category` smallint(4) UNSIGNED NOT NULL");
    
    if ($result == true) echo 'Table altered successfully.';
    else 'Table could not be altered. ' . mysql_error();
    
    mysql_close();
    ?>

    Run that just like above, and once again if you got a success message you are ready for the next step.

  3. Now we must add a page that will manage categories for us. We want to be able to add, rename and delete categories, and this part will teach you how to do this.

    Here is the code that we will use:

    <?php
    mysql_connect('localhost', 'db_username', 'db_password');
    mysql_select_db('db_name');
    
    if (isset($_POST['add_category'])) {
    
    }
    elseif (isset($_POST['edit_category'])) {
    
    }
    elseif (isset($_POST['delete_category'])) {
    
    }
    else {
    
    }
    
    mysql_close();
    ?>

    Obviously this is not the complete code, it is the outline for what we want to do. First we are checking whether any of the form buttons were pressed, and which one it was. If no button was pressed, we will get the page to show us all our existing categories.

    This is the first thing we will do, even though it comes last in the code structure, as it will help you to see how the categories are processed. It’s also the easiest part ;)

    As I said, the categories will be shown, along with options of what to do with them, by default on the page.

    Find this part in the code structure I posted above:

    else {
    
    }

    Between the { and the }, we will get our categories from the database. This is how we will do it:

    First, let’s get the details from the database.

    $result = mysql_query("SELECT * FROM php_blog_categories");

    Second, let’s close PHP so we can add some HTML code to format the categories before we show them. We also need to create the form that will tell PHP what we want doing with a category.

    ?>
    
    <form action="categories.php" method="post"><p>

    Then we will reopen PHP, and start showing our results in a loop (so that we don’t have to keep repeating the database selection every time). This is the same principle we used when showing the blog entries.

    <?php
    while($row = mysql_fetch_array($result)) {
    ?>

    Here I have closed PHP again, as it will make it easier to see what we are doing. Now we are going to create a list of all the categories, like so:

    <input type="radio" name="category" value="<?php echo $row['category_id']; ?>" /> <?php echo $row['category_name']; ?><br />

    When run by PHP, the HTML will be ‘filled in’, and will look something like this:

    <input type="radio" name="category" value="1" /> Work

    That code will repeat for every category you have.

    Now we go back into PHP and and close the while loop:

    <?php
    }
    ?>

    Then we come out of it again, and add our HTML for the edit and delete buttons and the end of the form. Underneath, we will also add a new form to add a new category.

    </p>
    <p><input type="submit" name="edit_category" id="edit_category" value="Edit selected category" /> <input type="submit" name="delete_category" id="delete_category" value="Delete selected category" /></p>
    
    </form>
    
    <form action="categories.php" method="post">
    <p>Add new category: <input type="text" name="new_category" id="new_category" /> <input type="submit" name="add_category" id="add_category" value="Add category" /></p>
    
    </form>

    We then need to reopen PHP to continue the script.

    <?php

    The complete code for categories.php should look like this now:

    <?php
    mysql_connect('localhost', 'db_username', 'db_password');
    mysql_select_db('db_name');
    
    if (isset($_POST['add_category'])) {
    
    }
    elseif (isset($_POST['edit_category'])) {
    
    }
    elseif (isset($_POST['delete_category'])) {
    
    }
    else {
    
    $result = mysql_query("SELECT * FROM php_blog_categories");
    ?>
    <form action="categories.php" method="post"><p>
    
    <?php
    while($row = mysql_fetch_array($result)) {
    ?>
    
    <input type="radio" name="category" value="<?php echo $row['category_id']; ?>" /> <?php echo $row['category_name']; ?><br />
    
    <?php
    }
    ?>
    
    </p>
    <p><input type="submit" name="edit_category" id="edit_category" value="Edit selected category" /> <input type="submit" name="delete_category" id="delete_category" value="Delete selected category" /></p>
    
    </form>
    
    <form action="categories.php" method="post">
    <p>Add new category: <input type="text" name="new_category" id="new_category" /> <input type="submit" name="add_category" id="add_category" value="Add category" /></p>
    
    </form>
    
    <?php
    }
    
    mysql_close();
    ?>

    At the moment, you will most likely not have any categories, as you have just created the tables, so running the categories.php as it is will probably result in a blank page with just the buttons. Don’t worry! That is normal.

  4. Next we will process category addition.

    Find the following part in your code:

    if (isset($_POST['add_category'])) {
    
    }

    Between those {}s, we will add our processing code.

    First we will strip HTML and convert special characters as both a security precaution and to prevent problems with category names.

    $newcat = htmlspecialchars(strip_tags($_POST['new_category']));

    Next, we need to check if a category was submitted. If the text box was left blank, we will display an error and stop the script from going any further. You might ask why we are doing this after stripping invalid characters from the category – we do it afterwards in case the category was made up only of invalid characters. If the entire category name is stripped out, it will be as if the person didn’t enter anything at all.

    if (empty($newcat)) {
    die("No category submitted! Please go back and enter one.");
    }

    If the category is not empty, the script will be allowed to continue (it will stop and display an error if the category is empty), so we don’t need an else statement for the next part, which is to add the category to the database.

    Before we add the code to the database, we need to escape quotes. On most servers this is done automatically, but if it isn’t, you’ll have problems adding new categories containing quotes.

    First we need to check whether the server will escape the quotes for us. Because we only need to do something if the server doesn’t escape the quotes, that is what we will check for, hence the ! in the if statement.

    if (!get_magic_quotes_gpc()) {
    $newcat = mysql_real_escape_string($newcat);
    }

    mysql_real_escape_string() will escape the quotes for us. It is similar to addslashes() which is used in previous parts of the Build A Blog tutorials, but mysql_real_escape_string() is especially suited for MySQL databases so is better to use than addslashes().

    Here is the code we will use to submit the new category to the database:

    $insert = mysql_query("INSERT INTO php_blog_categories (`category_name`) VALUES ('$newcat')");

    We then check to see if the category was successfully submitted:

    if ($insert == true) echo 'Category successfully added.';
    else echo 'The category could not be added to the database. ' . mysql_error();

    Now our complete code should look like this:

    <?php
    mysql_connect('localhost', 'db_username', 'db_password');
    mysql_select_db('db_name');
    
    if (isset($_POST['add_category'])) {
    
    $newcat = htmlspecialchars(strip_tags($_POST['new_category']));
    
    if (empty($newcat)) {
    die( "No category submitted! Please go back and enter one.");
    }
    
    if (!get_magic_quotes_gpc()) {
    $newcat = mysql_real_escape_string($newcat);
    }
    
    $insert = mysql_query("INSERT INTO php_blog_categories (`category_name`) VALUES ('$newcat')");
    
    if ($insert == true) echo 'Category successfully added.';
    else echo 'The category could not be added to the database. ' . mysql_error();
    
    }
    elseif (isset($_POST['edit_category'])) {
    
    }
    elseif (isset($_POST['delete_category'])) {
    
    }
    else {
    
    $result = mysql_query("SELECT * FROM php_blog_categories");
    ?>
    <form action="categories.php" method="post"><p>
    
    <?php
    while($row = mysql_fetch_array($result)) {
    ?>
    
    <input type="radio" name="category" value="<?php echo $row['category_id']; ?>" /> <?php echo $row['category_name']; ?><br />
    
    <?php
    }
    ?>
    
    </p>
    <p><input type="submit" name="edit_category" id="edit_category" value="Edit selected category" /> <input type="submit" name="delete_category" id="delete_category" value="Delete selected category" /></p>
    
    </form>
    
    <form action="categories.php" method="post">
    <p>Add new category: <input type="text" name="new_category" id="new_category" /> <input type="submit" name="add_category" id="add_category" value="Add category" /></p>
    
    </form>
    
    <?php
    }
    
    mysql_close();
    ?>
  5. The next part we will do is category deletion, as editing is a little bit harder. We will do that part last.

    The code to delete entries is very similar to the code to add entries – we will check to see if a value was submitted, and if that value was a number (the category ID). Then we will take that value, and delete the category to which that ID number corresponds.

    So, find this part in your code:

    elseif (isset($_POST['delete_category'])) {
    
    }

    As usual, we will add our new code between the braces ( { and } ).

    Let’s make sure the category that was submitted is valid (i.e. a number):

    $category = (int)$_POST['category'];

    Next, let’s see if a category was actually chosen for deletion (again, we do this after checking that the category is a number, as if it isn’t it will have been stripped out by the above code and will be equivalent to not having chosen a category to delete).

    if (empty($category)) {
    die("No category chosen! Please go back and choose a category to delete.");
    }

    If the above did not fail, we are sure we have an ID number. Let’s delete its corresponding category from the database:

    $delete = mysql_query("DELETE FROM php_blog_categories WHERE `category_id` = $category LIMIT 1");

    I’ve added a LIMIT 1 to the end, because although we are meant to have unique category ID numbers, if, for whatever reason you end up with two categories with the same ID number, we only want to delete one of the entries. LIMIT 1 will make sure that this happens.

    Now we will test to see if the category was deleted successfully:

    if ($delete == true) echo 'Category successfully deleted.';
    else echo 'The category could not be deleted. ' . mysql_error();

    The complete code should now look like this:

    <?php
    mysql_connect('localhost', 'db_username', 'db_password');
    mysql_select_db('db_name');
    
    if (isset($_POST['add_category'])) {
    
    $newcat = htmlspecialchars(strip_tags($_POST['new_category']));
    
    if (empty($newcat)) {
    die( "No category submitted! Please go back and enter one.");
    }
    
    if (!get_magic_quotes_gpc()) {
    $newcat = mysql_real_escape_string($newcat);
    }
    
    $insert = mysql_query("INSERT INTO php_blog_categories (`category_name`) VALUES ('$newcat')");
    
    if ($insert == true) echo 'Category successfully added.';
    else echo 'The category could not be added to the database. ' . mysql_error();
    
    }
    elseif (isset($_POST['edit_category'])) {
    
    }
    elseif (isset($_POST['delete_category'])) {
    
    $category = (int)$_POST['category'];
    
    if (empty($category)) {
    die("No category chosen! Please go back and choose a category to delete.");
    }
    
    $delete = mysql_query("DELETE FROM php_blog_categories WHERE `category_id` = $category LIMIT 1");
    
    if ($delete == true) echo 'Category successfully deleted.';
    else echo 'The category could not be deleted. ' . mysql_error();
    
    }
    else {
    
    $result = mysql_query("SELECT * FROM php_blog_categories");
    ?>
    
    <form action="categories.php" method="post"><p>
    
    <?php
    while($row = mysql_fetch_array($result)) {
    ?>
    
    <input type="radio" name="category" value="<?php echo $row['category_id']; ?>" /> <?php echo $row['category_name']; ?><br />
    
    <?php
    }
    ?>
    
    </p>
    <p><input type="submit" name="edit_category" id="edit_category" value="Edit selected category" /> <input type="submit" name="delete_category" id="delete_category" value="Delete selected category" /></p>
    
    </form>
    
    <form action="categories.php" method="post">
    <p>Add new category: <input type="text" name="new_category" id="new_category" /> <input type="submit" name="add_category" id="add_category" value="Add category" /></p>
    
    </form>
    
    <?php
    }
    
    mysql_close();
    ?>
  6. The next stage is to edit our categories. This is slightly more complicated than the other steps, as we first need to select the category we want to edit, show a form to edit it, then submit the changes to the database.

    First, find the only remaining "blank" part of your code (i.e. a part which doesn’t do anything), which is this part:

    elseif (isset($_POST['edit_category'])) {
    
    }

    Between the {}s we will start our new code.

    Let’s make sure the category is a number and that it is not empty (this is the exact same process we used for the deleting part):

    $category = (int)$_POST['category'];
    
    if (empty($category)) {
    die ("No category chosen! Please go back and choose a category to edit.");
    }
    
    

    Next we will get the current category name from the database:

    $result = mysql_query("SELECT * FROM php_blog_categories WHERE `category_id` = $category LIMIT 1");
    $row = mysql_fetch_array($result);

    Let’s check that the category actually exists. If it doesn’t, we will get errors everywhere or a blank page (depends on server settings), so let’s tell the user and exit the script before this has a chance to happen.

    if (!$row) {
    die("There doesn't seem to be a category with the ID number submitted. Please go back and try again.");
    }

    The !$row part is short for if ($row == false), which would be the case if no results were found in the database corresponding to the ID number we want.

    We will close PHP now, so we can use HTML properly to display a form for a new category name.

    ?>
    
    <form action="categories.php" method="post">

    We will add a hidden field now. The value of the field will be the ID number of the category, so that the database knows which category this is when the form is submitted; the name of the field will be the section of the script we are in, which is edit_category. This will enable the script to know where we are once the form has submitted.

    <p><input type="hidden" name="edit_category" value="<?php echo $row['category_id']; ?>" />

    Now we will show a field for entering a new category name, along with some text which tells us what the category is currently called.

    The current category name is <?php echo $row['category_name']; ?>. To change it, enter a new name in the box below.<br />
    Rename category to: <input type="text" name="new_name" id="new_name" /><br />
    
    <input type="submit" name="submit_category_edit" id="submit_category_edit" value="Submit new name" /></p>

    Finally for this part, we need to reopen PHP for the script to continue:

    <?php

    We have now created our category editing form, but it is of no use without the processing part which we will do next.

    Your edit category part should look like this:

    elseif (isset($_POST['edit_category'])) {
    
    $category = (int)$_POST['category'];
    
    if (empty($category)) {
    die ("No category chosen! Please go back and choose a category to edit.");
    }
    
    $result = mysql_query("SELECT * FROM php_blog_categories WHERE `category_id` = $category LIMIT 1");
    $row = mysql_fetch_array($result);
    
    if (!$row) {
    die("There doesn't seem to be a category with the ID number submitted. Please go back and try again.");
    }
    ?>
    
    <form action="categories.php" method="post">
    
    <p><input type="hidden" name="edit_category" value="<?php echo $row['category_id']; ?>" />
    
    The current category name is <?php echo $row['category_name']; ?>. To change it, enter a new name in the box below.<br />
    
    Rename category to: <input type="text" name="new_name" id="new_name" /><br />
    <input type="submit" name="submit_category_edit" id="submit_category_edit" value="Submit new name" /></p>
    
    <?php
    
    }

    The next part will go ABOVE what we have just done. In other words, it will go underneath this line:

    elseif (isset($_POST['edit_category'])) {

    But above this one:

    $category = (int)$_POST['category'];

    What we will do now is check to see if the category has been renamed, and if it has, we will change it in the database. This is almost exactly the same process as the category addition section.

    First we will check whether we have pressed to rename our category, or whether want to display the form to rename it (note that there is no closing brace on the end of this line, we will add this once we have finished processing the category).

    if (isset($_POST['submit_category_edit'])) {

    Now we check that the category name is appropriate, by stripping HTML and converting entities:

    $newcat = htmlspecialchars(strip_tags($_POST['new_name']));

    Next, we check to see if a category name was submitted:

    if (empty($newcat)) {
    die("No new category name entered, please go back and try again.");
    }

    Now we will check to see if a category ID was submitted, making sure it is numeric first.

    $id = (int)$_POST['edit_category'];
    
    if (empty($id)) {
    die("Invalid category!");
    }

    Once we are sure we have all the parts we need, we need to update the category details in the database. However, we should escape any quotes first, as we did for the category addition part:

    if (!get_magic_quotes_gpc()) {
    $newcat = mysql_real_escape_string($newcat);
    }

    This is the code we shall use update the database:

    $edit = mysql_query("UPDATE php_blog_categories SET `category_name` = '$newcat' WHERE `category_id` = $id LIMIT 1");
    
    if ($edit == true) echo 'Category successfully edited.';
    else echo 'Category could not be edited. ' . mysql_error();

    The last thing we will do is to close the if statement we began earlier, to test whether we were updating the category or not. We do this by adding a closing brace.

    }

    We also need to separate the processing part from the editing form, otherwise we will have both happening at once. So we will add an else statement after the closing brace mentioned above, like so:

    else {

    And then a closing brace just before this line:

    elseif (isset($_POST['delete_category'])) {

    The complete edit block should look like this:

    elseif (isset($_POST['edit_category'])) {
    
    if (isset($_POST['submit_category_edit'])) {
    
    $newcat = htmlspecialchars(strip_tags($_POST['new_name']));
    
    if (empty($newcat)) {
    die("No new category name entered, please go back and try again.");
    }
    
    $id = (int)$_POST['edit_category'];
    
    if (empty($id)) {
    die("Invalid category!");
    }
    
    if (!get_magic_quotes_gpc()) {
    $newcat = mysql_real_escape_string($newcat);
    }
    
    $edit = mysql_query("UPDATE php_blog_categories SET `category_name` = '$newcat' WHERE `category_id` = $id LIMIT 1");
    
    if ($edit == true) echo 'Category successfully edited.';
    else echo 'Category could not be edited. ' . mysql_error();
    
    }
    else {
    
    $category = (int)$_POST['category'];
    
    if (empty($category)) {
    die ("No category chosen! Please go back and choose a category to edit.");
    }
    
    $result = mysql_query("SELECT * FROM php_blog_categories WHERE `category_id` = $category LIMIT 1");
    $row = mysql_fetch_array($result);
    
    if (!$row) {
    die("There doesn't seem to be a category with the ID number submitted. Please go back and try again.");
    }
    ?>
    
    <form action="categories.php" method="post">
    <p><input type="hidden" name="edit_category" value="<?php echo $row['category_id']; ?>" />
    
    The current category name is <?php echo $row['category_name']; ?>. To change it, enter a new name in the box below.<br />
    Rename category to: <input type="text" name="new_name" id="new_name" /><br />
    
    <input type="submit" name="submit_category_edit" id="submit_category_edit" value="Submit new name" /></p>
    </form>
    
    <?php
    
    }
    
    }

    And that’s the category management part done!

    categories.php should look like this when completed:

    <?php
    mysql_connect('localhost', 'db_username', 'db_password');
    mysql_select_db('db_name');
    
    if (isset($_POST['add_category'])) {
    
        $newcat = htmlspecialchars(strip_tags($_POST['new_category']));
    
        if (empty($newcat)) {
            die( "No category submitted! Please go back and enter one.");
        }
    
        if (!get_magic_quotes_gpc()) {
            $newcat = mysql_real_escape_string($newcat);
        }
    
        $insert = mysql_query("INSERT INTO php_blog_categories (`category_name`) VALUES ('$newcat')");
    
        if ($insert == true) echo 'Category successfully added.';
        else echo 'The category could not be added to the database. ' . mysql_error();
    
    }
    elseif (isset($_POST['edit_category'])) {
    
        if (isset($_POST['submit_category_edit'])) {
    
            $newcat = htmlspecialchars(strip_tags($_POST['new_name']));
    
            if (empty($newcat)) {
                die("No new category name entered, please go back and try again.");
            }
    
            $id = (int)$_POST['edit_category'];
    
            if (empty($id)) {
                die("Invalid category!");
            }
    
            if (!get_magic_quotes_gpc()) {
                $newcat = mysql_real_escape_string($newcat);
            }
    
            $edit = mysql_query("UPDATE php_blog_categories SET `category_name` = '$newcat' WHERE `category_id` = $id LIMIT 1");
    
            if ($edit == true) echo 'Category successfully edited.';
            else echo 'Category could not be edited. ' . mysql_error();
    
        }
        else {
    
            $category = (int)$_POST['category'];
    
            if (empty($category)) {
                die ("No category chosen! Please go back and choose a category to edit.");
            }
    
            $result = mysql_query("SELECT * FROM php_blog_categories WHERE `category_id` = $category LIMIT 1");
            $row = mysql_fetch_array($result);
    
            if (!$row) {
                die("There doesn't seem to be a category with the ID number submitted. Please go back and try again.");
            }
            ?>
    
            <form action="categories.php" method="post">
            <p><input type="hidden" name="edit_category" value="<?php echo $row['category_id']; ?>" />
    
            The current category name is <?php echo $row['category_name']; ?>. To change it, enter a new name in the box below.<br />
            Rename category to: <input type="text" name="new_name" id="new_name" /><br />
    
            <input type="submit" name="submit_category_edit" id="submit_category_edit" value="Submit new name" /></p>
            </form>
    
            <?php
    
        }
    
    }
    elseif (isset($_POST['delete_category'])) {
    
        $category = (int)$_POST['category'];
    
        if (empty($category)) {
            die("No category chosen! Please go back and choose a category to delete.");
        }
    
        $delete = mysql_query("DELETE FROM php_blog_categories WHERE `category_id` = $category LIMIT 1");
    
        if ($delete == true) echo 'Category successfully deleted.';
        else echo 'The category could not be deleted. ' . mysql_error();
    
    }
    else {
    
        $result = mysql_query("SELECT * FROM php_blog_categories");
        ?>
        <form action="categories.php" method="post"><p>
    
        <?php
        while($row = mysql_fetch_array($result)) {
            ?>
    
            <input type="radio" name="category" value="<?php echo $row['category_id']; ?>" /> <?php echo $row['category_name']; ?><br />
    
            <?php
        }
        ?>
    
        </p>
        <p><input type="submit" name="edit_category" id="edit_category" value="Edit selected category" /> <input type="submit" name="delete_category" id="delete_category" value="Delete selected category" /></p>
    
        </form>
    
        <form action="categories.php" method="post">
        <p>Add new category: <input type="text" name="new_category" id="new_category" /> <input type="submit" name="add_category" id="add_category" value="Add category" /></p>
    
        </form>
    
        <?php
    }
    
    mysql_close();
    ?>
  7. Now we need to implement categories into our new post and edit post pages. We will start with the new post page.

    Find the following part in your entry posting page:

    <p><strong><label for="title">Title:</label></strong> <input type="text" name="title" id="title" size="40" /></p>

    Above that, we will add our category selection box. By default this may look slightly odd on your page, especially if you have a long category name, but you can easily change this using CSS.

    We will start by getting our categories from the database. We don’t have a database connection on this page yet, so we will insert one here:

    <?php
    mysql_connect('localhost', 'db_username', 'db_password');
    mysql_select_db('db_name');

    Let’s select the categories and display them in a loop:

    $result = mysql_query("SELECT * FROM php_blog_categories");
    
    echo '<p><strong><label for="category">Category:</label></strong> <select name="category" id="category">';
    
    while($row = mysql_fetch_array($result)) { ?>
    
    <option value="<?php echo $row['category_id']; ?>"><?php echo $row['category_name']; ?></option>
    
    <?php
    }
    ?>
    
    </select></p>

    Now find this part in your post page:

    $entry = nl2br($entry);

    Above that, we will add our category processing, and we will make sure it’s a number (category ID):

    $category = (int)$_POST['category'];

    Next find this:

    $sql = "INSERT INTO php_blog (timestamp,title,entry) VALUES ('$timestamp','$title','$entry')";

    (This may look slightly different if you have password protection or custom fields enabled)

    You need to add the category field to this query.

    $sql = "INSERT INTO php_blog (timestamp,title,entry,category) VALUES ('$timestamp','$title','$entry','$category')";
  8. Now that we have added the category to new posts, we need to be able to add it to our edit posts page as well, so we can change the category if needs be.

    Please note that you can only have ONE category per entry. This tutorial will not teach you how to give your entries multiple categories (or "tags").

    In your update entry page, find the following code:

    $old_timestamp = $row['timestamp'];
    $old_title = stripslashes($row['title']);
    $old_entry = stripslashes($row['entry']);
    $old_password = $row['password'];

    Add this part to the end:

    $old_category = $row['category'];

    Next, find this part:

    <p><strong><label for="title">Title:</label></strong> <input type="text" name="title" id="title" value="<?php echo $old_title; ?>" size="40" /></p>

    Above that, we will add our categories just like we did to the post entry form. We don’t need to open a database connection since we already have one, so we will get straight to it. However, we need to make sure the current category of the entry is selected; we will do this by testing each category to see if it matches the category ID stored for that post. If it matches, we will insert the proper HTML to select the current category by default.

    Here is the code for the categories:

    <?php
    $result2 = mysql_query("SELECT * FROM php_blog_categories");
    
    echo '<p><strong><label for="category">Category:</label></strong> <select name="category" id="category">';
    
    while($row2 = mysql_fetch_array($result2)) { ?>
    
        <option value="<?php echo $row2['category_id']; ?>" <?php if ($old_category == $row2['category_id']) echo ' selected="selected"'; ?>><?php echo $row2['category_name']; ?></option>
        <?php
    }
    ?>
    </select></p>

    We need to call this $row2 and $result2 rather than our normal $result and $row, since we already have a $result and $row – those are related to the entry (not the categories), and we don’t want to overwrite them.

    Now we must process the category update. This is identical to what we did to process the category in the new post page – first we look for this part in the code:

    $entry = nl2br($entry);

    Underneath it, we add:

    $category = (int)$_POST['category'];

    Now is where it differs slightly. Find this part:

    $result = mysql_query("UPDATE php_blog SET timestamp='$timestamp', title='$title', entry='$entry', password='$password' WHERE id='$id' LIMIT 1") or print ("Can't update entry.<br />" . mysql_error());

    (Again, this will differ depending on which fields you have in your database, and whether you are using password protection or not)

    Let’s add the category update to that query:

    $result = mysql_query("UPDATE php_blog SET timestamp='$timestamp', title='$title', entry='$entry', password='$password', category='$category' WHERE id='$id' LIMIT 1") or print ("Can't update entry.<br />" . mysql_error());

    And we’re done!

  9. But of course we’re not completely done yet – we want to show the category of a post on our blog front page, and on an individual entry.

    To do this, find this part in both your main blog page and your individual entry page (it will appear several times in your individual entry page, and again will look different depending on the fields you have):

        $title = stripslashes($row['title']);
        $entry = stripslashes($row['entry']);
        $password = $row['password'];

    Underneath this, we need to get our categories. We have them stored by ID in the entries table, so we need to convert them to their names.

    To do this, we need to get the name from the categories table, using the ID stored in the entries table, like so:

    $get_categories = mysql_query("SELECT * FROM php_blog_categories WHERE `category_id` = $row[category]");
    $category = mysql_fetch_array($get_categories);

    Now we are ready to display the category. Find these parts in your blog and individual entry pages (again, this may appear several times:

    <p><strong><?php echo $title; ?></strong><br /><br />
            <?php echo $entry; ?><br /><br />
    
            Posted on <?php echo $date; ?></p>

    Just add the category where you want it to show up. For example:

    <p><strong><?php echo $title; ?></strong><br /><br />
            <?php echo $entry; ?><br /><br />
    
            Posted in <?php echo $category['category_name']; ?> on <?php echo $date; ?></p>
  10. …You probably want to show all the posts in one category now, don’t you? Well if you do, you can change the code above to link the category to its archives, like so:

    Posted in <a href="category.php?category=<?php echo $row['category']; ?>"><?php echo $category['category_name']; ?></a> on <?php echo $date; ?>

    We will now make a category archive page, which will look pretty much exactly like the yearly archive page. We will call this page category.php (note that this is different from your category admin page, which is called categories.php. You can of course rename these files to anything you like, but in this example I will use category.php as the category archive page.

    Let’s start with the MySQL connection:

    <?php
    mysql_connect('localhost', 'db_username', 'db_password') ;
    mysql_select_db('db_name');

    We now need to check what category we are displaying, which is decided by the ?category= part in the URL. If someone has gone directly to category.php without specifying a category to display, we need to display an error just like we do in the single entry page and the archive page, when the person hasn’t specified a year or entry ID to look at.

    if (!isset($_GET['category'])) {
        die("Invalid category specified.");
    }
    else {
        $category = (int)$_GET['category'];
    }

    Now that we have the category we want to display entries from, let’s collect the posts from the entries table:

    $result = mysql_query("SELECT timestamp, id, title FROM php_blog WHERE category = $category ORDER BY id DESC");

    We will now add up how many posts were found:

    $num = mysql_num_rows($result);

    …and get the category name from the categories table:

    $get_category = mysql_query("SELECT * FROM php_blog_categories WHERE category_id = $category");
    $get_category2 = mysql_fetch_array($get_category);

    Now we can use those details as a header, for example you might want to say something like this:

    echo "<h1>There are $num posts in the &quot;$get_category2[category_name]&quot;category</h1>"

    Let’s now show the posts. This is identical to the archive page entry code.

    while ($row = mysql_fetch_array($result)) {
        $date = date("l F d Y", $row['timestamp']);
        $id = $row['id'];
        $title = stripslashes($row['title']);
    
        ?>
    
        <p><?php echo $date; ?><br /><a href="journal.php?id=<?php echo $id; ?>"><?php echo $title; ?></a></p>
    
        <?php
    }
    
    ?>

    That’s the category archive done. The complete code should look like this:

    <?php
    mysql_connect('localhost', 'db_username', 'db_password') ;
    mysql_select_db('db_name');
    
    if (!isset($_GET['category'])) {
        die("Invalid category specified.");
    }
    else {
        $category = (int)$_GET['category'];
    }
    
    $result = mysql_query("SELECT timestamp, id, title FROM php_blog WHERE category = $category ORDER BY id DESC");
    
    $num = mysql_num_rows($result);
    
    $get_category = mysql_query("SELECT * FROM php_blog_categories WHERE category_id = $category");
    $get_category2 = mysql_fetch_array($get_category);
    
    echo "<h1>There are $num posts in the &quot;$get_category2[category_name]&quot;category</h1>";
    
    while($row = mysql_fetch_array($result)) {
        $date = date("l F d Y", $row['timestamp']);
        $id = $row['id'];
        $title = stripslashes($row['title']);
    
        ?>
    
        <p><?php echo $date; ?><br /><a href="journal.php?id=<?php echo $id; ?>"><?php echo $title; ?></a></p>
        <?php
    }
    
    ?>
  11. You may want a little block to display all your categories and the number of posts in them, just like for the yearly archives. If you’d like to do this, here is how.

    First, let’s connect to the database:

    <?php
    mysql_connect('localhost', 'db_username', 'db_password') ;
    mysql_select_db('db_name');

    Next, let’s get our categories from the database:

    $result1 = mysql_query("SELECT * FROM php_blog_categories ORDER BY category_name ASC");

    Now let’s display the categories and link them to the archives:

    while($row = mysql_fetch_array($result1)) {

    Ah, before we can do that, we need our posts! We need to get them while inside the loop, as we need a different post count for each category.

    $result2 = mysql_query("SELECT COUNT(`id`) AS entries FROM php_blog WHERE category = $row[category_id]");
    $num_entries = mysql_fetch_array($result2);

    Now we can proceed with showing the posts!

        echo '<a href="category.php?category=' . $row['category_id'] . '">' . $row['category_name'] . '</a> (' . $num_entries['entries'] . ')<br />';

    Let’s close that while loop and PHP in general:

    }
    ?>

    Your category archive block should look like this:

    <?php
    mysql_connect('localhost', 'db_username', 'db_password') ;
    mysql_select_db('db_name');
    
    $result1 = mysql_query("SELECT * FROM php_blog_categories ORDER BY category_name ASC");
    
    while($row = mysql_fetch_array($result1)) {
    
        $result2 = mysql_query("SELECT COUNT(`id`) AS entries FROM php_blog WHERE category = $row[category_id]");
        $num_entries = mysql_fetch_array($result2);
    
        echo '<a href="category.php?category=' . $row['category_id'] . '">' . $row['category_name'] . '</a> (' . $num_entries['entries'] . ')<br />';
    
    }
    ?>

And that’s it! Now you should have categories fully enabled in your blog!