tree menu

[pfkdesign]

hi everybody.

 

i wounder if you can help me to show my flowing code in tree or shows items with its parents in  different level like:

ex.

item 1

--item1.1

-----item1.1.1

item2

--item2.1

--item2.2

..

description:

 

the database

 

id_mnu

idmnu_mnu <- parent id

label_mnu

.

...

 

<?php 
echo "<ul>";
do { 

       echo $row_rsmenu['label_mnu']; 

} while ($row_rsmenu = mysql_fetch_assoc($rsmenu));
echo "</ul>";
?>

 

any idea ?

[zq29]

That is not a very efficient method for storing and displaying hierarchical data, take a read of this article...

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

[pfkdesign]

unfortunately, it is too late to split data and there is no way to do something with that ( this table has already 300 entry) beside it is like menu and if parrent id is NULL , it is the first level and if the parent id is equal to the main id it is the child so  , :-\ now what should i do, any idea ??? 

[zq29]

Well, I'd seriously suggest you write a script that converts the current schema to a more efficient one, 300 entries isn't much.

 

But, if you're hell-bent on leaving it how it is, to be able to loop through all of the 'branches', you first need to find out how deep the deepest 'branch' goes, then loop up to that number, checking if it exists before printing so that non-existent branches don't throw an error.

[pfkdesign]

thank you for your reply.

unfortunately the number of entry is not much but  many table are related to this one and if i change this table, i have to change many things. :-\ so i checked the structure and it has 4 level deep.  :-X , so from here i dont know what to do , ??? :-\

 

[pfkdesign]

any idea?

[burn1337]

start with your parent tables, for each parent check for those menus associated with that parent id, (btw a do while loop if you ask me isn't going to work as well as for loops, or at least from my experiance [which isnt much] it hasn't ) i.e. :

 

$Pid = 1; //parent id

$text = "select * from Parent_tbl_name where Parentid=$Pid"; //do query

$Parent = mysql_fetch_array($query) //or assoc your choice

echo $Parent[0]; [1]; [2]; //display the row or what you want of the row

 

$text2 = "select * from Child_tbl_name where Parentid=$Pid"; //do query

$count = mysql_num_rows($query2)

$count - 1; //or --

//start loop here

$Cid = mysql_result($query2, $count, 0);//or what ever field represents the Child ID

$text3 = "select * from Child_tbl_name where ChildId=$Cid"; //do query

$Child = mysql_fetch_array($query3)

<ul>

echo $Child[0]; [1]; [2]; //display the row or what not

</ul>

decrement count $count -- // or you can use a variable you start at 0 and increment it untill it equals $count -1 (using mysql_result row reference's start at 0 hence the decrement of 1 before starting the loop

 

[aschk]

Basically what you need is not pretty (as demonstrate in the previous post), you need to use PHP to determine if there are any more children from the menu node you are going from. As a result you're performing multiple calls using PHP to MySQL, not exactly efficient.

 

I'm guessing that other tables link to this table based on the primary key? If you rejig the layout of this table you can still maintain that primary key... thus resolving any issues with foreign key data.

[burn1337]

it may not be efficient, but if mysql, and/or php is configured correctly, or if the opperations are used offten then it will stay resident in either virtual memory, or resident memory, making the functions run with more ease. but that does also depend, on the structure of the talbe layout as well. for instance, seeing as you can see I'm referenceing mostly the id's (aka most likely primary keys and/or foreing) unless you have an old old computer that shouldn't have any actual effect on speed. For instance I took that sample snippet from the basic design of my forums, I have a intel celeron D, (x86 architechure)  although I have yet to work with 300 entries with a loop of that structure, The layout s/he exampled should be just fine, just becareful of memory overflowing, it might even be a good idea to seperate that either into seperate functions, or set that entire section off into a seperate file, and call the functions from a different script. That should save some on the overall load.

[aschk]

The main problem is that you have an exponential growth in queries... a parent has 10 children, each child has 10 children, you're looking at 100 queries there alone, and they're NOT cached because each query is sufficiently different (by the WHERE clause). And you're only looking at 101 rows there! I would say that's pretty inefficient to be honest. Especially when it could be achieved with a single depth query.

 

