Hey All... This query is waaaay outside my skill set so I can't even fathom how to do this other than the "brut force" method and that's way to slow for my purpose (a mobile app).

I have two tables, `Categories` and `Articles`:

`Categories` has:
`id`, int auto-increment
`name` text
`parent` int id of parent or NULL which means that `name` is THE parent

An example:
id=0; name=Language; parent=NULL
id=1; name='PHP'; parent=0;
id=2; name='Javascript'; parent=0;
id=4; name='Operating System'; parent=NULL
id=5; name='Windows'; parent=4
id=6; name='Linux'; parent=4
Records 0 and 4 are Parents while records 1 & 2 are children of record 0 and records 5 & 6 are children of record 4.

Now, in the next table `Articles` I have:
`id int "auto-increment"
`title` "title of the article"
`category` int "category from the above table, only a child category"
`text` "text of the article"

Like this:
0, "Using error_log in php", 1, "blah blah blah"
1, "Using echo in php", 1, "blah, blah, blah"
2, "popups in javascript", 2, "blah blah blah"
3, "Improper shutdown", 5, "annoying error everytime you start windows"

Category is one of the child categories from the `Categories` table (and can never be a parent category) so 1 referrs to PHP and 2 refers to javascript and 5 refers to Windows.

When I get a query from a mobile device I need to return ALL of the categories AND each parent category will have the total articles in all its child/sub-catagories AND each child category will have the total of all the articles in that category.

So, on a mobile device you'd get a list of parent categories and then a number indicating the total number of articles in all of that parents children, like this:

Language (3) - there are three total articles in the Language category
Operating System (1)

When the user selects the category 'Language', they would see two sub-categories:
PHP (2) - two articles in the php category
Javascript (1) - one article in the Javascript category

Or, if they selected the category 'Operating System' they would they would see one sub-category:
Windows (1)

OK, so how in the heck do I write this query? LOL

Categories are a tree structure. You cannot write a query which returns a recursive structure. Therefore I recommend that you write a user defined function (UDF) which combines all parent categories for a given category in a string which your application then can display.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.