[SOLVED] Here is a tricky one... db theory

[forumnz]

Well, lets say that I have 100 auction categories each assigned a number (100,101,102 etc).

 

I have a main category called 100,

a sub category called 101,

and a third level category called 102.

 

So user would have to click 3 times to get to 102 (like Art>Supplies>Brushes).

 

Lets say the user was on .com/browse.php?cat=101 (or Supplies) and I wanted it to show all the next categories ie 102,103,104 etc. How would I have that in a db?

Also, in cat 101 it would display below some auctions (eg closing) from 102,103,104. HOw should I code that in a db?

 

More info. If user click on a third level cat e.g 102, then the url would be .com/browse.php?cat=102.

 

Get what I mean? So in cat 100 we would be able to see other sub cats + other sub cats auctions. Then in next sub cat we would see those sub cat auctions + third level sub cats. Then in the third level we would only see those auctions and not and mroe cats.

 

I know this may be hard (or easy) but please help!

 

Thanks you very much.

Sam.

[Jessica]

In each category you need to store it's parent category, then select out all of the categories with parentID of the current category.

[jitesh]

Category Table

 

Category_ID [Primary Key] Category_Name Parent_Category_id [Foreign Key Which is PK for Same Table]

100 c1 0

101 c2 100

102 c3 101

103 c4 102

[dbo]

Typically you have an associative table that links child table to parent table and then you use JOIN to get the data out.

[forumnz]

Wow! That is confusing. Can someone please explain it further or link me a tutorial?

 

Thanks for all your help.

Sam.

[darkfreaks]

http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php

[jitesh]

http://www.phpfreaks.com/forums/index.php/topic,158111.msg689031.html#msg689031

[darkfreaks]

i know it says LEFT JOIN but what you need is JOIN

[forumnz]

Thanks for that. I kind of understand it now. I have used the link from jitesh.

 

The output is this:

-me
- -myselft
- - -again!!
- - - -test1
- - - -test2
- - - - -test3
- - - - - -test4




me
me -myselft
me -myselft -again!!
me -myselft -again!! -test1
me -myselft -again!! -test2
me -myselft -again!! -test2 -test3
me -myselft -again!! -test2 -test3 -test4

 

I can understand how that is working, but lets say the user was in the first cat of 3, how would I display say the 20 auctions from all the subcats that were closing the soonest?

 

Thanks,

Sam.

[forumnz]

?Anyone?

[jitesh]

Not getting . Please expalin discriptivly.

 

I can understand how that is working, but lets say the user was in the first cat of 3, how would I display say the 20 auctions from all the subcats that were closing the soonest?

[forumnz]

Righto,

 

Well, as an example, lets say there is an auction site with many categories and user clicks on Tools then Power Tools then Drills.

 

The user is now in the drills section and we can display all the auctions from drills.

What I want is, lets say the user clicks on Tools and nothing else. The should see the sub categories (i.e Power Tools, Hand Tools) and under them them should see a selection of auctions from all the categories and sub cats within the sub cats.

 

You get it?

 

Tools - Power Tools - Drills

                                - Jigsaw

        - Hand Tools

 

See? So if user click on Tools they will see sub cats Power Tools and Hand Tools. PLus they will see auctions from drills and jigsaw there.

 

Understand?

 

Sam.

 

[jitesh]

Yes getting you.

 

This is so simple

 

Now see below function in nested-categories.php file.

 

function get_cats(&$cats_result , $parent_id = 0 , $level = 1)
    {
            $this->cats = array();
        $this->tmp_cats = array();
            $this->get_cats_($cats_result,$parent_id, $level);
    }

When first time the page will be load then the parent_id will be 0 in this function.

 

Now when user click on any category then pass category id for selected category to parent_id in above function. It will give you tree from that category.

[forumnz]

Cool - I think I get it.

 

So what do I do from here?

What is my next step?

[jitesh]

(1) Simply load parent level categories on first load.

(2) On Click of category explained above.

[forumnz]

Can that be based on the URL - like .com/browse.php?parent=$105&show=subcats ?

 

Sorry I am still learning. I would have to define the show subcats function I think, to show the next cats from the parent one?

[jitesh]

1) When page load first time the url will be like this

   

    www.site.com/category.php

    // Code to load parent categories

 

 

2) Now each time when click the url will be like this

 

    www.site.com/category.php?category_id=100

    // Pass category id as parent id in function. will give tree from given category id.

 

   

   

 

 

[forumnz]

Right, by parent do you mean the first category or the next one clicked?

 

So basically if the first category is loaded, all the parent ones will load aswell?

 

[jitesh]

(1) Parent Level (First Time) : Load all categories which has "parent" 0 in table.

(2) Now for next all time whichever category is clicked will be parent for open all opened sub categories.

    while the categories level 0 ("parent" 0 in table) will be same at their posision.

[forumnz]

Ok got it. Now, how would I code that? That's it right? After I get the php script working and the db structure setup right, it should work?

 

How can that be done?

 

Thanks,

Sam.

[jitesh]

First time write a query.

 

Select * from categories where parent = 0;

 

Display all parent level categories

 

Second

 

Continue with Select * from categories where parent = 0;

 

now for selected category write a query.

 

select * from categories where parent = " selected category id ",

 

and access that function with passed the " selected category id " as a parent id.

 

Diplay your contents.

[forumnz]

Wow that is confusing. I can see what you are saying but I wouldn't know where to begin with the coding. Do you know of any good tutorials where I could see some code?

 

Thanks,

Sam.

[jitesh]

You only need to understand current given code and need changes according to requirement.

 

It will satisfy your requirements.

[forumnz]

Ok then thanks for that. I will give that a go then. Can you point me in the right direction? Is there somewhere in the code speciafically I should be looking?

[jitesh]

Use Search functionality at

 

http://www.phpclasses.org

 

May be it helpfull.

 

No any other idea at now.