Categories and Searching

[phence]

If i am developing a site similar to ebay. How should i develop and impliment categories and searching by the category?

i'm not sure i understand how categories work. I have an idea but im not sure how correct it is?

What kind of database structure is used? I don't understand how sub categories work either...

Should I assign the listing a category id? and then when a person clicks on the category links it searches the listing database for all listings with the category id.

If anyone understands what i mean, Please help.
I am using php/mysql

You help is greatly appreciated!

[website]

Yeah you are on the right track, if you look at it like a sitemap it is really not so hard to understand. Lets imagine this as a sitemap for some website

Frontpage
Links
-Carlinks
-Videolinks
--Horror
--Comedy

Then the database would be something like this

Table: sitemap
ID |  Name      |  Parent

1  | Frontpage  | NULL
2  | Links      | NULL
3  | Carlinks   | 2
4  | Videolinks | 2
5  | Horror     | 4
6  | Comedy     | 4

And then you could write a function that executes itself, something like this:

function sitemap ($id, $depth) {
	if ($id == 'NULL')
		$query = "SELECT * FROM sitemap WHERE Parent IS NULL";
	else
		$query = "SELECT * FROM sitemap WHERE Parent = '" . $id . "'";

$result = mysql_query($query);
if (mysql_num_rows($result) < 1)
	return;
while($row = mysql_fetch_array($result)) {
	for ($i = 0; $i < $depth; $i++) {
		echo("-");
	}
	echo($row['Name'] . "<br>");
	sitemap ($row['ID'], $depth++);
}
}

Then you simply start calling this function by doing
sitemap ("NULL", 0);
and it would print out the list like this
Frontpage
Links
-Carlinks
-Videolinks
--Horror
--Comedy

Note that this is has not been tested and may have some bugs in it!

Hope that helps!

[cgraz]

The way I do it is set up two tables.

main_categories
sub_categories

main_categories would contain the following fields:

id
category

sub_categories would contain:

id (primary key)
mcat_id (would associate each record w/a record in main_categories table. This would be the id field in the main_categories table)
sub_category (the name of the sub category)

Hope this helps.

Cgraz

[website]

well, yes you can do it like cgraz suggested but my way offers endless subcategories

[phence]

Thanks alot guys for your input!!!

yeah i am definatly going to have Multipul Sub directories.
as well as multipul sub directories of sub directories. You know what i mean.

somthing like this:
Categories >
Categories > Construction
Categories > Construction > Equipment
Categories > Construction > Equipment > Concrete
Categories > Construction > Equipment > Concrete > Trailer Pumps
Categories > Construction > Equipment > Concrete > Mixers
Categories > Construction > Equipment > Concrete > Boom Pumps
Categories > Construction > Equipment > Concrete > Other
Categories > Construction > Equipment > Carpentry
Categories > Construction > Equipment > Steel

website,
I'm not sure i understand how your depth system works completly...
But i do get the basic idea.
Can you elaborate a little more on that?

if it's what im thinking, if the parent was 3. then the sub category would fall under car links??

With a Massive amount of categories, wouldn't that get a little confusing?

how would this work in a system like i have displayed above ?

[xblue]

There are two common approaches to store tree structures with variable depth in a database, nested set model and storing the parent id with each child's record as website outlined (there may be simpler ways for simpler tasks, e.g. if the depth will always be 3 or something, possibly more redundant or less efficient, but it always depends on your needs).

