Jump to content

select field with multiple sub-categories - too inefficient


1internet

Recommended Posts

I have a select box that filters categories. However, there are also sub-categories, and sub-sub-categories, etc.

  <form method="GET" action="categories.php">
    <div class="form">
      <select id="filter" size="1" name="filterCat">
        <option value="allCats" <?php if ($filterCat == 'allCats') echo 'selected="selected"';?>>All Categories</option>
        <option value="0" <?php if ($filterCat == 0) echo 'selected="selected"';?>>MAIN</option>
        <option value="noItem" disabled="disabled" >-------------------------------</option>
        <?php
			$cat_result = mysql_query("SELECT `categories_id`,`categories_name` FROM `categories` WHERE `parent_id`=0 ORDER BY `categories_name`");
			while($cat_row = mysql_fetch_assoc($cat_result)){
				$categories_id = $cat_row['categories_id']
				?>
				<option value="<?php echo $categories_id; ?>" <?php if ($filterCat == $categories_id) echo 'selected="selected"';?>> <?php echo $cat_row['categories_name']; ?></option>
				<?php
					$cat_result2 = mysql_query("SELECT `categories_id`,`categories_name` FROM `categories` WHERE `parent_id`=$categories_id ORDER BY `categories_name`");
					while($cat_row2 = mysql_fetch_assoc($cat_result2)){
					$categories_id2 = $cat_row2['categories_id']
					?>
					<option value="<?php echo $categories_id2; ?>" <?php if ($filterCat == $categories_id2) echo 'selected="selected"';?>>  - <?php echo $cat_row2['categories_name']; ?></option>
					<?php
						$cat_result3 = mysql_query("SELECT `categories_id`,`categories_name` FROM `categories` WHERE `parent_id`=$categories_id2 ORDER BY `categories_name`");
						while($cat_row3 = mysql_fetch_assoc($cat_result3)){
						$categories_id3 = $cat_row3['categories_id']
					?>
						<option value="<?php echo $categories_id3; ?>" <?php if ($filterCat == $categories_id3) echo 'selected="selected"';?>>    -- <?php echo $cat_row3['categories_name']; ?></option>
						<?php
							$cat_result3 = mysql_query("SELECT `categories_id`,`categories_name` FROM `categories` WHERE `parent_id`=$categories_id3 ORDER BY `categories_name`");
							while($cat_row4 = mysql_fetch_assoc($cat_row4)){
							$categories_id4 = $cat_row4['categories_id']
						?>						
							<option value="<?php echo $categories_id4; ?>" <?php if ($filterCat == $categories_id4) echo 'selected="selected"';?>>    -- <?php echo $cat_row4['categories_name']; ?></option>
				<?php }}}} ?>
      </select>
    </div>
  </form>

So my issue is that I repeat the code for each new sub-category, and create a new loop to look up the sub-categories belonging to that one. Is there a simpler way that will cause the categories to look up if they have any sub-categories, and then run a loop from there, i.e. infinitely, so that if there were even 10 levels of categories, it would still be able to find them all?

Link to comment
Share on other sites

You could make a recursive function to do that. Something like this:

getChildren($pid)
{
	$cats = array();
	$r = mysql_query('SELECT * FROM cats WHERE parent = '.$pid);
	if (!mysql_num_rows($r))
		return false;
	while ($row = mysql_fetch_assoc($r))
	{
		if ($children = getChildren($row['catid']))
		$cats[$row['catid']] = $children;
	}
		
	return $cats;
}

It hurts my brain to try to check that without actual data, but I think the general idea in there should be in working order.

Link to comment
Share on other sites

For this you really should be looking at JOINs.

Running queries inside loops is really inefficient, as you've noticed, and it wastes time like nothing else. This is also what relational databases do best, retrieve data from related tables via a JOIN statement. ;)

 

Quick example:

SELECT m.`cat_id`, m.`name`, s.`cat_id`, s.`name`
FROM cats AS m
LEFT JOIN cats AS s ON s.`parent_id` = m.`cat_id`
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.