Rewriting a URL without using the ID with PHP & MySQL

The following post shows you how to rewrite a url without the user providing an id in your address bar.

Requirements
I recommended that you are familiar with Apache mod-rewrite, PHP and MySQL before reading onwards!

The Problem
When building a website sometimes we may want to do it without showing the id of a table row. For example, consider the url for this page:

http://beta.neilyoungcv.com/blog.php?blogId=14

Becomes:

http://beta.neilyoungcv.com/blog/rewriting-a-url-without-using-the-id-with-php-and-mysql/

Despite a lot of what I have read online this is achievable using MySQL.

How To Do It
In my .htaccess file I add the following:
#BLOG PAGE
RewriteRule ^blog/([^/.]+)/?$ blog-item.php?$1 [L,QSA]
This tells apache to take any characters and replace the value of $1.
In PHP I have a function that takes a string and builds a rewritable url:

public function RewriteString($string)
{

	if(DEBUG == 1) { echo 'SiteFunctions->RewriteString(' . $string . ')' . "nn"; }

	//trim the string
	$string = trim($string);

	//look for an & used as the name which is surrounded by spaces left and right
	// e.g. PHP & MySQL - we then replace '&' with 'and'
	$string = preg_replace("/s&s/", " and ", $string);

	//look for html entities, if found clear them
	$string = preg_replace("/&[#0-9A-Za-z]+;?s?/", "", $string);

	//check for invalid characters (except space)
	$invalid = array(" ", ";", "/", "?", "%", "*", ":", "|", '"', "<", ">", ".", "'",".", ",", "!", "");

	//replace invalid characters (except -)
	$replace = array("-", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "","");

	//lower case the string and strip out invalid characters
	$string = strtolower(str_replace($invalid, $replace, $string));

	//return the string
	return $string;

}

The following function is the most important, this runs a MySQL query that dynamically strips out invalid characters and matches the title field to a url that is generated by the query. This is a bit messy as MySQL does not really support preg_replace functionality.

public function Lookup($url)
{
	// debug function call
	if(DEBUG == 1) { echo 'BlogItems->Lookup(' . $url . ')' . "nn"; }

	// create sql query to strip invalid characters from field and
	// compare to passed url e.g. my-blog-article
	//use the RewriteString function to get this
	$sql = 'SELECT *, ';
	$sql .= 'CONCAT(';
	$sql .= '   LOWER(';
	$sql .= '       REPLACE(';
	$sql .= '       REPLACE(';
	$sql .= '       REPLACE(';
	$sql .= '       REPLACE(';
	$sql .= '       REPLACE(';
	$sql .= '       REPLACE(';
	$sql .= '       REPLACE(';
	$sql .= '       REPLACE(';
	$sql .= '       REPLACE(';
	$sql .= '       REPLACE(';
	$sql .= '       REPLACE(';
	$sql .= '       REPLACE(';
	$sql .= '       REPLACE(';
	$sql .= '       REPLACE(';
	$sql .= '       REPLACE(';
	$sql .= '       REPLACE(';
	//compare to the title field
	$sql .= '       title, ';
	$sql .= '       "’", ""), ';
	$sql .= '       "!", ""), ';
	$sql .= '       ",", ""), ';
	$sql .= '       '"', ""), ';
	$sql .= '       "'", ""), ';
	$sql .= '       "&", "and"), ';
	$sql .= '       ".", ""), ';
	$sql .= '       ">", ""), ';
	$sql .= '       "<" , ""), ';
	$sql .= '       "|", ""), ';
	$sql .= '       ":", ""), ';
	$sql .= '       "*", ""), ';
	$sql .= '       " ", "-"), ';
	$sql .= '       "/", ""), ';
	$sql .= '       "?", ""), ';
	$sql .= '       "%", "") ';
	$sql .= ')) AS url ';
	$sql .= "FROM blog_items ";
	$sql .= "HAVING url = '" . $url . "' ";
	$sql .= "LIMIT 1";

	// print out sql query for debug purposes
	if(DEBUG == 1) { echo $sql . "nn"; }

	//execute query
	$result = mysql_query($sql);

	//check if a result is returned
	if (!$result)
	{
		//debug error
		if(DEBUG == 1) { echo mysql_error() . "nn"; }
	}

	//print out result - e.g. should return field with the title My Blog Article
	while($row = mysql_fetch_assoc($result))
	{
		//print row information (recursive)
		print_r($row);
	}

	//free result
	mysql_free_result($result);
}

Although this is fairly database intensive (this has to perform multiple replacing of characters in a string) – it is a good way to dynamically select a field row based on a url.

Another way to do this would be to store the rewritten url in a field and do a lookup on that field at the point of extraction. This would mean that you would update the url when an article is added or edited.

The latter, from a database performance point of view would be faster (no string manipulation needed) but I thought I would mention the former for developers wishing to extract their database rows dynamically.