SEO URL based on category name without have a cate

Question

I have two table posts & categories

post_id | post_title  | post_content   | post_cat
--------------------------------------------------
1         Hello World  welcome to my..   1
.         ..           ..                ..

categories table

cat_id | cat_name | cat_parent
-----------------------------
1        News       NULL
2        Sports     1
.        ...        .. 

Let's say current category link for news is http://domain.com/category/1/

MySQL statment

SELECT posts.post_id,
       posts.post_id,
       posts.post_title,
       posts.post_content,
       posts.post_cat,
       categories.cat_id,
       categories.cat_name,
       categories.cat_parent
FROM   posts
       INNER JOIN categories
         ON posts.post_cat = categories.cat_id  
       WHERE posts.post_cat = (int)$_GET['cat_id']

So we can get a result for post_cat = 1

According to my current database structure, how do I remove the ID but change it to be a nice slug? Example :-

Main category - http://domain.com/category/news/
Sub category  - http://domain.com/category/news/sports/

Let me know a clue how script will tell News is equal 1 on post_cat column?

Answer 1

It is not as different as generate an url with id. Use the same logic to generate the url with title. However they way I see it, there is only one thing, you need to remember.

The category name has to be unique at all cost.

Place a .htaccess to ensure the titles are properly redirected you the page

RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ /category.php?title=$1 [L]

Then, instead of using id in the query use the title

SELECT posts.post_id,
       posts.post_id,
       posts.post_title,
       posts.post_content,
       posts.post_cat,
       categories.cat_id,
       categories.cat_name,
       categories.cat_parent
FROM   posts
       INNER JOIN categories
         ON posts.post_cat = categories.cat_id  
Where 
      categories.cat_name = 'News'

---

As for the subcategory also, the above query should work, since all the categories are placed in same table.

Answer 2

You can use an .htaccess file to rewrite the URL's for you.

The entry in the .htaccess file would look something like this :

RewriteEngine on
RewriteCond $1 ^category 
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ parseCategoryUrl.php?q=$1 [L,QSA]

I'll break down the lines here to help understand whats going on and what each line means :

1. Turn the RewriteEngine on.
2. This rewrite will only occur on URL's where the first word after the host name is category - all URL's starting with that word will be processed by the lines below.
3. The rule will exclude URL's that point to actual files !-f
4. The rule will exclude URL's that point to actual directories !-d
5. The actual rule will capture the entire request_uri and pass it to the parseCategoryUrl.php file with the entire request as a parameter called q (query) in the $_GET array. The flags at the end of the rule (L,QSA) do two things.
   • L - This is the last rule. After processing this line the .htaccess file will stop performing actions on the current URL.
   • QSA - Append the query string to the rewritten URL (so that we can parse it later).

Your parseCategoryUrl.php file could contain something similar to the following :

$request = explode('/',$_GET['q'] );
array_shift($request);

The first line will split the request by slashes into an array - and the second line removes the word category from the beginning of the array (because we know that we are parsing a category URL).

The final $request array, with a URL example such as : http://example.domain.com/category/news/sports
Will be this :

Array
(
    [0] => news
    [1] => sports
)

So you see here that you have now successfully split the URL into an array; All you have to do now is query your database and and provide the user with the correct page.
Because the parseCategoryUrl.php page in fact has no output, you could just use an include() function to insert the correct page according to the URL that was provided.

---

SEO is about making your pages and their URL's more informative to search engines so that a user searching the internet will be able to receive page results that are related to their search query. A search engine looking at the URL : http://domain.com/category/1/2 will not be able to extract much information. However if your URL contains (as your question requires), category information, then a search engine will be able to deduct that the specific URL is related to :

• http://domain.com/category/news/ - news
• http://domain.com/category/news/sports - sports news
• http://domain.com/category/blog/ - blog
• etc...