Jump to content

Limiting Queries


Cod-nes
 Share

Recommended Posts

I have a sql table called menu. In the table I have menu links with parents and childs. Example:-> parent #1--> child #1--> child #2-> parent #2--> child #1

idlinknamepower // 1 if parent and 2 if childunder //if childorder
But I have too many queries sent to the database and I was wondering how can I limit the queries.Part of the code.
// already connected to sql host and database$query = mysql_query("SELECT * FROM menu where power = 1 ORDER BY order");while($row =  mysql_fetch_array($query)){echo "<a href='{$row['link']}'>{$row['name']}</a><br>/n";$query2 = mysql_query("SELECT * FROM menu where under = {$row['id']} ORDER BY order");$query3 = mysql_num_rows($query2);if($query3 >= 1) { while($row2 = mysql_fetch_array($query2))   {  echo "-> <a href='{$row2['link']}'>{$row2['name']}</a>";  } }}

Link to comment
Share on other sites

If you can only have one level of child items, you can add a sort order column to the table and just sort both parents and children out in the order you want them to show up. You can use the power column to figure out if you indent it before displaying it.

Link to comment
Share on other sites

If you can only have one level of child items, you can add a sort order column to the table and just sort both parents and children out in the order you want them to show up. You can use the power column to figure out if you indent it before displaying it.
How can I do that?
Link to comment
Share on other sites

Try something like this:

<?// <-- Connect to database here$result = mysql_query ("SELECT * FROM menu WHERE power='1' ORDER BY order") or die(mysql_error());$rownum = mysql_num_rows ($result);$i = 0;while ($i < $rownum) {   $under_id = mysql_result($result,$i,'id');   $under_list .= $under_id.',';   $i++;}// Remove the last ',' from the list for a safer query$under_list = substr($under_list,0,-1);// Get the under stuff$get = mysql_query ("SELECT * FROM menu WHERE under IN($under_list) ORDER BY order") or die(mysql_error());// <-- Close database here$gnum = mysql_num_rows ($get);$g = 0;// Reset i$i = 0;while ($i < $rownum) {   $parentid = mysql_result($result,$i,'id');   echo '<a href="'.mysql_result($result,$i,'link').'">'.mysql_result($result,$i,'name').'</a><br>/n';   while ($g < $gnum) {	  if ($parentid == mysql_result($get,$g,'under')) {		 echo '-> <a href="'.mysql_result($get,$g,'link').'">'.mysql_result($get,$g,'name').'</a>';	  }	  $g++;   }   // Reset g   $g = 0;   $i++;}?>

In total it uses 2 queries instead of x + (child * x).I haven't tested it but I'm sure the syntax is correct.Hope it helps!

Edited by Kameloh
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...