Rewriting url parameters using rewritemap and mysql

Question

I realize this question has been beaten to death by a lot of people asking it, but most of the answers appear to be for a very specific case that only suits their needs, and so far I have not found a suitable solution (at least, as far as i can see).

My problem is that I am trying to make my website redirect URL parameters from an ID to an actual string.

For example:

www.example.com/?category=1

would display as:

www.example.com/software

while

www.example.com/?category=2

would be displayed as:

www.example.com/software/desktop

From what I've read up, I'm supposed to be looking into an apache rewritemap, and this is where my confusion comes in. I'd really rather not have to load from a flat txt file, as I'd like to make this as dynamic as possible, and I have read that I can make it read from a php file and read it from a MySQL database, which is what I'd like.

The problem with that is that I'm really not too sure what the proper way is of achieving this. The RewriteMap document only somewhat covers flat .txt files, and not achieving it with MySQL.

So basically what I'm asking is if someone can explain how to achieve what I'm looking for, or at least point me in the right direction. Most of the threads I've found so far have sadly not been too helpful as of yet, but it's possible I might have passed by useful ones.

If it helps, right now, my MySQL data is formatted in an inherited structure like so:

 ID |   Title          |   Link             | Parent
  1 | Software         | /Software/         |  NULL
  2 | Desktop Software | /Software/Desktop/ |   1
  2 | Mobile Software  | /Software/Mobile/  |   2

PS: I should add that most solutions I've found give this as the example:

  RewriteMap examplemap prg:/path/to/file.php
  RewriteRule (.*) ${examplemap:$1}

Yet it never gives information as to what is in that file.php, and how it queries and returns the value.

EDIT I should mention that I am on a shared hosting server, not my own private one, and so I may not have access to all possible options

EDIT 2 Just for the sake of clarity: What I'm trying to do is make it so that a user who accesses 'example.com/software' would be treated as though they are on 'example.com/?category=1'; basically prettying the link and making it more readable. The only thing is, I'm trying to read it from a database

Answer 1

If you don't have access to the server or vhost config, you can't use RewriteMap anyways. The map itself needs to be defined in either the server or vhost config, not in an htaccess file. But apache 2.4 has an option of using mod_dbd to use an SQL query to define a rewrite map.

If you need to access MySQL, you're probably better off doing all of this in PHP instead of using mod_rewrite. You'd use mod_rewrite to route to your php file, which would then redirect. Maybe something like this?

RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ /file.php?link=$1 [L]

So when someone requests http://example.com/Software/Mobile/, the request gets rewritten to: /file.php?link=Software/Mobile/, and your file.php script would do the lookup.

Or if you actually mean the other way around:

RewriteCond %{QUERY_STRING} category=([0-9]+)
RewriteRule ^$ /file.php?ID=%1 [L]

So when someone requests http://example.com/?category=2, the request gets rewritten to: /file.php?ID=2 and the php script does the lookup.

Answer 2

My suggestion would be to look at utilizing a front controller pattern. I think that once you start getting into user friendly URL's or the concept of "routes", that the front controller can really simply things since you no longer have to worry about mapping specific URL's to specific controllers at the web server level.

If you have Apache mod_dir enabled (chances are you do), you could do something like this in your Apache config or .htaccess:

FallbackResource /index.php

This simple directive will direct any requests that would otherwise cause a 404 error to be directed to a front controller at /index.php.

This can also be done via mod_rewrite like this:

RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ /index.php [L,QSA]

In the front controller, you could evaluate the URI and route the request to whatever logic need to handle the request. You could do this via lookup of routes from a database or a hard-coded array of routes or whatever. (I would suggest however that, if using a database, you have a cached version of the routes available for quick access).

There are a number of different PHP route controllers available such that you don't need to reinvent the wheel (most every modern framework has some sort of routing concept).