How to create a link to a sub category from a page that is associated with a parent category using MySQL & PHP.

Question

How would I be able to display a url value from a sub category listed in my MySQL database that looks something like the example below?

index.php?cat=category&sub=sub-cat-1&sub2=sub-cat-2&sub3=sub-cat-3

Here is the MySQL table.

CREATE TABLE categories ( 
id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
parentID INT UNSIGNED NOT NULL DEFAULT 0, 
categoryNAME TEXT NOT NULL, 
PRIMARY KEY (id), 
INDEX parent (parentID)
);

UPDATE: My end goal is to create a link to a sub category from a page that is associated with a parent category.

Example: I am on the "Auto Parts" page and I would like to display a link to the "Engine Parts", "Suspension Parts" and "Transmission Parts" pages.

Answer 1

You need to be sure that a name is unique, at least within its parent. My suggestion for that would be one extra field: urlName VARCHAR(100) NOT NULL, and one new index: UNIQUE KEY(parentID, urlName). You have to make sure that the urlName is safe to put in an url, else you'll get some ugly urls.

That said, your url 'll need some work to. I guess something like this will work:

http://example.com/index.php?cat[]=cat&cat[]=subcat1&cat[]=subcat2

That'll work, but it's still kinda nasty, I would never use something like that. You should really consider URL Rewriting, so your url will be something like:

http://example.com/cat/subcat/subcat2/

That's a lot better, isn't it? It's quite easy as well! Just use the following .htaccess:

RewriteEngine on
RewriteCond %{REQUEST_FILENAME} !-f
RewriteRule .* index.php

Then just explode your $_SERVER['REQUEST_URI'] and you're good to go!

Answer 2

There will be many different ways to do what you want to do and that is up to you to determine the most appropriate way to do it.

Essentially you need to create a connection to the database, run a query to select the data, return it to your page/application so you can output it as part of the link

For the basics on PHP/MySQL I would suggest this tutorial

http://www.w3schools.com/php/php_mysql_intro.asp

If what your trying to do is part of an overall application where you want database paging or results using querystring parameters there will be a lot more to it than this but if your unsure about just connecting to the database and selecting data etc I would suggest that your not ready to do that just yet, start with the tutorial and go from there