Prevent duplicating specific column from database table

Question

I have SQL table of cities and counties. I need to loop over them and create dropdown with labels. Cities should be an <option> elements and counties wrapper <optgroup>s.

function my_custom_shortcode() {
    global $wpdb;
    $results = $wpdb->get_results( "SELECT city, county FROM slovak_cities" );
    
    $select = '<select name="city_field">';
    
    foreach ( $results as $result ) {

        $city = $result->city;
        $county = $result->county;

        $select .= '<optgroup label="' . $county . '">';
        $select .= '<option value="' . $city . '">' . $city . '</option>';      
        $select .= '</optgroup>';
    }
    
    $select .= '</select>';
    
    return $select;
}
add_shortcode( 'my_shortcode', 'my_custom_shortcode' );

Counties are such groups of cities and should be displayed only one time without duplicating in every iteration. I get results like this:

Counties are duplicated and created wrapper of every city every iteration. It should be one county <optgroup> wrapper for all cities associated with it.

So I tried to edit my code, but results are completely wrong:

    global $wpdb;
    $results = $wpdb->get_results( "SELECT DISTINCT city, county FROM slovakia_cities" );
    
    $select = '<select id="city_field" name="city_field" data-required="yes" data-type="select">';
    
    $no_duplicates = array();

    foreach ( $results as $result ) {

        $city = $result->city;
        $county = $result->county;
        
        if ( ! in_array( $county, $no_duplicates ) ) {
            
            $select .= '<optgroup label="' . $county . '">';
            $select .= '<option value="' . $city . '">' . $city . '</option>';      
            $select .= '</optgroup>';
            array_push( $no_duplicates, $county );
        }
    }
    
    $select .= '</select>';

Answer 1

This is more of a general PHP question than a Wordpress one.

Use an array with the county as a key to group up your results.

$groups = array();
foreach( $results as $result ) {
    $groups[$result->county][] = $result;
}

var_dump or print_r on $groups to see how this groups up your results.

You can then loop over $groups, if there's only one item in a group you output a single option, when there's more than one then you make an option group.