Demos, Blog, PHP & MySQL, Tutorials

Blog Series

li>Part 7 Tags

This tutorial is extending the Creating a blog from scratch with PHP with that in mind I will only be covering new pieces of code and not the whole codebase.

This part will cover adding categories to posts.

admin demo: http://www.daveismyname.blog/demos/simpleblog-cats/admin

username: demo
password: demo

Create a new table in the database called blog_cats this table will store the categories, with the following structure:

Another table is also required to store the postID and catID for all posts that have a category selected. Create a table called blog_cat_cats with the following structure:

Next update .htaccess another rule needs to be added for viewing posts by category, ant request for c- the the post slug will be passed to the catpost.php page.

RewriteEngine On
RewriteBase /simpleblog-cats/

RewriteRule ^c-(.*)$ catpost.php?id=$1 [L]

RewriteCond %{REQUEST_FILENAME} !-d [NC]
RewriteCond %{REQUEST_FILENAME} !-f [NC]
RewriteRule ^(.*)$ viewpost.php?id=$1 [QSA,L]

Next open index.php 

Change 

echo '<p>Posted on '.date('jS M Y H:i:s', strtotime($row['postDate']));

To

echo '<p>Posted on '.date('jS M Y H:i:s', strtotime($row['postDate'])).' in ';

$stmt2 = $db->prepare('SELECT catTitle, catSlug    FROM blog_cats, blog_post_cats WHERE blog_cats.catID = blog_post_cats.catID AND blog_post_cats.postID = :postID');
$stmt2->execute(array(':postID' => $row['postID']));

$catRow = $stmt2->fetchAll(PDO::FETCH_ASSOC);
$links = array();
foreach ($catRow as $cat){
     $links[] = "<a href='c-".$cat['catSlug']."'>".$cat['catTitle']."</a>";
}
echo implode(", ", $links);

echo '</p>';

The above select the relevant category from the blog_cats table where the postID matches the postID in the blog_post_cats table, the category is then wrapped inside a link, to give the ability to see all posts tagged with that category. 

Next create a new file called catpost.php the code will be almost identical to index.php this file will show all posts that belong to the passed category if no category if found the user is taken back to index.php.

<?php require('includes/config.php'); 


$stmt = $db->prepare('SELECT catID,catTitle FROM blog_cats WHERE catSlug = :catSlug');
$stmt->execute(array(':catSlug' => $_GET['id']));
$row = $stmt->fetch();

//if post does not exists redirect user.
if($row['catID'] == ''){
    header('Location: ./');
    exit;
}

?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title>Blog - <?php echo $row['catTitle'];?></title>
    <link rel="stylesheet" href="style/normalize.css">
    <link rel="stylesheet" href="style/main.css">
