Page 1 of 1

[SOLVED] Grouping for select box

Posted: Wed Oct 06, 2004 3:06 pm
by leewad
I need to group a selection of items from database- there are two fields

1, type
2, newbuild

i need to group all types and also which are newbuild, I have the following code but it displays the following:
heres the display

Image

Code: Select all

function gettypes($table)
{
	$query = "SELECT type, newbuild, COUNT(*) as items FROM `".$table."` WHERE status != 'hide' group by type, newbuild order by items DESC, type;";
	$result = mysql_query($query);
	$num_results = mysql_num_rows($result);
    if ($num_results > 7)
		$selectSize = 7;
   	else
		$selectSize = $num_results + 1;

	echo '<select class="inputbox" name="type[]" MULTIPLE size='.$selectSize.'>';
	echo '<option value="any" selected >&nbsp;&nbsp;- any -</option>';
	for ($i= 0; $i <$num_results; $i++)
	{
    	$row = mysql_fetch_array($result);

        if ($row["newbuild"] <>"newbuild") {
		echo stripslashes('<option value="'.$row["type"].'">'.$row["type"].' ('.$row["items"].')'.'</option>');

    }else {

  echo stripslashes('<option value="'.$row["newbuild"].'">'.$row["newbuild"].' ('.$row["items"].')'.'</option>');
	} }
	echo '</select>';
}
can anybody spot why it is showing 2 newbuilds in the input box ?


Cheers

Posted: Wed Oct 06, 2004 3:10 pm
by feyd
the types didn't match for both.. there were 3 newbuilds, 2 matched types.

... and please use

Code: Select all

code blocks for posting php code.

Posted: Wed Oct 06, 2004 3:20 pm
by leewad
yes 2 different types ( apartments and villas ) both also newbuilds
But I need it to display just 1 newbuild selection ( both types combined ) - how can I do this ?

Sorry about use [code.]

Posted: Wed Oct 06, 2004 3:25 pm
by feyd
could try switching the order you do the grouping. i.e. 'group by newbuild, type' however, depending on how your data is set up, this may group the others into a single clump. So you may require 2 seperate queries, or what may be easier is a [mysql_man]union[/mysql_man].

Posted: Wed Oct 06, 2004 3:35 pm
by leewad
Thanks feyd the UNION sorted it