Mod_ReWrite / ReWriteMap a URL using a database lookup script

Question

The Scenario
I have completely rewritten an old existing ASP classic ecommerce website over to PHP.

The database design of the previous site had alot of relational ID problems causing troublesome linking of product data rows to other tables in the database.

To get around this I also redesigned the database, giving products new Primary Keys, whilst still keeping a column in the table with the products old PK.

The Problem
The problem I have is when the site is relaunched, I need all search engine links that used to point to 'Product.asp?ProductID=29' to lookup the the database, match the products old PK, and redirect to the products new PK i.e. 'Products.php?ID=53'.

I'm looking to do this with Mod_ReWrite/ReWriteMap, however, all documentation I can find online doesn't state how to deal with the database script PK lookup.

Update
I've read up further on Ignacio Vazquez-Abrams' suggestion at http://httpd.apache.org/docs/2.0/mod/mod_rewrite.html#rewritemap > External Rewriting Program, and it seems to be correct and the ideal solution.
However, after contacting my host, they do not enable ReWriteMap on their servers.

I've supplied my own, but by no means the best/correct solution of achieving this without ReWriteMap enabled.

Answer 1

Your query:

SELECT
  ID
FROM
  thetable
WHERE
  ProductID=?

From there just echo the value of ID and a newline, and flush stdout.

Rewrite rules:

RewriteMap dbfixup prg:dbfixup.script
RewriteCond %{QUERY_STRING} ProductID=(\d+)
RewriteRule Product.asp Products.php?ID=${dbfixup:%1}

Answer 2

You should have a different get variable name for the new ID. That way in your new program you will know whether to match the product using the old ID or the new one.

$old = isset($_GET['id']) && is_numeric($_GET['id']) && $_GET['id'] > 0 ? $_GET['id'] : 0;
$new = isset($_GET['new']) && is_numeric($_GET['new']) && $_GET['new'] > 0 ? $_GET['new'] : 0;
if ($old + $new > 0) {
    $query = 'SELECT * FROM `ProductDetails` WHERE '.($old > 0 ? '`OrigPrdID`='.$old : '`PrdDetID`='.$new).' LIMIT 1;';
    ...

Answer 3

After finding out RewriteMap is disabled by my host, I've reached my own solution simply using mod_rewrite and 2x 301 redirects.

.htaccess file

RewriteEngine on
RewriteRule ^Product(.*)\.asp SEO_Redirect.php [NC,R=301]

SEO_Redirect.php file

$ID = $_GET['ID'];

$query_rsNewID = "SELECT NewProductID FROM Products WHERE OldProductID = '$ID'";
$rsNewID = mysql_query($query_rsNewID, $Database);
$row_rsNewID = mysql_fetch_assoc($rsNewID);

header ('HTTP/1.1 301 Moved Permanently');
header ('Location: Product.php?ID='.$row_rsNewID['NewProductID']);

Note, this is a simplified excerpt of each file, and would not be secure against SQL injection.

Hopefully Google and alike will accept getting 2x 301 redirects without problems.