</head>
<body>

    <div id="wrapper">

        <h1>Blog</h1>
        <p>Posts in <?php echo $row['catTitle'];?></p>
        <hr />
        <p><a href="./">Blog Index</a></p>

        <?php    
        try {

            $stmt = $db->prepare('
                SELECT 
                    blog_posts_seo.postID, blog_posts_seo.postTitle, blog_posts_seo.postSlug, blog_posts_seo.postDesc, blog_posts_seo.postDate 
                FROM 
                    blog_posts_seo,
                    blog_post_cats
                WHERE
                     blog_posts_seo.postID = blog_post_cats.postID
                     AND blog_post_cats.catID = :catID
                ORDER BY 
                    postID DESC
                ');
            $stmt->execute(array(':catID' => $row['catID']));
            while($row = $stmt->fetch()){
                
                echo '<div>';
                    echo '<h1><a href="'.$row['postSlug'].'">'.$row['postTitle'].'</a></h1>';
                    echo '<p>Posted on '.date('jS M Y H:i:s', strtotime($row['postDate'])).' in ';

                        $stmt2 = $db->prepare('SELECT catTitle, catSlug    FROM blog_cats, blog_post_cats WHERE blog_cats.catID = blog_post_cats.catID AND blog_post_cats.postID = :postID');
                        $stmt2->execute(array(':postID' => $row['postID']));

                        $catRow = $stmt2->fetchAll(PDO::FETCH_ASSOC);

                        $links = array();
                        foreach ($catRow as $cat)
                        {
                            $links[] = "<a href='c-".$cat['catSlug']."'>".$cat['catTitle']."</a>";
                        }
                        echo implode(", ", $links);

                    echo '</p>';
                    echo '<p>'.$row['postDesc'].'</p>';                
                    echo '<p><a href="'.$row['postSlug'].'">Read More</a></p>';                
                echo '</div>';

            }

        } catch(PDOException $e) {
            echo $e->getMessage();
        }

        ?>

    </div>


</body>
</html>

With that in place the public side of the blog is complete. 

Admin

Open menu.php in the admin folder and add another link for categories, the link will point to categories.php

<h1>Blog</h1>
<ul id='adminmenu'>
    <li><a href='index.php'>Blog</a></li>
    <li><a href='categories.php'>Categories</a></li>
    <li><a href='users.php'>Users</a></li>
    <li><a href="../" target="_blank">View Website</a></li>
    <li><a href='logout.php'>Logout</a></li>
</ul>
<div class='clear'></div>
<hr />

Create a new file called categories.php and place the following code. This page will list all categories and have edit,delete and add new category links.

<?php
//include config
require_once('../includes/config.php');

//if not logged in redirect to login page
if(!$user->is_logged_in()){ header('Location: login.php'); }

//show message from add / edit page
if(isset($_GET['delcat'])){ 

    $stmt = $db->prepare('DELETE FROM blog_cats WHERE catID = :catID') ;
    $stmt->execute(array(':catID' => $_GET['delcat']));

    header('Location: categories.php?action=deleted');
    exit;
} 

?>
<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>Admin</title>
  <link rel="stylesheet" href="../style/normalize.css">
  <link rel="stylesheet" href="../style/main.css">
  <script language="JavaScript" type="text/javascript">
  function delcat(id, title)
  {
      if (confirm("Are you sure you want to delete '" + title + "'"))
      {
          window.location.href = 'categories.php?delcat=' + id;
      }
  }
  </script>
</head>
<body>

    <div id="wrapper">

    <?php include('menu.php');?>

    <?php 
    //show message from add / edit page
    if(isset($_GET['action'])){ 
        echo '<h3>Category '.$_GET['action'].'.</h3>'; 
    } 
    ?>

    <table>
    <tr>
        <th>Title</th>
        <th>Action</th>
    </tr>
    <?php
        try {

            $stmt = $db->query('SELECT catID, catTitle, catSlug FROM blog_cats ORDER BY catTitle DESC');
            while($row = $stmt->fetch()){
                
                echo '<tr>';
                echo '<td>'.$row['catTitle'].'</td>';
                ?>

                <td>
                    <a href="edit-category.php?id=<?php echo $row['catID'];?>">Edit</a> | 
                    <a href="javascript:delcat('<?php echo $row['catID'];?>','<?php echo $row['catSlug'];?>')">Delete</a>
                </td>
                
                <?php 
                echo '</tr>';

            }

        } catch(PDOException $e) {
            echo $e->getMessage();
        }
    ?>
    </table>

    <p><a href='add-category.php'>Add Category</a></p>

</div>

</body>
</html>

Next create add-category.php

<?php //include config
require_once('../includes/config.php');

//if not logged in redirect to login page
if(!$user->is_logged_in()){ header('Location: login.php'); }
?>
<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>Admin - Add Category</title>
  <link rel="stylesheet" href="../style/normalize.css">
  <link rel="stylesheet" href="../style/main.css">
</head>
<body>

<div id="wrapper">

    <?php include('menu.php');?>
    <p><a href="categories.php">Categories Index</a></p>

    <h2>Add Category</h2>

    <?php

    //if form has been submitted process it
    if(isset($_POST['submit'])){

        $_POST = array_map( 'stripslashes', $_POST );

        //collect form data
        extract($_POST);

        //very basic validation
        if($catTitle ==''){
            $error[] = 'Please enter the Category.';
        }

        if(!isset($error)){

            try {

                $catSlug = slug($catTitle);

                //insert into database
                $stmt = $db->prepare('INSERT INTO blog_cats (catTitle,catSlug) VALUES (:catTitle, :catSlug)') ;
                $stmt->execute(array(
                    ':catTitle' => $catTitle,
                    ':catSlug' => $catSlug
                ));

                //redirect to index page
                header('Location: categories.php?action=added');
                exit;

            } catch(PDOException $e) {
                echo $e->getMessage();
            }

        }

    }

    //check for any errors
    if(isset($error)){
        foreach($error as $error){
            echo '<p class="error">'.$error.'</p>';
        }
    }
    ?>

    <form action='' method='post'>

        <p><label>Title</label><br />
        <input type='text' name='catTitle' value='<?php if(isset($error)){ echo $_POST['catTitle'];}?>'></p>

        <p><input type='submit' name='submit' value='Submit'></p>

    </form>

</div>

Then create edit-category.php

<?php //include config
require_once('../includes/config.php');

//if not logged in redirect to login page
if(!$user->is_logged_in()){ header('Location: login.php'); }
?>
<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>Admin - Edit Category</title>
  <link rel="stylesheet" href="../style/normalize.css">
  <link rel="stylesheet" href="../style/main.css">
</head>
<body>

<div id="wrapper">

    <?php include('menu.php');?>
    <p><a href="categories.php">Categories Index</a></p>

    <h2>Edit Category</h2>


    <?php

    //if form has been submitted process it
    if(isset($_POST['submit'])){

        $_POST = array_map( 'stripslashes', $_POST );

        //collect form data
        extract($_POST);

        //very basic validation
        if($catID ==''){
            $error[] = 'This post is missing a valid id!.';
        }

        if($catTitle ==''){
            $error[] = 'Please enter the title.';
        }

        if(!isset($error)){

            try {

                $catSlug = slug($catTitle);

                //insert into database
                $stmt = $db->prepare('UPDATE blog_cats SET catTitle = :catTitle, catSlug = :catSlug WHERE catID = :catID') ;
                $stmt->execute(array(
                    ':catTitle' => $catTitle,
                    ':catSlug' => $catSlug,
                    ':catID' => $catID
                ));

                //redirect to index page
                header('Location: categories.php?action=updated');
                exit;

            } catch(PDOException $e) {
                echo $e->getMessage();
            }

        }

    }

    ?>


    <?php
    //check for any errors
    if(isset($error)){
        foreach($error as $error){
            echo $error.'<br />';
        }
    }

        try {

            $stmt = $db->prepare('SELECT catID, catTitle FROM blog_cats WHERE catID = :catID') ;
            $stmt->execute(array(':catID' => $_GET['id']));
            $row = $stmt->fetch(); 

        } catch(PDOException $e) {
            echo $e->getMessage();
        }

    ?>

    <form action='' method='post'>
        <input type='hidden' name='catID' value='<?php echo $row['catID'];?>'>

        <p><label>Title</label><br />
        <input type='text' name='catTitle' value='<?php echo $row['catTitle'];?>'></p>

        <p><input type='submit' name='submit' value='Update'></p>

    </form>

</div>

</body>
</html>    
</pre>

<p>In admin/index.php the delete code on line 9 will need to include the deletion of categories added to the deleted posts in the blog_post_cats table:</p>

<pre lang="php">
if(isset($_GET['delpost'])){ 

    $stmt = $db->prepare('DELETE FROM blog_posts_seo WHERE postID = :postID') ;
    $stmt->execute(array(':postID' => $_GET['delpost']));

    //delete post categories. 
    $stmt = $db->prepare('DELETE FROM blog_post_cats WHERE postID = :postID');
    $stmt->execute(array(':postID' => $_GET['delpost']));

    header('Location: index.php?action=deleted');
    exit;
}

Next the add and edit post pages will need to include the categories, start by opening add-post.php then add the following to line 71 after the insert query has ran.

$postID = $db->lastInsertId();

//add categories
if(is_array($catID)){
    foreach($_POST['catID'] as $catID){
        $stmt = $db->prepare('INSERT INTO blog_post_cats (postID,catID)VALUES(:postID,:catID)');
        $stmt->execute(array(
            ':postID' => $postID,
            ':catID' => $catID
        ));
    }
}

This will get the id of the post inserted to the database then add the selected categories along with the postID and add them to the blog_post_cats table.

Next add the following above the submit button in the form:

<fieldset>
    <legend>Categories</legend>

    <?php    

    $stmt2 = $db->query('SELECT catID, catTitle FROM blog_cats ORDER BY catTitle');
    while($row2 = $stmt2->fetch()){

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

            if(in_array($row2['catID'], $_POST['catID'])){
               $checked="checked='checked'";
            }else{
               $checked = null;
            }
        }

        echo "<input type='checkbox' name='catID[]' value='".$row2['catID']."' $checked> ".$row2['catTitle']."<br />";
    }

    ?>

