3 Jun
SEO Friendly URLs With PHP, MySQL and Apache – Part 2
This is the second part of a two part series on implementing Search Engine Optimization (SEO) friendly URLs in an Apache/PHP/MySQL environment. In the first part of this article we discussed why we would implement SEO friendly URLs and in this part we discuss how we would implement SEO friendly URLs. This article provides the technical details necessary to implement SEO friendly URLs in an Apache web server, PHP programming language, and MySQL database environment and gives the details on how each of these parts link together to provide the appropriate information when the SEO URL is requested by the user.
In this part, we create a hypothetical MySQL database that stores news articles. This database stores the title, article, and publish-date of the article which is used to provide that information to be displayed on the web page. This is a simple example of getting this data out of the database using SEO URLs and does not include any style or pretty formatting. Instead, this article will focus on the mechanics of displaying an article when requested using an SEO URL. The article’s URL will be preceded by the path /article/ and will contain the article title appended to this path.
Articles Database
The first thing we will do is create our database. As stated before, there will be a column for the title, article, publish date, as well as an auto-increment column for an id that is unique for each article. Here is the code to build our database which can be added to your database by executing the SQL from within phpMyAdmin or a similar tool:
CREATE TABLE `news` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`article` text COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Now that the database is created, we can add some articles using SQL statements in phpMyAdmin. In reality, you will want to create some sort of administration page to add, remove, and change the articles:
INSERT INTO `news` (`title`,`article`,`publish_date`) VALUES
('This is an article','I wrote a wonderful article', NOW()),
('This is a Great SEO Article','This article shows how to do SEO in your article', NOW()-INTERVAL 1 DAY);
Since the id column is an AUTO_INCREMENT, the articles will automatically be given a unique id in the id column when the articles are inserted. This id will be the key to displaying the correct article the user has clicked on.
Showing the Articles
Here is the code that will display either the list of articles or a single article based on whether the PHP variable $article_id is set and is not set to false. We are not going to focus much on this code, since it is all based on the site that this will be integrated into, so here it is:
<!DOCTYPE html>
<html>
<head></head>
<body>
<?php
$link = mysql_connect(HOST_NAME, USER_NAME, PASSWORD) or die();
mysql_select_db(DB_NAME) or die()
if(isset($article_id) && $article_id != false){
$sql = "SELECT id,title,article FROM news WHERE id=".$article_id;
$result = mysql_query($sql) or die();
if($row=mysql_fetch_assoc($result)){
echo($row['article']);
}
} else {
$sql = "SELECT id,title FROM news ORDER BY publish_date DESC";
$result = mysql_query($sql) or die();
while($row=mysql_fetch_assoc($result)){
print_seo_link($row['id'],$row['title']);
}
}
?php>
</body>
</html>
This simply checks to see if there is an article id. If there is an article id, it prints out the contents of the article. If there is no article id set or the article id is not an integer, then it prints the list of articles. Most of the code is self-explanatory except for the function print_seo_link, which we will explain below.
For the purpose of this article, the code above will be put into a file called view_article.php.
Printing the article links
The function print_seo_link takes two parameters, the unique id of the article and the article total. From this information, it prints out the SEO link to the specific article. The code for the function is:
function print_seo_link($id,$title){
$seo_patterns = array('/[^a-zA-Z0-9 -]/', '/[ -]+/', '/^-|-$/','/quot/');
$seo_replace = array('', '-', '', '');
$seo_name = "/articles/".preg_replace($seo_patterns,$seo_replace,$title)."-".$id;
print(“<p><a href=’$seo_name’>$title</a></p>”);
}
This function sets up an array of preg_replace patterns and an array of items to replace the patterns. The first array item finds anything that is not letters, numbers a space or dash characters and replaces it with nothing. The second item finds any number of spaces or dashes and replaces them with dashes, the third looks for dashes at the beginning or end of the string and replaces them with nothing, and the last item finds the string quot and replaces it with nothing.
Next we perform the preg_replace on the title of the article. This will take the title ‘This is a article’ and return ‘This-is-a-article’. In that same line of code, we also prepend ‘/article/’ to the title and append a ‘-‘ and the id of the article at the end, so the title will look like ‘/article/This-is-an-article-1’ and then simply print that as the href in the a tag.
Getting an article id
Now that we have printed a list of all the articles, we need a way to read the link and determine what the article id is. this function takes a string and return an id if there is one, and false if there is not one:
function get_seo_id($seo_title){
$id=false;
$parts = explode("-",$seo_title);
if(count($parts) > 1){
$seo_id=$parts[count($parts)-1];
if((string)$seo_id == (string)(int)$seo_id){
$id=$seo_id;
}
}
return $id;
}
This function sets up a variable to be false in the event of not finding an id. It then takes the name and splits it apart on the ‘-‘ character. If there is more than one part, or a ‘-‘ was found, then it gets the last part (which should be the id). If that id is a number, then it returns the number, otherwise the default value of false is returned.
Note that this function does not even look at what the title of the article is. It simply looks for the number at the end and returns it if there is one. If there are extraneous non numeric characters after the number, it returns false. If there is no number at the end it returns false.
Apache .htaccess Changes
Now, an astute programmer will notice that we have created links that do not equate to the file name view_article.php that we put the code into at the beginning. What we are going to do is use the power of the Apache web server to rewrite requests and send them to this file without the user even knowing. We want to take the request for /article/This-is-an-article-1 into a request to the file /view_article.php?name=This-is-an-article-1.
The magic to make this happen is to add a line to the .htacess file in the root of the web server that rewrites the first to the second. This is actually accomplished by 3 lines, however if you are already using mod-rewrite to rewrite requests, the first two will already be included in the file. The lines are:
RewriteEngine on
RewriteBase /
RewriteRule ^article/(.*)$ display_articles.php?name=$1 [L]
The first two lines setup the file to use the mod-rewrite engine within the Apache server. The first line ‘RewriteEngine on’ turns the engine on, and the second line ‘RewriteBase /’ sets the base directory of rewrites to be the root directory.
The third line does all of the rewriting of the URL. If a URL is requested that starts with ‘article/’ after the domain, take everything after it and set it equal to the name parameter to the file ‘view_article.php’ file. In this way, everything after the ‘article/’ will be available as a $_GET parameter in the view_article.php file. Once this is added to your .htaccess file in the root of the web server, requests will be rewritten and sent to the file.
So putting all the code into the view_article.php file we get:
<?php
function get_seo_id($seo_title){
$id=false;
$parts = explode("-",$seo_title);
if(count($parts) > 1){
$seo_id=$parts[count($parts)-1];
if((string)$seo_id == (string)(int)$seo_id){
$id=$seo_id;
}
}
return $id;
}
function print_seo_link($id,$title){
$seo_patterns = array('/[^a-zA-Z0-9 -]/', '/[ -]+/', '/^-|-$/','/quot/');
$seo_replace = array('', '-', '', '');
$seo_name = "/articles/".preg_replace($seo_patterns,$seo_replace,$title)."-".$id;
print(“<p><a href=’$seo_name’>$title</a></p>”);
}
$article_id=get_seo_id(@$_GET[‘name’]);
?>
<!DOCTYPE html>
<html>
<head></head>
<body>
<?php
$link = mysql_connect(HOST_NAME, USER_NAME, PASSWORD) or die();
mysql_select_db(DB_NAME) or die()
if(isset($article_id) && $article_id != false) {
$sql = "SELECT id,title,article FROM news WHERE id=".$article_id;
$result = mysql_query($sql) or die();
if($row=mysql_fetch_assoc($result)){
echo($row['article']);
}
} else {
$sql = "SELECT id, title FROM news ORDER BY publish_date DESC";
$result = mysql_query($sql) or die();
while($row=mysql_fetch_assoc($result)){
print_seo_link($row['id'],$row['title']);
}
}
?php>
</body>
</html>
And there you have it, a simple and effective way to turn any kind of title or headline into an effective and efficient SEO Friendly URL.
Respond to this post