Making a dynamic selection box with conditions

[schwim]

Hi there everyone,

For a classifieds system, I need to allow the user to select an ad category via a selection box, but am not sure how to generate the select via my conditions. Here's my table.

[ATTACH]4265[/ATTACH]

I need to create the dropdown to list all categories that ads are allowed in. In addition to this, I need to list each selection with their parent category, so instead of:

bike
parts
other
bike
parts
other

the selection should look like

for sale~>bike
for sale~>parts
for sale~>other
wanted~>bike
wanted~>parts
wanted~>other

Could someone help me with the query and form selection that would lead to this end? Any help is greatly appreciated.

thanks,
json

[big_nerd]

schwim,

Try the below - I am unsure of if this is exactly what you're looking for, and it may not be the best method -- but off the top of my head it should work.

<?php
$query = "SELECT * FROM `table` WHERE `ads_allowed` > 0 ORDER BY `parent`,`title` ASC;";
$result = mysql_query($query);

$parents = array();
$categories = array();
while ($row = mysql_fetch_assoc($result)) {
  if ($row['parent'] == 0) {
    $parents[$row['id']] = $row['title'];
  } else {
    $categories[$row['id']] = $parents[$row['parent']]."~>".$row['title'];
  }// end if
}

printf('<pre>&#37;s</pre>',print_r($categories,true));

?>

If that works to your satisfaction...

<select name="category" id="category">
<?php
  foreach ($categories as $id => $title) {
    echo '<option value="'.$id.'">'.$title.'</option>'."\n";
  }
?>
</select>

The above would put it into a nice little select box. Keep in mind ordering by parent is essential as you want all items that have no parent (0) to be parsed by the while statement first.

Hope this helps!

[schwim]

Thank you so much for your help! I'm having one problem with the code, however:

[EDIT]I've figured out what's wrong, but now I'm not sure if I need to change something else.

The query wasn't picking up the parent categories because of the condition that only categories with allowed >0 should be retrieved.

The array originally printed like this:

Array
(
[3] => ~>Bikes
[5] => ~>Other
[4] => ~>Parts
[6] => ~>Bikes
[8] => ~>Other
[7] => ~>Parts
)

So I changed :

$query = "SELECT * FROM `classifieds_categories` WHERE `ads_allowed` > 0 ORDER BY `parent`,`title` ASC;";
							$result = mysql_query($query);

to:

$query = "SELECT * FROM `classifieds_categories` ORDER BY `parent`,`title` ASC;";
							$result = mysql_query($query);

Which gave me what looks like a proper array:

Array
(
[3] => For Sale~>Bikes
[5] => For Sale~>Other
[4] => For Sale~>Parts
[6] => Wanted~>Bikes
[8] => Wanted~>Other
[7] => Wanted~>Parts
)

So it looks like it fixed it. I'm confused now about which part of the code is keeping the categories that you're not allowed to post in out of the array. I thought removing that was going to give me an array that looked like this:

Array
(
[1] => For Sale~>
[2] => Wanted~>
[3] => For Sale~>Bikes
[5] => For Sale~>Other
[4] => For Sale~>Parts
[6] => Wanted~>Bikes
[8] => Wanted~>Other
[7] => Wanted~>Parts
)

But your code seems to have taken care of this elsewhere. Could you let me know where this is covered in the code? I would very much like to understand how it handled that.

For reference, here's my implementation of your code:

$query = "SELECT * FROM `classifieds_categories` ORDER BY `parent`,`title` ASC;";
$result = mysql_query($query);

$parents = array();
$categories = array();
while ($row = mysql_fetch_assoc($result)) {
  if ($row['parent'] == 0) {
	$parents[$row['id']] = $row['title'];
  } else {
	$categories[$row['id']] = $parents[$row['parent']]."~>".$row['title'];
  }// end if
}

printf('<pre>%s</pre>',print_r($categories,true));

echo("<br><br>

<select name='category' id='category'>");
foreach ($categories as $id => $title) {
	echo '<option value="'.$id.'">'.$title.'</option>'."\n";
}
echo("</select>");

thanks,
json

[big_nerd]

Try the following:

$debug = true;
while ($row = mysql_fetch_assoc($result)) {
	echo ($debug) ? print_r($row,true) : '';
  if ($row['parent'] == 0) {
  	echo ($debug) ? 'Parent is 0 - Setting row '.$row['id'].' to '.$row['title'] : '';
		$parents[$row['id']] = $row['title'];
  } else {
		$categories[$row['id']] = $parents[$row['parent']]."~>".$row['title'];
  }// end if
}

That should display (you may have to view source for this if its within a select tag)

[schwim]

This is what I get:

Array
(
[id] => 1
[parent] => 0
[ads_allowed] => 0
[title] => For Sale
[description] => Master category for all items being offered for sale.
)
Parent is 0 - Setting row 1 to For SaleArray
(
[id] => 2
[parent] => 0
[ads_allowed] => 0
[title] => Wanted
[description] => Master category for all items you're in search of.
)
Parent is 0 - Setting row 2 to WantedArray
(
[id] => 3
[parent] => 1
[ads_allowed] => 1
[title] => Bikes
[description] => Bikes for sale
)
Array
(
[id] => 5
[parent] => 1
[ads_allowed] => 1
[title] => Other
[description] => All for sale ads that don't belong in either bikes or parts
)
Array
(
[id] => 4
[parent] => 1
[ads_allowed] => 1
[title] => Parts
[description] => Parts for sale
)
Array
(
[id] => 6
[parent] => 2
[ads_allowed] => 1
[title] => Bikes
[description] => Bikes wanted
)
Array
(
[id] => 8
[parent] => 2
[ads_allowed] => 1
[title] => Other
[description] => All wanted ads that don't belong in either bikes or parts
)
Array
(
[id] => 7
[parent] => 2
[ads_allowed] => 1
[title] => Parts
[description] => Parts wanted
)