dropdown menu generator...

[stevesweetland]

have a looksee, please look at this code, and tell me how i can speed it up, cause it runs like a right dog at the moment.. and this is a problem.

<script language="JavaScript">
<!--
function mmLoadMenus() {
<?
function magicfunction($string){
  $newString = "";
  for($char=0;$char<strlen($string);$char++){
    $newString .= ord(substr($string,$char,1))-96;
  }
  return $newString;
};

$db = db();
$db2 = db();
$db3 = db();

$i = 0;

// colors
$normaltext = "#077676";
$highlighttext = "#077676";
$bordercolor = "#000000";
$normalbgcolor = "#D9FDFF";
$highlightbgcolor = "#32C9D6";
// end color declaration

$sql = "SELECT * FROM Categories WHERE CatID < '9' OR CatID > '11' ORDER BY CatID ASC";
$db->query($sql);
while ($db->next_record()){
  $t_id = $db->field("CatID");
  $t_name = $db->field("CatDesc");
  $t_catmenuid = magicfunction(strtolower(str_replace(" ","",$t_name)))."_0";
  if ($i == 0){
     print "
  if (window.mm_menu_".$t_catmenuid.") return;";
  };

  unset ($scatarray);
  $scatarray = array();

  $sql = "SELECT * FROM SubCategories WHERE CatID = '$t_id' AND SCatDesc <> 'Home' ORDER BY SCatDesc DESC";
  $db2->query($sql);
  $si = 1;
  while ($db2->next_record()){
    $t_scatid = $db2->field("SCatID");
    $t_scatname = $db2->field("SCatDesc");

$sql = "SELECT * FROM Articles WHERE SCatID = '$t_scatid'";
$db3->query($sql);
if ($db3->recordcount() > 1){
  $t_scatmenuid = $t_catmenuid."_$si";
  print "
window.mm_menu_".$t_scatmenuid." = new Menu(\"$t_scatname\",180,18,\"Verdana, Arial, Helvetica, sans-serif\",11,\"$normaltext\",\"$highlighttext\",\"$normalbgcolor\",\"$highlightbgcolor\",\"left\",\"middle\",4,0,500,0,0,true,true,true,0,false,true);";
  while ($db3->next_record()){
    $t_artscatid = $db3->field("ArtID");
    $t_artsname = $db3->field("ArtTitle");
    print "
  mm_menu_".$t_scatmenuid.".addMenuItem(\"$t_artsname\",\"location='home.php?artid=$t_artscatid'\");";
  };
  $scatarray[] = "menuitem#split#mm_menu_".$t_catmenuid.".addMenuItem(mm_menu_".$t_scatmenuid.");";
  print "
  mm_menu_".$t_scatmenuid.".hideOnMouseOut=true;
  mm_menu_".$t_scatmenuid.".menuBorder=0;
  mm_menu_".$t_scatmenuid.".menuLiteBgColor='$highlightbgcolor';
  mm_menu_".$t_scatmenuid.".menuBorderBgColor='$bordercolor';
  mm_menu_".$t_scatmenuid.".bgColor='$normalbgcolor';\n";
} else {
  $scatarray[] = "linkitem#split#mm_menu_".$t_catmenuid.".addMenuItem(\"".$t_scatname."\",\"location='home.php?scatid=$t_scatid'\")";
};
$si++;
  };

  print "
    window.mm_menu_".$t_catmenuid." = new Menu(\"root\",180,18,\"Verdana, Arial, Helvetica, sans-serif\",11,\"$normaltext\",\"$highlighttext\",\"$normalbgcolor\",\"$highlightbgcolor\",\"left\",\"middle\",4,0,500,0,0,true,true,true,0,false,true);";

  foreach ($scatarray as $smenuitems){
    $t_parts = explode("#split#",$smenuitems);
    $t_type = $t_parts[0];
    $t_string = $t_parts[1];
    print "
      $t_string";
  };

  print "
      mm_menu_".$t_catmenuid.".hideOnMouseOut=true;
      mm_menu_".$t_catmenuid.".childMenuIcon=\"images/arrows.gif\";
      mm_menu_".$t_catmenuid.".menuBorder=0;
      mm_menu_".$t_catmenuid.".menuLiteBgColor='$highlightbgcolor';
      mm_menu_".$t_catmenuid.".menuBorderBgColor='$bordercolor';
      mm_menu_".$t_catmenuid.".bgColor='$normalbgcolor';\n";

  $i++;
};


print "
  mm_menu_".$t_catmenuid.".writeMenus();";
?>
} // mmLoadMenus()

//-->
</script>

[stevesweetland]

magicfunction() is just a way of me generating a 'random' number - the same number every time. this is important for the menu script to work....

[planetsim]

Im fustrated seeing people code like this

$sql = "SELECT * FROM Categories WHERE CatID < '9' OR CatID > '11' ORDER BY CatID ASC";
$db->query($sql);

Yes there isnt much wrong its correct however what a waste of a variable. You could just have it within the $db->query

Also im seeing a lot of

}; what is that suppose to be?

Also i tested your code and couldnt get it to work correctly i think your missing a } somewhere and i dont have the time to debug it at the moment.

Also dont use short tags "<?" use "<?php" only because it will work no matter what.

[Weedpacket]

Originally posted by planetsim
Im fustrated seeing people code like this

$sql = "SELECT * FROM Categories WHERE CatID < '9' OR CatID > '11' ORDER BY CatID ASC";
$db->query($sql);

Yes there isnt much wrong its correct however what a waste of a variable. You could just have it within the $db->query[/b]

I don't have a problem with that; my problem is with the use of SELECT instead of selecting only the fields that are needed. SELECT has the potential to be a time sink.

I also wonder why

$scatarray[] = "menuitem#split#mm_menu_".$t_catmenuid.".addMenuItem(mm_menu_".$t_scatmenuid.");"; 

and then explode later, instead of

$scatarray[] = array("menuitem",
"mm_menu_".$t_catmenuid.".addMenuItem(mm_menu_".$t_scatmenuid.");"); 

[planetsim]

But its a waste of a variable its what fustrates me. What is 2lines could be 1. Im all for cutting down the amount of useless lines of code in my work i know this is my opinion and by the sounds of it yours is quite different to mine.

Also a load time issue get into bigger projects and there will be a fairly resonable amount of load time difference, but smaller ones wont be as obvious if obvious at all (A couple of milliseconds)

[stevesweetland]

keep em coming!!

[stevesweetland]

$scatarray[] = "menuitem#split#mm_menu_".$t_catmenuid.".addMenuItem(mm_menu_".$t_scatmenuid.");";

in actual fact, the whole #split# thing was a remnant of a first attempt, and doesnt need to be there at all

$scatarray[] = "mm_menu_".$t_catmenuid.".addMenuItem(mm_menu_".$t_scatmenuid.");";

[planetsim]

Okay a mate of mine who ive just found out is part of the PHP5 development team. Has given some good advice on improving my Query statements. Actually a lot of tutorial sites (goot tutorials sites teach you this + here)

Anyway to get to the point

$sql = "SELECT * FROM Categories WHERE CatID < '9' OR CatID > '11' ORDER BY CatID ASC";

In your queries this being an example dont use

SELECT * FROM mytable

use

SELECT col_1,col_2, col_3 FROM mytable

Of course you must select the fields your require you dont need to specify them all.

[bubblenut]

Weed's on the PHP5 development team?

[planetsim]

Thatd be news to me, but no i wasnt referring about Weedpacket, but someone else (they are not registered here)

[Weedpacket]

Originally posted by planetsim
But its a waste of a variable its what fustrates me. What is 2lines could be 1. Im all for cutting down the amount of useless lines of code in my work i know this is my opinion and by the sounds of it yours is quite different to mine.

Also a load time issue get into bigger projects and there will be a fairly resonable amount of load time difference, but smaller ones wont be as obvious if obvious at all (A couple of milliseconds)

Nah, that difference is trivial. And Zend's optimiser, for example, will fold the two together and cache the result anyway. Standard optimisation technique.

Besides, it's not like variables are a scarce commodity; this isn't the old days when you needed hacks like the Golomb switch to fit things into memory. And if you're going to write a decent error logging mechanism you'll want to record the faulty queries somewhere for diagnostic purposes - and those variables are going to have to go back in again.

[Weedpacket]

Originally posted by planetsim
Thatd be news to me, but no i wasnt referring about Weedpacket, but someone else (they are not registered here)

Originally posted by Weedpacket
I don't have a problem with that; my problem is with the use of SELECT instead of selecting only the fields that are needed. SELECT has the potential to be a time sink.

[Weedpacket]

I'm thinking that a fair bit of time could be clawed back by taking the queries out of the loops somehow. Haven't looked too hard at the idea, but roughly

"SELECT foo FROM table WHERE blah"
foreach($foo)
{ "SELECT bar FROM anotherTable WHERE something=$foo"
...
}

could change into
"SELECT foo FROM table WHERE blah"
Collect all the $foos into an array
"SELECT bar FROM anotherTable WHERE something IN (list of all the $foos)"

Because making so many queries of the database as the nested structure implies might be a lot slower than collecting everything that might be relevant and sorting through it afterwards.

$sql = "SELECT foo FROM mytable WHERE condition='blah'";
$db->query($sql);
$foos = array();
while($db->next_record())
{	$foos[] = $db->field('foo');
	$bars[$foo]=array();
}
$sql = "SELECT foo,bar FROM mytable WHERE foo IN ('".join("','", $foos)."')";
while($db->next_record())
{	$foo = $db->field('foo');
	$bar = $db->field('bar');
	$bars[$foo][] = $db->field('bar');
}

etc.etc., building up a multidimensional array where each dimension is indexed
by the values returned by the corresponding query.

[ednark]

btw: weedpackets inclusing of the IN() phrase in your sql code should show you a dramatic decrease in total query time...

but well since we are being picky

instead of doing :

substr($string,$char,1)

you may directly access each char in the string with curly braces

$string{$char}

this won't give any dramatic performace boost but it is more efficient that a function call

also in your for loop... it will reevaluate strlen($string) each pass which it doens't need to do since it will never change. so i would throw in a variable instead of again using a function call each time

the result would be

function magicfunction($string){
  $newString = "";
  $str_len = strlen($string);
  for($char=0;$char<$str_len;$char++){
    $newString .= ord($string{$char})-96;
  }
  return $newString;
};

[stevesweetland]

Originally posted by Weedpacket
I'm thinking that a fair bit of time could be clawed back by taking the queries out of the loops somehow. Haven't looked too hard at the idea, but roughly

"SELECT foo FROM table WHERE blah"
foreach($foo)
{ "SELECT bar FROM anotherTable WHERE something=$foo"
...
}

could change into
"SELECT foo FROM table WHERE blah"
Collect all the $foos into an array
"SELECT bar FROM anotherTable WHERE something IN (list of all the $foos)"

Because making so many queries of the database as the nested structure implies might be a lot slower than collecting everything that might be relevant and sorting through it afterwards.

$sql = "SELECT foo FROM mytable WHERE condition='blah'";
$db->query($sql);
$foos = array();
while($db->next_record())
{	$foos[] = $db->field('foo');
	$bars[$foo]=array();
}
$sql = "SELECT foo,bar FROM mytable WHERE foo IN ('".join("','", $foos)."')";
while($db->next_record())
{	$foo = $db->field('foo');
	$bar = $db->field('bar');
	$bars[$foo][] = $db->field('bar');
}

etc.etc., building up a multidimensional array where each dimension is indexed
by the values returned by the corresponding query. [/B]

ok, so i get where ya comin from - but how would i then go about getting all the data i need, surely looping arrays, and querying for each row isnt going to be much more efficient, unless i have a multidimensional array...

$sql = "SELECT foo FROM mytable WHERE condition='blah'";
$db->query($sql);
$foos = array();
while($db->next_record())
{	$foos[] = $db->field('foo');
	$bars[$foo]=array();
}
$sql = "SELECT foo,bar FROM mytable WHERE foo IN ('".join("','", $foos)."')";
while($db->next_record())
{	$foo = $db->field('foo');
	$bar = $db->field('bar');
	$bars[$foo]["bar_id"] = $db->field('bar_id');
	$bars[$foo]["bar_name"] = $db->field('bar_name');
}

will this save me time - and more importantly, how am i going to deal with making this nest, like it does currently? Im dealing with an unspecified number of levels, surely this code will limit me to only one? unless, this goes into a function, and i call the function from within itself, like im doing at the moment. now, thats all well and good - but am i really going to be saving myself time?

[Weedpacket]

Still, the most important saving can come from cutting down the number of queries; if there are five articles per subcategory, five subcategories per category, and five categories that match, the existing code sends 155 queries to the DBMS, and has to wait for each query to be received, parsed, processed, and the results sent back before it can continue (and both servers have to wait for the OS's juggling the two processes in core).

$query="
Select
	c.CatID as CatID,
	s.SCatID as SCatId,
	a.ArtID as ArtID
	...and other fields to taste
FROM
	Categories c,
	SubCategories s,
	Articles a
WHERE
	(c.CatID<'9' OR c.CatID>'11')
	AND s.SCatDesc!='Home'
	AND s.CatID=c.CatID
	AND a.SCatID=s.SCatID
ORDER BY
	CatID ASC,
	SCatDesc DESC
";
$db->query($query);
while($db->next_record())
	$article[$db->field('CatID')][$db->field('SCatID')][$db->field('ArtID')]
		=array('SCatDesc'=>$db->field('SCatDesc'),etc);
// print_r($article);
foreach($article as $CategoryID=>$SubCategories)
{	foreach($SubCategories as $SubCategoryID=>$Articles)
	{	foreach($Articles as $ArticleID=>$Article)
		{
			//fnord
		}
	}
}