Chosen with <optgroup> Support

jrahma

Hi,

I am trying to populate chosen with <optgroup> as per this:

http://harvesthq.github.io/chosen/#optgroup-support

I am using below php code to populate the areas but i want to use the governorates as optgroup.

I though of adding the governorate_name in the SELECT like this:

SELECT area_guid, area_name, governorate_name FROM areas

but how can I make PHP understand the governorate column and set it as optgroup?

<select id="cboHomeFindArea" name="cboHomeFindArea" data-placeholder="[Choose your Area..]" class="chosen-select" style="width:350px;" tabindex="2">
    <option value=""></option>
    <optgroup label="Capital Governorate">
    <?php
        if ($sql_connection)
        {
            $sql_stmt = "SELECT * FROM areas";
            $sql_query = sqlsrv_query($sql_connection, $sql_stmt);

        while($sql_row = sqlsrv_fetch_array($sql_query, SQLSRV_FETCH_ASSOC))
        {
?>
    <option value="<?php echo $sql_row['area_guid']; ?>"><?php echo $sql_row['area_name']; ?></option>
<?php
        }
    }
    else { die( print_r( sqlsrv_errors(), true)); }
?>
</optgroup>
</select>

Thanks,
Jassim

pbismad

There are two ways of doing this -

1) Add ORDER BY governorate, area_name to the sql query statement so that the rows in the result set are arranged together by first the governorate, then by the area_name within each governorate. When looping over the result set, you would detect a change in the governorate value, by using a variable to hold the 'last' value seen. When the value changes, you would close out any previous optiongroup and start a new optiongroup with the new value.

2) Pre-process (loop over) the result set and store the data into a multi-dimensional array using the governorate value as the main array's index. All the rows for any governorate value would be stored as a sub-array under the correct main index value. To produce the output, you would loop over the main array, the index would become the optiongroup value, then loop over each sub-array to produce the <option>....</option> list.

Method #2 results in the clearest code.

In any case, you should have an ORDER BY area_name as part of your sql query statement so that the output is in a determinate order.

jrahma

sorry I didn't get it 😕 I never done this before.. can you help with sample code please.. here is my starting code:

<select id="cboHomeFindArea" name="cboHomeFindArea" data-placeholder="[Choose your Area..]" class="chosen-select" style="width:350px;" tabindex="2">
    <option value=""></option>
    <?php
        if ($sql_connection)
        {
            $sql_stmt = "SELECT states.state_name, areas.area_guid, areas.area_name FROM areas JOIN states ON states.state_id = areas.state_id ORDER BY states.state_name, areas.area_name";
            $sql_query = sqlsrv_query($sql_connection, $sql_stmt);

        while($sql_row = sqlsrv_fetch_array($sql_query, SQLSRV_FETCH_ASSOC))
        {
?>
    <optgroup label="<?php echo $sql_row['state_name']; ?>">
    <option value="<?php echo $sql_row['area_guid']; ?>"><?php echo $sql_row['area_name']; ?></option>
    </optgroup>
<?php
        }
    }
    else { die( print_r( sqlsrv_errors(), true)); }
?>
</select>

jrahma

I managed to do it the below way and getting the correct result but is it the correct way?

<?php
	if ($sql_connection)
	{
		$sql_stmt = "SELECT states.state_name, areas.area_guid, areas.area_name FROM areas JOIN states ON states.state_id = areas.state_id ORDER BY states.state_name, areas.area_name";
		$sql_query = sqlsrv_query($sql_connection, $sql_stmt);

	$state_name = "";

	while($sql_row = sqlsrv_fetch_array($sql_query, SQLSRV_FETCH_ASSOC))
	{
		if ($state_name != $sql_row['state_name'])
		{
			if ($state_name != '') { echo '</optgroup>'; }
			echo "<optgroup label='". $sql_row['state_name'] . "'>";
		}
  ?>
	<option value="<?php echo $sql_row['area_guid']; ?>"><?php echo $sql_row['area_name']; ?></option>
<?php
			$state_name = $sql_row['state_name']; 
		}

	if ($state_name != '') { echo '</optgroup>'; }
}
else { die( print_r( sqlsrv_errors(), true)); }
?>

pbismad

Yes, this implements the first method that was described.

BTW - you should test if your database connection worked, immediately after making the connection. For a database driven site, if you cannot connect to the database, there's no point in running any of the rest of the code on your page. By the time you get to the point of querying for any data, you should already know that the connection worked and you don't need to test the connection again.

However, you should test each query to make sure it executed without any errors. Unfortunately, the sqlsrv extension doesn't implement exceptions, which is the easiest way of handling database errors. It would take writing a wrapper class to throw your own exceptions for sqlsrv query errors, which is beyond the scope of this thread. Edit: using PDO_SQLSRV would allow you to use exceptions to handle errors.