[SOLVED] Trying to return all results as well as count of another select statement

[danieliser]

MySQL Version - 5.1.33-community

 

ok im trying to build a dynamic menu setup and the way i want to go about it is to get the following from a single table

 

table is as follows

 

-- ----------------------------

-- Table structure for menu

-- ----------------------------

DROP TABLE IF EXISTS `menu`;

CREATE TABLE `menu` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `label` varchar(50) NOT NULL DEFAULT '',

  `link_url` varchar(100) NOT NULL DEFAULT '#',

  `parent_id` int(11) NOT NULL DEFAULT '0',

  `sort_id` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

 

Ok, so what i want returned is

 

ID | LABEL | LINK_URL | PARENT_ID | SORT_ID | COUNT1

 

 

COUNT1 is where this is tricky.. i have tried using COUNT and GROUPBY but i am either not doing it right or i am needing something else

 

I want COUNT1 to return the number of other row where parent_id = ID of the current row so that if there is a submenu it will return > 0. If not it returns zero. Then i can loop that with php to echo the proper format for my menu.

 

[kickstart]

Hi

 

Something like this:-

 

SELECT a.id, a.label, a.link_url, a.parent_id, a.sort_id, Deriv1.ChildCount
FROM `menu` a
LEFT OUTER JOIN (SELECT parent_id, COUNT(*) AS ChildCount FROM `menu` GROUP BY parent_id) Deriv1
ON a.id = Deriv1.parent_id

 

All the best

 

Keith

[danieliser]

wow.. worked perfect lol.. i would have never come up with that.. thanks a ton.. although after running it several times.. it might be to cumbersome to run on a larger menu.. took several seconds with only 12 results. might have to try and achieve the result using php.. any tips? trying to make this lightweight and fast.. hence the reason i am trying to do it with only one query..

[danieliser]

not sure if it was just a lag in the connection or if it memorized the results.. but now it is loading the results in navicat in .003secs... seems to be working as fast as i was hoping..

[kickstart]

Hi

 

Might be worth putting an inde on parent_id. That might help performance.

 

Seconds for 12 results seems WAY to slow to be correct.

 

All the best

 

Keith

[danieliser]

well avg time now since the first try is around .002 - .003 seconds per query. not sure what you mean by put an inde on parent_id though. i am making a CMS setup that i can template and use for my web design business.. i have been putting it off for a while.. but im tired of having to add new prodcuts for my clients and id hate to have to do that for 100+ products.

 

im not 100% this query will do the work i want it to now.. as im trying to code the php to do a limitless level menu. i dont want to have to have my php setup using loop inside of loop inside of loop.. and even then im almost forced to use a second select statement to retrieve the children. any ideas to do this using only one query? as this will be loaded everytime you refresh the page i want it very lightweight.

 

[kickstart]

Hi

 

I meant an index on parent_id (ie, assume that at the moment id is the primary key, but parent_id isn't indexed)

 

Not sure you can use it as a single call for generating the drop down menus. While you can get all the data out no problem, the trouble would be getting them out in the right order.

 

Have a google for tree traversal for a way of bringing back the full tree in one piece of sql.

 

All the best

 

Keith

[danieliser]

thanks for the help.. i have previously read

http://www.sitepoint.com/article/hierarchical-data-database/2/

 

and i am not sure that will work for me.. or at least i am confused on how i would implement it.. i have an admin area setup already that allows for the adding of pages and products and i will add in the ability to change the sort order column on my table to change the sort of the menu. but the site above would require the menu table to be rebuilt to affect the menu. im just lost as to where i would do that.. i guess on submission of a new page/product i could attach the rebuild to the submission. hmm.. maybe an easier way out i guess.. i would love to make it work without that and be a completely independent dynamic menu..  i have been toying with the idea of asigning all the values to variables in the loop and then possibly allowing the php to loop back through to reorder and output.. not sure if that is feasible.. or if it would be less server load than just multiple select statements..

 

oh.. ps.. i know this isnt in the right forum.. but is it possible in php to have a loop inside a loop but the contents of the inside loop also loop back to the first loop? i know in C i could just use GOTO LINE or something haha.. that would be to simple here..

[kickstart]

Hi

 

That kind of database should work for what you want I think. It isn't that complicated. The biggest problem is that inserting a new record means updating loads of other records, which takes time. But for something only updated occasionally like a menu that shouldn't be a problem.

 

Not keen on gotos (too many years of mainframe programming in PL/1, when people used them for performance reasons and to make dumps easier to read, but then used them so much the code was unreadable). With nested loops I would just have some kind of clause on the inner loop that I could set to false to trigger it to drop out.

 

All the best

 

Keith

[danieliser]

I dont mind that now.. but i am planning to template this for sale to other companies of which i dont know thier requirements.. if i were to get a client that had a mass selection of products.. such as hypathetically walmart.. cough cough.. that model wouldnt work as well with records constantly changing and being added/removed.  I also am thinking possibly a php funtion.. and upon any record having childcount > 0 calling the funtion which would just loop back on itself til there were no results left. but that would require multiple querys.. so i guess im gonna moch up several of these different ways and see which works best for my needs.

[kickstart]

Hi

 

Certainly you can use a recursive function to loop through items. It will be far easier and more efficient for updates, but far slower for reads. Depends how many read per update you have .

 

All th ebest

 

Keith

[danieliser]

lol.. i feel retarded haha.. the first page of that deals with that.. i didnt even realize i was on the second.. but im happy cause i thought of using a function before i read that haha.. i know im getting the hang of php and sql when im laying in bed at night to fall asleep and running through the code it would take to add a function to the site in my head haha.. usually forget by morning though.. blah.. thanks again..