Page 1 of 1

Links In Categories....

Posted: Sun Aug 17, 2003 3:03 am
by Mr. Tech
Hi again!

I have a links directory script and I am trying to determine the amount of links in a category and its sub categories and their sub categories and so on.

In the mysql database I record the location of the script and the path of the script by recording all the categories before it and their IDs. E.g:

Location(The categories it is in): Home/Category/Sub Category
Path(IDs): 1/2/3

Does that make sense?

And to get the amount of links in the categories I use this:

SELECT * FROM links WHERE path rlike '$path'

But the problem with this is that that path gets confused with other numbers. For example 1 will get mixed up with the number 10 because they are similar...

Please let me know if this dows not make sense. I'll try explain it better.

Thanks

Posted: Sun Aug 17, 2003 6:56 am
by qads
have you tried

Code: Select all

SELECT * FROM links WHERE path = '$path'
? :)

Re: Links In Categories....

Posted: Sun Aug 17, 2003 8:44 am
by delorian
Mr. Tech wrote:In the mysql database I record the location of the script and the path of the script by recording all the categories before it and their IDs. E.g:

Location(The categories it is in): Home/Category/Sub Category
Path(IDs): 1/2/3

Does that make sense?
Couldn't you just put the father category into your database, instead of that category path. The Home category will have sub categories and the father category equal to zero. The sub categories will have the father category equal to HOME category ID and so on. So when you want to count all the links in the category you need only:

Code: Select all

SELECT count(category_id) AS number_of_links_in_category FROM categories WHERE category_father=some_category_id
or something like this. I know there will be problem in other matters doing it in that way. But I think it's much better from the database point of view. :D[/i]

Posted: Sun Aug 17, 2003 6:29 pm
by Mr. Tech
Thanks for your replys!

qads, that wonr work if the link is in a sub category..

delorian, the thing is if I only put the "father" category id how will the subcategories track their amount of links...

Maybe I can explain it better. If I have a category that has the location like this:

Home/Sub Cat

I want it to count the amount of links that have the location of for example:

Home/Sub Cat
Home/Sub Cat/More Sub Cats
Home/Sub Cat/Category

Not ones like these with the same sort of text:

Home/Main Cat/Sub Cat
Home/Cat/Sub/Sub Cat

Understand what I'm saying? It's sort of like >= but that only seems to work with numbers.

Any ideas?

Thanks

Posted: Mon Aug 18, 2003 2:41 am
by delorian
I'm sorry, but I don't get it. I will explain my point of view.

You have go a categories table:

Code: Select all

CAT_ID   CATEGORY_NAME    CAT_FATHER
1             HOME                     0
2             SUB1                      1
3             SUB2                      1
4             HOME2                    0
5             SUBSUB1                2
6             SUB3                      4
and so on...

so the cat link would look like:

Code: Select all

HOME
   SUB1
      SUBSUB1
   SUB2
HOME2
   SUB3
In every category you have got links. Even if the link has two or more categories you can always add another coulumn in the links table.

So, as you can see if you want to count all the links in category e.g. HOME you will have to count all the links which has the CAT_ID equal to HOME, SUB1, SUBSUB1, SUB2. But how the script has to know which CAT_ID should it count. And there we have my CAT_FATHER stuff. First you should make a list of categories which ID should be taken into account in your link counting, and then you just go through that ID's and count the links, of course, omitting the duplicates.