loop – 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.

enter image description here

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:

enter image description here
enter image description here

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>';

0
Juraj 2 months 2022-10-08T17:00:25-05:00 0 Answers 0 views 0

Leave an answer

Browse
Browse