select field with multiple sub-categories - too inefficient

[1internet]

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?

[lemmin]

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.

[Christian F.]

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`

[1internet]

@lemmin - thanks, thats exactly what I was thinking.

 

@Christian F. - yes, thats ideally where I would like to get it, but its a little bit too complex for me yet.

[trq]

yes, thats ideally where I would like to get it, but its a little bit too complex for me yet.

So your just going to put off learning how to do things properly for now?

[1internet]

Ha, no no, not at all. I just don't really have the time to get my head around it, I understand the join if there was just a category and sub-category, but when you could have multiple child levels, I don't understand how to do the join there.

[1internet]

Oh wait for

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`

Does this need to be in a loop, so it obtains all child levels?

[Jessica]

It's probably best if you get it all worked out manually, then figure out how to write the query using a loop.

[Yohanne]

to add your logic simply follow this if you want to get what you want.

 

http://i1323.photobucket.com/albums/u587/jayson_ph/jph_200004_zpsca9519b3.png 

 

from above use

SELECT DISTINCT 

AND 

INNER JOIN