Categorising Data Into A MySQL Database Using PHP

[justlukeyou]

Hi,

I have a database of products but I am finding it diffult to understand how to categorise each product and build a site for it.

Can I do something like this:

$category = (

$gamesconsoles = ( playstation, xbox, wii )
$personalhygiene = ( electric toothbrush, electric razor, electric shaver)

)

So I have PHP code which identifies a 'Playstation' as a $gamesconsoles. It then enters a 'gamesconsoles' tag into the database in a category field.

I then I have link on my site which looks something like this on the homepage:

productrange.php?category="gamesconsoles" class="menulink">Games Consoles</a>

I then link to a productrange.php page which has in it:

$category = category;
if (isset($_GET['category'])) {
$id = $_GET['category'];
}

I then echo all the producys from the database which come under the category of 'gamesconsoles'.

Or am I completely mad and it should be done differently. If so please advise.

[guido2004]

If you haven't already, you should start by designing your database, the PHP code will come later.
You say you have a database of products. What tables do you have in your database?

[justlukeyou]

Many thanks,

I have description, price, full description, image link, link.

I can enter a category such as Playstation but I dont know how to put this into a category of 'Games Consoles'.

All I currently have is a sub-category.

Im looking to do this on multiple basis. For example sub-category of 'xbox controller' into 'Games Controllers'.

[guido2004]

I have description, price, full description, image link, link.

These are not tables, they are columns of one table.

So you have a products table.

You'll need a categories table.

And you'll have to decide:
1) A product can be part of only one category -> add a categoryid column to the products table
2) A product can be part of more categories -> you'll need a productcategory table (which contains two columns: productid and categoryid) that will link products and categories together

I can enter a category such as Playstation but I dont know how to put this into a category of 'Games Consoles'.

All I currently have is a sub-category.

Im looking to do this on multiple basis. For example sub-category of 'xbox controller' into 'Games Controllers'.

Take a look at this explanation written by r937: http://sqllessons.com/categories.html

[justlukeyou]

Many thanks for this,

This complicates things. I thought I could this all within the same table.

So I would have one column for category and one for subcategory. The column for the category would have an entry based on what was in the subcategory.

This sounds much easier lol

So I take it I need to link the two tables together somehow?

[guido2004]

You've lost me here.

Please post an example of what you would store in your tables.

[justlukeyou]

Table:


Category: Sub-Category: Price: Description:
Games Console Playstation 3 £399.99 Playstation 3 Games Console with 3 Games
Games Controller XBox Controller £29.99 Wireless Xbox Contoller
Furniture TV Stand £249.99 Television Stand for 32" Flat Panel TV

So I would enter the category into a 'Category' column based on what is in the Sub-Category

For example when the code reads 'TV Stand' it would enter Furniture into the Category column.

[guido2004]

All in one table? Did you read my post and the article I linked to?

[justlukeyou]

Hi,

I was just trying to explaing how I thought it was done but I must be wrong. Working with two tables sounds complicated.

One category sounds fine. So that can be done it one table. Im getting a bit confused here.

Is it not possible to add a category to one table and allocate a category. That so much easier than using two tables.