hierarchical data help

[delboy2405]

MYSQL 5.0.18

 

hi,

 

mysql newbie here, please excuse my ignorrance.

 

i've practically only read few chapter in a php book regarding mysql so very much a beginner.

 

I have database with 6 tables:- systems, ratings, comment, category, linkcount, bet_type, i have attached a diagram of the database

 

what i'm trying to get to workout is what would be the best way to do the category table. 

 

AS a parent-child type or nested set type.  within the systems table is a foreign key that links to category table.

The category table current goes to a depth of around 2 and is unlikely to go in deeper, however the chances are more will get added

 

What am i try to do:  I'm looking to display each of the category items with a count of how many systems have this category in a full tree mode.

 

many thanks

 

data can be supplied if needed

 

[attachment deleted by admin]

[fenway]

MySQL has an excellent dev article on this (the list adjacency model) -- have you explored this option?

[delboy2405]

hi, i've had a look at the article which i have found very helpful.

 

how easy would it be to add a count to the below code:

 

Retrieving a Full Tree

 

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';

 

+-------------+----------------------+--------------+-------+

| lev1        | lev2                | lev3        | lev4  |

+-------------+----------------------+--------------+-------+

| ELECTRONICS | TELEVISIONS          | TUBE        | NULL  |

| ELECTRONICS | TELEVISIONS          | LCD          | NULL  |

| ELECTRONICS | TELEVISIONS          | PLASMA      | NULL  |

| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |

| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS  | NULL  |

| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |

+-------------+----------------------+--------------+-------+

 

[fenway]

What do you want to count?

[delboy2405]

apologies fenway i'm looking to count the amount of systems in the systems table that are in each category of the category table.

 

i'm currently using the query below.  The problem with this query is that it doesn't count the parent items i.e. this is a category called SPORT that is the parent of soccer and tennis,

 

SELECT category.name, COUNT( systems.name ) AS nrOfItems
FROM systems, category
WHERE systems.category = category.ID
GROUP BY category.name
ORDER BY category.name

 

Result:

 

name  nrOfItems 

Blackjack 3

Greyound 2

Horse Racing 11

Poker 2

Roulette 4

Soccer 4

Tennis 1

 

EXPLAIN:

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra 

1 SIMPLE systems ALL NULL NULL NULL NULL 27 Using temporary; Using filesort

1 SIMPLE category eq_ref PRIMARY PRIMARY 4 bettingsystemsr.systems.category 1

 

CATEGORY TABLE

 

CREATE TABLE `category` (

  `id` int(11) NOT NULL auto_increment,

  `name` varchar(50) NOT NULL,

  `parent` int(11) NOT NULL,

  `ts` datetime NOT NULL,

  PRIMARY KEY  (`id`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

 

SYSTEMS TABLE

 

CREATE TABLE `systems` (

  `id` int( unsigned NOT NULL auto_increment,

  `name` varchar(255) NOT NULL,

  `bet_type` int(11) NOT NULL,

  `category` int(11) NOT NULL,

  `description` text NOT NULL,

  `cost` float NOT NULL,

  `guarentee` tinytext NOT NULL,

  `delivery` varchar(255) NOT NULL,

  `tech_support` varchar(25) NOT NULL,

  `reg_date` datetime NOT NULL,

  `views` int(11) default NULL,

  `image` blob,

  PRIMARY KEY  (`id`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=28 ;

 

 

SQL VERSION -- Server version: 5.0.51

 

 

 

 

 

[fenway]

I'm confused... how do you mean?

[delboy2405]

ok,

 

i'll try to explain bit better. Below is the category table, this is linked to the system table view category.id and systems.category.

 

I'm looking to count how many systems are in each category and the total of the parent categories ie soccer and tennis are in the sport category. so if we had 5 in soccer and 6 in tennis we would want to have sport count with 11.

 

hope i've explained better

 

id  name  parent  ts 

1 All 0 2008-08-09 10:14:08

2 Horse Racing 1 2008-08-09 10:14:08

3 Greyound 1 2008-08-09 10:14:56

4 Sports 1 2008-08-09 10:14:56

5 Soccer 4 2008-08-09 10:15:44

6 American Sports 4 2008-08-09 10:15:44

7 Tennis 1 2008-08-09 10:16:13

8 Casino 1 2008-08-09 10:16:13

9 Blackjack 8 2008-08-09 10:16:54

10 Poker 8 2008-08-09 10:16:54

11 Baccarat 8 2008-08-09 10:17:33

12 Roulette 8 2008-08-09 10:17:33

13 Tipsters 1 2008-08-09 10:17:47

 

[fenway]

Maybe now I see what you mean... you want to count down multiple levels of hierarchy?

[delboy2405]

yes exactly wahy i meant. 

[fenway]

Then you'll have to JOIN as many levels down (or up) as you need... there's no recursive solution in this case.

[delboy2405]

Then you'll have to JOIN as many levels down (or up) as you need... there's no recursive solution in this case.

 

not to sure what you mean fenway can you show me and example.  Currently I have a depth of around 2

[fenway]

How do you know where to start (up or down)?

[delboy2405]

How do you know where to start (up or down)?

 

apologies fenway not to sure what you mean?

[fenway]

What is the input to your quey?