</fieldset>

Each category will be listed with a checkbox if ticket the category will be saved to the post, if there is an error on the page the ticked checkbox will remain ticked by checking the posted catID is in the catID array.

Open edit-post.php after the update has ran on line 76 add the following:

//delete all items with the current postID
$stmt = $db->prepare('DELETE FROM blog_post_cats WHERE postID = :postID');
$stmt->execute(array(':postID' => $postID));

if(is_array($catID)){
    foreach($_POST['catID'] as $catID){
        $stmt = $db->prepare('INSERT INTO blog_post_cats (postID,catID)VALUES(:postID,:catID)');
        $stmt->execute(array(
            ':postID' => $postID,
            ':catID' => $catID
        ));
    }
}

This will delete all records from the table for this post only then insert the newly selected categories.

Above the submit button in the form add the following to show all categories and the checked checkboxes for the selected categories.

<fieldset>
    <legend>Categories</legend>

    <?php

    $stmt2 = $db->query('SELECT catID, catTitle FROM blog_cats ORDER BY catTitle');
    while($row2 = $stmt2->fetch()){

        $stmt3 = $db->prepare('SELECT catID FROM blog_post_cats WHERE catID = :catID AND postID = :postID') ;
        $stmt3->execute(array(':catID' => $row2['catID'], ':postID' => $row['postID']));
        $row3 = $stmt3->fetch(); 

        if($row3['catID'] == $row2['catID']){
            $checked = 'checked=checked';
        } else {
            $checked = null;
        }

        echo "<input type='checkbox' name='catID[]' value='".$row2['catID']."' $checked> ".$row2['catTitle']."<br />";
    }

    ?>

</fieldset>

That's it all changes completed, Now categories can be created and assigned to multiple posts. All code has been updated in the download file.