Nathan Nathan - 5 months ago 9
SQL Question

how to convert pre-existing categories and products to SEO friendly URL's

I have recently taken on a project to convert an old shopping cart site (custom developed) to a modern SEO friendly site

here is my problem the current site utilizes URL's like the below

http://www.domain.com/page.php?id=XX


i would like to convert there pre existing database of pages (products and categories) to a URL that would look similar to the below

http://www.domain.com/confectionery-&-snacks/lollies/cola-zombie-chews


if the user was to delete cola-zombie-chews the url would look like the below and load the page lollies and so forth if lollies was removed it would load the page confectionery & snacks

http://www.domain.com/confectionery-&-snacks/lollies/


there current database setup looks similar to the below

ID | TYPE | NAME
--------+-----------+------------
1 | category | confectionery & snacks
2 | category | lollies
3 | product | cola zombie chews


another issue is the URL could be 4 categories deep for example see below

http://www.domain.com/frozen-foods/seasonal/christmas/meat/smoked-salmon-1kg


what type of rewrite rule will i need for this and what type of code would i need to check the url exists in the database

i have thought about adding another colum in the database for slug and copying the name and removing the whitespace and replacing with -

Answer

I think @Vrac did a good answer +1 :). And @AntoineB has a point too. So I wanted to add a slightly different way and more detailed on the url creation part.

You want to use search engine friendly URLs, well you need 2 things:

  1. Replace all the urls page.php?id=XX everywhere in the site by the ones you want. So at this point a click on the new URL will return a 404.

  2. All the new urls should make sense to your php - that is the job of .htaccess + php router.


for step #1,

On each product page I guess you have access to which product is in which category. If the database schema is such as what you gave the architecture is not sufficient to decide which category is in which. So you need a 4th column parent that will store the id of a parent category of each category or product. And whether it is a category or object, they all have a category as parent or null for the root.

In php in the page that generates URL for category or product you need to fetch from database all the categories associated to the current item and climb up the tree from the last nested node (the current item) to the root.

So you will have a loop as the simplified code bellow.

Tip:

  • If small amount of items, fetch all at once from db to save database queries and then loop on your results (in an array) - call it case a;
  • Otherwise start from querying only the current item from its id and get its parent, then in the loop do another query to get the new parent again - call it case b;

So you get the parent of the parent and so on until parent is null.

<?php

$itemTree = [];
$allDBItems = mysql_query('SELECT * FROM items');

while($parent)
{
    // getParent() is a function that loops on $allDBItems - in case a - and break loop on found $currItemInLoop['id'] === $myItem['parent']
    // or - in case b - query the db like mysql_query("SELECT * FROM items WHERE id = $myItem[parent]");
    $parent = getParent();

    if ($parent) $itemTree[] = $parent;
}

$itemUrl = implode('/', array_reverse($itemTree));

yay, you have the url generated!


for step #2

in .htaccess redirect all the urls to page.php with no exception as follow (I hope you can have a better name than page.php or at least index.php that makes sense as root script).

RewriteRule ^(.*)$ index.php?path=$1 [NC,L,QSA] 

Almost like @Vrac wrote.

Then in index.php do as @Vrac wrote:

$path = $_GET('path');
$patharray = explode('/',$path);
//do processing based on contents of patharray 
$category = $patharray[0];
$product = $patharray[1];
//etcetera, etcetera...
//serve up content based on the path that was requested

You have it complete! :)