Need help to cut down drastically on query count!!!!!

[Damascus]

Ok, so I got this code that displays categories and subcategories. It works just fine, but you can tell just by looking at it that the more categories there are the more friggin' queries there are.

Is there a way to do this with just ONE query? I'm sure there is, but I can't quite grasp the concept. Any help would be great!

		$getOrder = query("SELECT cat_id, cat_name, cat_order, cat_gotsub FROM {$kbase[table_prefix]}tblcats WHERE cat_parent =  '0' ORDER BY cat_order ASC");

	while($order = mysql_fetch_array($getOrder)){

		$oList .= "
span class='highlight2'>{$order[cat_name]}</span> [ {$order[cat_order]} ]<br> ";

		$getSubOrder = query("SELECT cat_id, cat_name, cat_order, cat_gotsub FROM {$kbase[table_prefix]}tblcats WHERE cat_parent =  {$order[cat_id]} ORDER BY cat_order ASC");

		while($subOrder = mysql_fetch_array($getSubOrder)){

			$oList .= "&nbsp;&nbsp;&nbsp;&nbsp;-- <span class='highlight3'>{$subOrder[cat_name]}</span> [ {$subOrder[cat_order]} ]<br>";

		}

	$oList .= "<br>";

	}

🙂

[ednark]

you will have many queries if you have so many tables...

you could get away with one category table and then one table for each type of thing you want to categorize

try a structure like this

table CATEGORIES
cat_id, cat_name, cat_parent

table ITEMS
item_id, item_name, cat_id

table OTHER_ITEMS
item_id, item_name, cat_id

table MORE_ITEMS
item_id, item_name, cat_id

[Damascus]

thx, but I'm only using one table for this and that's all I need. It's a category table and a record is either stand alone or a sub-category of another.

This is the code I used:

		$getOrder = query("SELECT * FROM {$kbase[table_prefix]}tblcats ORDER BY cat_order ASC");

	while($order = mysql_fetch_array($getOrder)){

		if($order['cat_parent'] != 0){

			$subOrder[$order['cat_id']] = $order; 

		}else{

			$Order[$order['cat_id']] = $order; 
		}

	}

	$oList = "<table cellpadding='3' cellspacing='0' width='100%' align='center'>";

	foreach($Order as $id => $cat){

		$oList .= "
			<tr>
				<td><span class='highlight2'>{$cat[cat_name]}</span> [ {$cat[cat_order]} ]</td>
				<td align='center'>[ <a href='{$kbase[server_path]}/index.php?act=cp_cat&amp;CODE=3&amp;cid={$cat[cat_id]}'>delete</a> ]</td>
				<td align='center'>[ <a href='{$kbase[server_path]}/index.php?act=cp_cat&amp;CODE=2&amp;cid={$cat[cat_id]}'>edit</a> ]</td>
			</tr>
		";

		foreach($subOrder as $subid => $sub){

			if($sub['cat_parent'] == $cat['cat_id']){

				$oList .= "
					<tr>
						<td>&nbsp;&nbsp;&nbsp;&nbsp;-- <span class='highlight3'>{$sub[cat_name]}</span> [ {$sub[cat_order]} ]</td>
						<td align='center'>[ <a href='{$kbase[server_path]}/index.php?act=cp_cat&amp;CODE=3&amp;cid={$sub[cat_id]}'>delete</a> ]</td>
						<td align='center'>[ <a href='{$kbase[server_path]}/index.php?act=cp_cat&amp;CODE=2&amp;cid={$sub[cat_id]}'>edit</a> ]</td>
					</tr>
				";

			}else{

				continue;

			}

		}

	}

	$oList .= "</table>";

[ednark]

oh, my bad, i thought

{$kbase[table_prefix]}

was changing every time it was called, meaning you having lots of different tables...

after looking at your code more closeely, your last example looks good, sorry