In yesterday’s section of the tutorial we considered the basic layout, CSS and database structure. Today we are going to build the initial code that will fetch posts and other information. The previous tutorial ended looking like this:
Clearly there wasn’t any information so we need to begin adding things. The first thing we should do before doing anything else is to add some sample data to the database so that we can begin creating code that works. I added the following:
- A blog post with the title ‘My first blog post!’ and the link My_first_blog_post along with some basic content
- I added the category ‘life’ to the category table by adding a row with Id 1, Category life and Post 1 (links with the post table)
- I added some tags the same way I added categories
The next simple bit of code to be run is to fetch a category list to display on the sidebar. I wrote the following code in code.php:
$pdo = new PDO("mysql:host=localhost;dbname=myblog", "root", "");
$pageTitle = "My Blog";
$pageContent = "";
$categoryList = "<ul class=\"category-list\">";
foreach ($pdo->query("SELECT DISTINCT Category FROM categories ORDER BY Category ASC")->fetchAll() as $cat)
{
$categoryList .= "<li><a href=\"http://localhost/myblog/category/" . $cat[0] . "\">" . $cat[0] . "</a></li>";
}
$categoryList .= "</ul>";
Here’s a quick walkthrough of the code:
- First we create a new PHP Data Object which is connected to the database we created. If you haven’t used PDO before it maybe worth looking it up in the PHP Manual
- We then set default values for the variables and start an HTML list element in the $categoryList variable
- We then loop through all the unique values in alphabetical order in the categories table and add them as a list item and link
- We finish the list off
The end result of my sidebar is the following (I added some additional styling and layout stuff):
In the database I had all the category names as lowercase however CSS can style these as an uppercase letter at the beginning of each word. Now we need to look back at how the URLs work.
We have already established that the server will direct everything that doesn’t exist already into index.php to give us clean URLs. We therefore need to write some code that will interpret the requested URL. We also need to consider that we are including an RSS feed feature which will mean that people can subscribe to the site. I have therefore including the code that will generate the feed:
$url = explode("/", substr($_SERVER['REQUEST_URI'], strpos($_SERVER['REQUEST_URI'], "myblog/")+7));
if ($url[0] == "feed" || $url[0] == "rss")
{
header("Content-type:application/rss+xml; charset=ISO-8859-1");
echo "<?xml version=\"1.0\"?>\n";
echo "<rss version=\"2.0\">\n";
echo "<channel>\n";
echo "<title>My Blog</title>\n";
echo "<link>http://localhost/myblog/</link>\n";
echo "<description>The source of information about my life!</description>\n";
echo "<language>en-us</language>\n";
foreach ($pdo->query("SELECT * FROM posts ORDER BY Posted DESC LIMIT 0, 20")->fetchAll() as $row)
{
echo "<item>\n";
echo "<title>" . $row['Title'] . "</title>\n";
echo "<link>http://localhost/myblog/post/" . $row['Link'] . "</link>\n";
echo "<description><![CDATA[" . $row['Content'] . "]]></description>\n";
echo "<pubDate>" . date('D, d M Y H:i:s O', strtotime($row['Posted'])) . "</pubDate>\n";
echo "</item>\n";
}
echo "</channel>\n</rss>";
exit;
}
else
{
}
I won’t explain what all the code that generates individual items of XML does, but here is roughly what happens:
- We get everything in the URL after the myblog/ part and split it where there is a forward slash character
- We check if the first part of the URL is feed or rss and if it is send an XML header
- Echo the initial required data about the feed
- Loop through the twenty most recent items and print the required details – note that the date has to be first passed into a PHP date and then into a valid RSS date
- If the feed wasn’t requested, continue running code.
The next stage of code is rather massive but gives us the core stuff for the blog and goes inside that last else statement. I won’t chunk it out into sections so here is the block:
if (isset($_GET['p'])) $page = ((int)$_GET['p'])-1;
else $page = 0;
if ($page < 0) $page = 0;
if ($url[0] == "post")
{
if (isset($url[1]))
{
$post = $pdo->prepare("SELECT * FROM posts WHERE Link = :link");
$post->execute(array(":link" => $url[1]));
}
else header("location:http://localhost/myblog/");
}
else if ($url[0] == "category")
{
if (isset($url[1]))
{
$post = $pdo->prepare("SELECT * FROM posts WHERE Id = ANY (SELECT Post FROM categories WHERE Category = :cat) ORDER BY Posted DESC LIMIT " . $page*10 . ", 10");
$post->execute(array(":cat" => $url[1]));
}
else header("location:http://localhost/myblog/");
}
else if ($url[0] == "tag")
{
if (isset($url[1]))
{
$post = $pdo->prepare("SELECT * FROM posts WHERE Id = ANY (SELECT Post FROM tags WHERE Tag = :tag) ORDER BY Posted DESC LIMIT " . $page*10 . ", 10");
$post->execute(array(":tag" => $url[1]));
}
else header("location:http://localhost/myblog/");
}
else $post = $pdo->query("SELECT * FROM posts ORDER BY Posted DESC LIMIT " . $page*10 . ", 10");
if ($post->rowCount() > 0)
{
foreach ($post->fetchAll() as $row)
{
if ($url[0] == "post") $pageTitle = $row['Title'];
$pageContent .= "<article>";
$pageContent .= "<header><h2><a href=\"http://localhost/myblog/post/" . $row['Link'] . "\">" . $row['Title'] . "</a></h2><p>" . date('j F Y', strtotime($row['Posted'])) . "</p></header>\n";
$pageContent .= $row['Content'];
$cats = $pdo->prepare("SELECT Category FROM categories WHERE Post = :post");
$cats->execute(array(":post" => $row['Id']));
if ($cats->rowCount() > 0)
{
$pageContent .= "\n<p>Posted in: ";
foreach ($cats->fetchAll() as $cat)
{
$pageContent .= "<a href=\"http://localhost/myblog/category/" . $cat[0] . "\" class=\"category-link\">" . $cat[0] . "</a> ";
}
$pageContent .= "</p>";
}
$tags = $pdo->prepare("SELECT Tag FROM tags WHERE Post = :post");
$tags->execute(array(":post" => $row['Id']));
if ($tags->rowCount() > 0)
{
$pageContent .= "\n<p>Tagged with: ";
foreach ($tags->fetchAll() as $tag)
{
$pageContent .= "<a href=\"http://localhost/myblog/tag/" . $tag[0] . "\" class=\"category-link\">" . $tag[0] . "</a> ";
}
$pageContent .= "</p>";
}
$pageContent .= "</article>";
}
}
- The first thing that happens is checking if the p variable is set – this tells the user what page they are on
- If we are looking at an individual post, prepare a query for that post
- If we are looking at a category, prepare a query for that category
- If we are looking at a tag, prepare a query for that tag
- Loop through all the posts and produce the appropriate HTML for them
In reality it is actually relatively simple, it just takes quite a bit of code to work correctly. Here is what the finished result looks like, although you may want to add extra styling:
In the next tutorial we’ll add commenting and a basic admin panel.
