apache .htaccess rule with dynamic pages (php) performance

Question

i have a new website (i'm building one right now) and i want to make sure i do it correctly and not redesigning after 1 month.

so i have pages like:

/candy
/candy/chocolate
/drink
/drink/beer

so i look on stackoverflow about how can i do this and i found:

RewriteRule ^([a-z]+)/([a-z]+)/?$ index.php?category=$1&page=$2 [NC,L]

now, this will work but my question is about execution. obviously the $_GET[category] will be the name and page will be chocolate for example.

now when i do my query i will do:

$sql = "SELECT myfields FROM mytable WHERE name = '" . $_GET['category'] . "'";

now, would it be better if i use the primary key which is an INT. if so, what can i do in my .htaccess to do this?

Answer 1

Unfortunately using a name and convert it to a unique id might take longer to execute than creating an index on the name itself.

Here's what I recommend based on the urls you have:

Add a index to the name of the page

ALTER TABLE `mytable` ADD key indexname (columnname);

example:

ALTER TABLE `page` ADD key pagename (name);

Now, because the structure is different /candy vs /candy/chocolate, I assume you have some sort of structure like a main page with the list (/candy) and specific list (/candychocolate) so in this case you can use this:

RewriteRule ^([a-z]+)/?$ index.php?category=$1&page=list [NC,L]
RewriteRule ^([a-z]+)/([a-z]+)/?$ index.php?category=$1&page=$2 [NC,L]

Then you can simply query the category and the page using the indexed field. This will fast (of course not as fast as an INT but still fast).

For the first query you can do:

$name = addslahes($name);
SELECT fields FROM category WHERE category = '$name';

and when you get a page you can use:

$categoryname = addslahes($categoryname);
$pagename = addslahes($pagename);
SELECT fields FROM page LEFT JOIN category ON (page.categoryid = category.id) WHERE page = '$pagename' AND category = '$categoryname';

this way, by using both category and page, you will avoid page not found (404).

Answer 2

Just make sure your database has an index for the name row. That will make the lookup just as fast as using a integer primary key.

Use something like CREATE INDEX name ON mytable I think, but doing it via phpmyadmin is a lot easier.

Also protect the script from sql injection by using addslashes

$sql = "SELECT myfields FROM mytable WHERE name = '" . addslashes($_GET['category']) . "'";