Storing the parent id requires recursive DB queries for some task e.g. these breadcrumb things (I think that's what they are called?).

The nested set model allows retrieving data more efficiently, but is harder to maintain.

There's not a lot of material about the nested set model, I found one for you if you want to read on about this:
http://threebit.net/tutorials/nestedset/tutorial1.html
(this is about implementing it with java and postgre, but the basics of database organization are general)

[website]

yeah well as xblue explained then the Parent is always the ID of the parent category, so to say we have
Categories > Construction > Equipment > Concrete > Boom Pumps
The database would be like this

Table: categories
ID |  Name        |  Parent

1  | Construction | NULL
2  | Equipment    | 1
3  | Concrete	  | 2
4  | Boom         | 3

And then construction would be with parent NULL which means no parent because it is on top of the tree, equipment would have construction as parent, concrete equipment as parent and so on. You see, very simple when it clicks 🙂

With your tree you would need a little different function, not much but still... Then you would have to trace the parent after parent of each categorie which would be a little stressful for the database. If you want a function that does that I can write it for you...

[website]

hmm, forgot to answear some questions

if it's what im thinking, if the parent was 3. then the sub category would fall under car links??

Correct 😉

With a Massive amount of categories, wouldn't that get a little confusing?

Well, it is only a database, with the right way to retrive the info it won't get very confusing but I mean, what database is not confusing 😉

[phence]

okay, i think i got it
but could you show me an example using 2 or more categories.
LIKE:
Categories > Construction > Equipment > Concrete > Boom Pumps
and
Categories > Cars > Coupe > Ford

Now would the Cars Category Parent start back at NULL and
Coupe = 1, Ford = 2 ?? as well as any new categories, would the main category start at null and work down through the numbers?
Or would they continue accumulating?
Thanks for the help guys

[website]

well, yes, but they may not need to be in the same order.
Parent in my case refers to ID in the same table, here is an example of the following structure:
Categories > Cars > Coupe > Ford

Table: categories
ID |  Name    |  Parent
---------------------------
1  | Cars     | NULL  //Null says that this is the top level, you could also use just 0, 
2  | Coupe    | 1     //although NULL and 0 are not the same things
3  | Ford     | 2

//Note that the categories table could just as well be like below, 
//it still is the same order

Table: categories
ID   |  Name |  Parent
---------------------------
21   | Cars  | NULL
195  | Ford  | 195
301  | Coupe | 21

So top level is NULL, next levels always refer to the next level above with the Parent column

Hope that helps!

[phence]

okay i understand that! thanks a lot!! I really do appreciate all your help!

so for these two category structures:
Categories > Construction > Equipment > Concrete > Boom Pumps
Categories > Cars > Coupe > Ford

the database would look like this...

Table: categories
ID  |  Name |  Parent
---------------------------
1  | Cars         | NULL
2  | Ford         | 3
3  | Coupe        | 1
4  | Constrution  | NULL
5  | Equipment    | 4
6  | Concrete     | 5
7  | Boom         | 6
-----------------------------

Is that right? thats where i''m confused.. not sure how to handle more than one category...

So the Parent will allways refer to the Id of the category or sub category it belongs infornt of?

Sorry if i'm not understanding this right.. just one of those days..

[website]

Yeah, that is correct.

[phence]

website,
I am not getting any output from this code...?
is there somthing i am missing?

<?php

// this function creates a list of all categories and sub categories!
function categories ($id, $depth) {
    if ($id == 'null')
        $query = "SELECT * FROM categories WHERE Parent IS NULL";
    else
        $query = "SELECT * FROM categories WHERE Parent = '" . $id . "'";

$result = mysql_query($query);
if (mysql_num_rows($result) < 1)
    return;
while($row = mysql_fetch_array($result)) {
    for ($i = 0; $i < $depth; $i++) {
        echo("-");
    }
    echo($row['Name'] . "<br>");
    sitemap ($row['ID'], $depth++);
}
}

//connect to DB
if(!($connection = @ mysql_pconnect($hostName, $username, $password)))
		die("Could Not Connect to database");
if (!mysql_select_db($databaseName, $connection))
		showerror();

// print it
$id = 'null';
$parent = 0;
echo categories($id, $parent);

?>

here is my database table: categories

| id | Name | parent |
----------------------- 
 1   | Antiques |  0  

 2   | Art |  0   

 3   | Crafts |  2   

 4   | Drawings |  2   

 5   | Construction |  0  

 6   | Services |  5  

 7   | Equiptment |  5  

 8   | Advertising |  6  

 9   | Concrete |  7  

thank-you

[website]

You might want to change parent to Parent and id to ID

If that doesn't work, I'll look into it as soon as I can.

[phence]

yep... still not working..
Thanks alot for your time and help

[website]

Got it to work. I had accidently tryed to execute sitemap function which was the old name of the categories function. Also you shouldn't had done
echo categories...
The function itself echos all text.

I did it working both ways, check it out.

First, the db layout(here is one example):

+----+-------------------+--------+
| ID | Name              | Parent |
+----+-------------------+--------+
|  1 | Computer Industry |   NULL |
|  2 | Cars              |   NULL |
|  3 | Games             |      1 |
|  4 | Ford              |      2 |
|  5 | Counter-Strike    |      3 |
|  6 | Computers         |      1 |
+----+-------------------+--------+

Idea number 1. (the original one)

function categories ($id, $depth) {
    if ($id == 'NULL')
        $query = "SELECT * FROM categories WHERE Parent IS NULL";
    else
        $query = "SELECT * FROM categories WHERE Parent = '" . $id . "'";

$result = mysql_query($query);
if (mysql_num_rows($result) < 1)
    return;
while($row = mysql_fetch_array($result)) {
    for ($i = 0; $i < $depth; $i++) {
        echo(">> ");
    }
    echo($row['Name'] . "<br>");
    categories ($row['ID'], ($depth + 1));
}
}

//the following starts it all
categories("NULL", 0);

This would output the following

Computer Industry

Games

Counter-Strike
Computers
Cars
Ford

Now to the second idea, your idea...

function categories ($id, $depth) {
    if ($id == 'NULL')
        $query = "SELECT * FROM categories WHERE Parent IS NULL";
    else
        $query = "SELECT * FROM categories WHERE Parent = '" . $id . "'";

$result = mysql_query($query);
if (mysql_num_rows($result) < 1)
    return;
while($row = mysql_fetch_array($result)) {
	categories2 ($row, $depth);		
}
}
//note that I had to split this into 2 functions 
//because $depth will not restart on each loop
function categories2 ($row, $depth) {
	echo($depth . $row['Name'] . "<br>");
	$depth = $depth . $row['Name'] . " >> ";
	categories ($row['ID'], $depth);
}

//the following starts it all
categories("NULL", "");

This would output

Computer Industry
Computer Industry >> Games
Computer Industry >> Games >> Counter-Strike
Computer Industry >> Computers
Cars
Cars >> Ford

Well, try this..!

[phence]

That did it! THANK-YOU SOOOO MUCH! i can't thank you enough for not giving up on me and putting up with my questions!!
I really do appreciate it!
Thank You,
Sean

[phence]

I have a question, if i just wanted to get a list of the Main Categories, meaning not sub categories...
How could i do that?

Thanks for the help

[website]

Way number one, much simpler here...

function categories ($id, $depth, $maxdepth) {
    if ($id == 'NULL')
        $query = "SELECT * FROM categories WHERE Parent IS NULL";
    else
        $query = "SELECT * FROM categories WHERE Parent = '" . $id . "'";

$result = mysql_query($query);
if (mysql_num_rows($result) < 1)
    return;
while($row = mysql_fetch_array($result)) {
    if ($depth >= $maxdepth)
      break;
    for ($i = 0; $i < $depth; $i++) {
        echo(">> ");
    }
    echo($row['Name'] . "<br>");
    categories ($row['ID'], ($depth + 1));
}
}

//the following starts it all
categories("NULL", 0, $maxdepth);
$maxdepth = HowManyLevelsYouWantToGoDown
Note that 0 will not display any categorie, 1 will only display the top and so on...

function categories ($id, $depth, $nrdepth, $maxdepth) {
    if ($id == 'NULL')
        $query = "SELECT * FROM categories WHERE Parent IS NULL";
    else
        $query = "SELECT * FROM categories WHERE Parent = '" . $id . "'";

$result = mysql_query($query);
if (mysql_num_rows($result) < 1)
    return;
while($row = mysql_fetch_array($result)) {
    if ($nrdepth > $maxdepth)
      break;
    categories2 ($row, $depth, $nrdepth, $maxdepth);        
}
}

function categories2 ($row, $depth, $nrdepth, $maxdepth) {
    echo($depth . $row['Name'] . "<br>");
    $depth = $depth . $row['Name'] . " >> ";
    categories ($row['ID'], $depth, ($nrdepth+1), $maxdepth);
}

//the following starts it all
categories("NULL", "", 0, $maxdepth);
$maxdepth is the same as above
Here I added numberic depth too (even though other ways may be possible).

Try this, I haven't tested it so there may well be some errors in it.

That did it! THANK-YOU SOOOO MUCH! i can't thank you enough for not giving up on me and putting up with my questions!!
I really do appreciate it!
Thank You,
Sean

No problem 😉

[phence]

😃 they works great! thanks agian!!! 😃