rewrite url with htaccess and MySql

[doortodoororganics]

assuming apache and a mysql db with a products table with two fields:

product_id
product_url

inwhich product_id=53 = yellowwidgets,

how can one query the db using product_id to re-write the url with the value from product_url? example:

query string:
[site.com...]

re-written url:
[site.com...]

is this a problem for RewriteRule?

[jd01]

Not really, the way you described. If you would like to use variable in the script that connects to the DB you will need to pass it somewhere.

In the example you posted, you would need to select by product rather than id. If you changed your URL to http://site.com/51/yellowwidgets or http://site.com/yellowwidgets/51/ you could select by either id or product.

Justin

[doortodoororganics]

the db does not contain product.
only product_id and product_url

you would need to select by product rather than id.

could you clarify the point, thanks.

[jd01]

Sure...

The problem is you need to somehow associate the URL with what you will be selecting by, because you cannot pass 'invisible' information (unless you POST, but that defeats the purpose).

Here is an example:

Original URL:
http://site.com/page.php?product_id=53

On your php page you have something like this:
$id=$_GET['product_id'];

"SELECT * FROM table WHERE id=".$id." ";

If you change the URL to this http://site.com/yellowwidgets you have two options:
1. Your php will need to be something like this:

$uri=explode("/",$_SERVER['REQUEST_URI']);
$product=end($uri);

AND your .htaccess like this:

RewriteRule ^([^.]+)$ /page.php [L]

Now you have the product, but no way to select, because you do not have the id to use and you do not have the product listed and indexed in the DB.

2. Your .htaccess will need to be something like this:

RewriteRule ^([^.]+)$ /page.php?product_id=$1 [L]

This stores and passes any request that does not contain a .(dot) as the product_id to the php script, but you have the same problem of no id number, so you cannot select.

The only real way to do it without changing your DB structure is to pass the id in the URL, like this:

http://site.com/51/yellowwidgets

or similar

Then you can use mod_rewrite to pass the id you need to select:

RewriteRule ^([^/]+)/[^.]+$ /page.php?product_id=$1 [L]

Hope this helps.

Justin