Page 1 of 1

Friendy URL using value from database

Posted: Sat Oct 01, 2011 4:09 am
by stoyleg
I'm looking for a way to convert a URL to something friendly using values stored in a MySQL database.

I have a control panel which allows users to enter their own friendly URL, so for example if the user entered "my-friendly-url", the URL would change from...

http://www.domain.com/page.php?pid=4&id=37

to...

http://www.domain.com/my-friendly-url

I know you can convert URLs using .htaccess, but I have no idea how to use database values in place of the querystring. As far as I know it's not possible for .htaccess to connection to a database (?)

Drupal, Joomla etc have this function, but I don't know how they do it.

Thanks for any help.

Re: Friendy URL using value from database

Posted: Sat Oct 01, 2011 9:31 pm
by twinedev
EDIT: Added in the header lines for 404 error, (header("HTTP/1.0 404 Not Found");). In looking over the code, realized I forgot them. Without them, your site would never give a response to a invalid page, ie search engines would think it is a properly called page and index it

Basically you need to set ti to rewrite all requests that do not call an actual file/folder, to call a php script and pass it what you are trying to get

Here is something I normally use. The first condition forces the site to use www. version of the domain, the second block does the above:

[text]RewriteEngine on

RewriteCond %{HTTP_HOST} !^www\.domain\.com [NC]
RewriteRule ^(.*)$ http://www.domain.com/$1 [R=301,L]

RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteCond %{REQUEST_URI} !=/favicon.ico
RewriteRule ^(.*) /index.php?path=$1 [L][/text]

So now, if you go to http://domain.com/gregs-page (assuming there is not a directory called "gregs-page") the following would actually be called:
http://www.domain.com/index.php?path=gregs-page

So now that you have this, index.php would take $_GET['path'] and look that up in a database, where you have the "SEO friendly" version mapped to the value you need (this is assuming that every call will be similar to the example you gave, each having a pid and id value (in this example, 404error is the page to display for a invalid URLs, index is the "home page")
[text]pathname (pk) | pid | id
--------------+-----+----
index | 1 | 1
gregs-page | 3 | 23
sample-stuff | 23 | 14
404error | 2 | 72[/text]

So you could have something like :

Code: Select all

<?php 

// Sets up database connection
require_once('connect.php');

// Makes sure it was called correctly. Nothing passed we call 'index' something invalid, we call ''404error'
if (!isset($_GET['path'])) { $_GET['path'] = 'index'; }
elseif (!preg_match('/[.a-z0-9_-]+/i',$_GET['path'])) { $_GET['path'] = '404error'; }

$rsPage = mysql_query('SELECT `pid`,`id` FROM `tblPaths` WHERE `path` = "'.$_GET['path'].'"');
if (!rsPage || mysql_num_rows($rsPage) < 1) {
	// The path given doesn't exist, force 404 error
	header("HTTP/1.0 404 Not Found");
	$rsPage = mysql_query('SELECT `pid`,`id` FROM `tblPaths` WHERE `path` = "404error"');
	if (!rsPage || mysql_num_rows($rsPage) < 1) {
		// At this point, we tried getting 404error page, but something still went wrong!
		die ("The page you were looking for is not available");
	}
}
$_GET = array_merge($_GET,mysql_fetch_assoc($rsPage));

mysql_free_result($rsPage);
unset($rsPage);

require_once ('page.php');

// EOF: index.php
If you are going to be using things other than page.php, and varying QS parameters, the you could instead do:
[text]pathname (pk) | module | qs
--------------+----------+---------------------------
index | page | pid=1&id=1
gregs-page | events | pid=3&id=23
sample-stuff | products | pid=23&producttype=4
404error | page | pid=2id=72[/text]

Then you can use the following:

Code: Select all

<?php 

// Sets up database connection
require_once('connect.php');

// List of actual PHP files that are to be called by this system. 
$aryModules = array('page','events','products');

// Makes sure it was called correctly. Nothing passed we call 'index' something invalid, we call ''404error'
if (!isset($_GET['path'])) { $_GET['path'] = 'index'; }
elseif (!preg_match('/[.a-z0-9_-]+/i',$_GET['path'])) { $_GET['path'] = '404error'; }

$rsPage = mysql_query('SELECT `module`,`qs` FROM `tblPaths` WHERE `path` = "'.$_GET['path'].'"');
if (!rsPage || mysql_num_rows($rsPage) < 1) {
	// The path given doesn't exist, force 404 error
	header("HTTP/1.0 404 Not Found");
	$rsPage = mysql_query('SELECT `module`,`qs` FROM `tblPaths` WHERE `path` = "404error"');
	if (!rsPage || mysql_num_rows($rsPage) < 1) {
		// At this point, we tried getting 404error page, but something still went wrong!
		die ("The page you were looking for is not available");
	}
}
$aryLoad = mysql_fetch_assoc($rsPage);
mysql_free_result($rsPage);
unset($rsPage);

// This prevents any possibility of an bad data trying to call something not inteded.
if (!in_array($aryLoad['module'],$aryModules)) {
	die ('Invalid call to site');
}
parse_str($aryLoad['qs'], $_GET);

require_once ($aryLoad['module'].'.php');

// EOF: index.php

Re: Friendy URL using value from database

Posted: Sun Oct 02, 2011 8:37 pm
by benny4343
From your code it seems that the request for the friendly URL is directed to a index.php file that takes
a GET path variable a that tells the server which page to show.

"So now, if you go to http://domain.com/gregs-page
(assuming there is not a directory called "gregs-page")
the following would actually be called:
http://www.domain.com/index.php?path=gregs-page"

What does it mean will the url in the browser show the friendly version or redirected
to: http://www.domain.com/index.php?path=gregs-page ??

when I tried to use rules I can easily link to:

http://www.mysite.com/category/product

but when user types this url it redirects to the ugly url I have on my site.

I must be missing something,

maybe you can help?

Thanks

Re: Friendy URL using value from database

Posted: Mon Oct 03, 2011 3:20 pm
by twinedev
Sounds like you are using rules that issue a 301 redirect instead of just rewriting it for apache to use.

If you look at the first section of the .htaccess I use (to redirect non www. traffic to use www.), you will see at the end of the line, it is doing it as a 301 redirect.

The last line in the .htaccess file that does a redirect to the index.php?path=whatever is not a browser redirect, it is just a rewrite rule to tell apache what URL to process (the end user doesn't see the change)

-Greg

Re: Friendy URL using value from database

Posted: Mon Oct 03, 2011 3:31 pm
by twinedev
For those who may have tried this code before I wrote this post, please note that I had left out the line

Code: Select all

header("HTTP/1.0 404 Not Found");
in each sample. It is in the code listed here now.

Without that line, when you go to a page that doesn't exist, the browser (and search engines) would see a response code of 200 OK, indicating it was a proper page. People would see a 404 error page telling them it didn't exist (or whatever you put on it), however search engines would just see that URL as a valid page on your site and index the content.

-Greg