e.g.

SELECT m1.label_mnu, m2.label_mnu, m3.label_mnu
FROM menus m1
LEFT JOIN menus m2 ON m2.idmnu_mnu= m1.id_mnu
LEFT JOIN menus m3 ON m3.idmnu_mnu = m2.id_mnu
WHERE m1.idmnu_mnu = <parent id here>

 

However, of course, this requires you know the depth of the deepest node.

[burn1337]

true, and the whole not caching. Its called indexing for a reason, plus the code will get cached I never said the query would be cached, hence the hole call the function from another script lol.

[aschk]

So you're saying that the PHP should be cached? You're assuming here that the OP has caching available to him (which isn't the case in most paid-for hosting facilities).

 

As far as I can see there are 2 types of caching available these days, bytecode, and html page (browser output).

 

The 2nd method won't show the latest menu items if they've changed (until the cache time expires). It's also worth mentioning that the CODE itself is NOT cached, it's the output that is cached (i.e. what the browser sees).

 

The 1st is available via modules such as APC, which do bytecode compiling and storage, however this will ONLY reduce the compiling time of the php script (which is minimal anyway), whereas the real issue lies with issuing multiple SQL requests to your expensive resource (the database server). Bytecode compiling does NOT negate the expensive streaming calls to the database... therefore your issue still exists.

 

As a side note: PHP is NOT held in memory, it's compiled and run at execution time (when requested).

[burn1337]

ok first go back and re-read what I said, I said IF MYSQL AND/OR PHP IS CONFIGURED RIGHT. Not meaning it is always going to be. Secondly if your going to first say that the query is not cached and then say the output (which would contain the query and loop) is what is cached well then your just contradicting yourself, and your just saying the exact oppisite of what you originally said except for the part of new menu parts. And if the php code and/or files are not held in memory then please tell me why it is that all my main scripts that are sitting in que waiting to be used on this computer (MY SERVER) are sitting in either resident memory or (most cases, aka que) virtual memory? Look dude why don't you go grab these 2 books, "The Definitive Guide to MySQL5" by Michael Kofler, and PHP 5 Power Programming by Andi Gutmans. I think maybe you would actually learn something. Cause both of those books mention(although I can't remember word for word) about the automatic caching systems in both Mysql and Php. Honestly I would much rather believe one of the creators of Php rather some guy who is saying two different things that are the exact oppisite of eachother. Besides dude if you really knew anything about programming then you would know everything (including syntax, placement, variables and structure) has something to do with the effect of the program and that include the way everything is entered into the memory and how it is extracted back from the memory.  Although you are somewhat right, the file/program sits in memory sometimes pre-loaded(meaning if it has high volume then it will be pre-loaded) untill the file/code is called upon from there it executes. And pardon me for not speaking in complete lamens terms I am very tired atm. I haven't slept but 8 hours in the last week or so cause I've been coding my arse off on my website.  Another thing will you please also tell me why it is that my college classes/ Professors (again for the life of me I can't remember it word for word) even mentioned the fact that php files/code sit in memory?

 

Plus Mysql as well as I am pretty sure nearly every type of Database system has this thing called INDEXING. Which helps to reduce the time of querys. Not to mention the fact that you can set those indexs yourself, and some automaticly set those indexes.

[sasa]

try

<?php
function echo_menu($menu_array, $labels, $ident = 0, $start = 0){
$ide = '--';
foreach ($menu_array[$start] as $label ){
	echo str_repeat('--', $ident), $labels[$label], "<br />\n";
	if (isset($menu_array[$label])) echo_menu($menu_array, $labels, $ident + 1, $label);
}
}
mysql_connect('localhost', 'root');
mysql_select_db('test');
$sql = 'SELECT * FROM menus';
$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)){
$labels[$row['id_mnu']] = $row['label_mnu'];
$cat = $row['idmnu_mnu'] ? $row['idmnu_mnu'] : 0; 
$menu[$cat][] = $row['id_mnu'];
}
echo_menu($menu, $labels);
?>

[aschk]

Thanks Sasa for your reply.

 

Not wishing to continue the trivial arguments that Burn wishes to have, can we take this off-thread please. I'm more than willing to discuss further, the issue.