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.
Friendy URL using value from database
Moderator: General Moderators
Re: Friendy URL using value from database
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 :
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:
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[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
Last edited by twinedev on Mon Oct 03, 2011 3:28 pm, edited 1 time in total.
Re: Friendy URL using value from database
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
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
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
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
Last edited by twinedev on Mon Oct 03, 2011 3:32 pm, edited 1 time in total.
Re: Friendy URL using value from database
For those who may have tried this code before I wrote this post, please note that I had left out the line
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
Code: Select all
header("HTTP/1.0 404 Not Found");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