a) a .htaccess rule which rewrites urls from /products/red-widget to products.php?item=red-widget
b) a script called products.php which searches your database for "... from products where slug='red-widget'"
if you do not go down the route of maintaining a unique slug that script uses the equivalent of:
"from products where title = 'Red Widget'"
which you can do using the likes of: "from products where title = '". str_replace("-", " ", ucwords($item)) ."'";
For the sake of brevity the sql escaping is omitted, make sure you add it.
The other way to achieve somewhat pretty urls is to keep the id number in the url as discussed a few days ago here.