Issue with DISTINCT or GROUP BY on multiple Columns in SQL query

Question

I am using PHP and MYSQL to populate some dropdown lists from a database.

However, I have been at my wits end as to why the following query populates only the first column (City) - and that too without grouping, specified from the bunch of columns that needs to be grouped

SELECT City, NativeTown, Language, State 
FROM candidates 
WHERE ApprovalCode!='' AND Gender='$gtype' 
GROUP BY City, NativeTown, Language, State 
ORDER BY City,NativeTown,State,Language ASC

I tried this too

SELECT DISTINCT City, NativeTown, Language, State 
FROM candidates 
WHERE ApprovalCode!='' AND Gender='$gtype' 
ORDER BY City,NativeTown,State,Language ASC

But, in both, only City gets populated and that too without grouping

Where am I going wrong ?

PS:

Database

ACTUAL CODE

$getC=$maindb->query("SELECT City, NativeTown, Language, State FROM candidates 
WHERE ApprovalCode!='' AND Gender='$gtype' 
GROUP BY City, NativeTown, Language, State 
ORDER BY City,NativeTown,State,Language ASC") or die("LIST ERROR :".$maindb->error);


    City<br>
        <!-- <input type="text" name="city" id="city" Placeholder="City" <?php //if(isset($filtvals['city'])){echo "Value ='$filtvals[city]'";}?>>-->

        <select name="city" id="city">
                <option value="Choose">Choose</option>
                <?php while($getClist=$getC->fetch_assoc()){?>
                <option value="<?php  echo $getClist['City'];?>" <?php if(isset($filtvals['city']) && $filtvals['city']==$getClist['City']){echo "Selected='Selected'";}?>><?php  echo $getClist['City'];?></option>
                <?php } $e=error_get_last(); print_r($e);?>
        </select>
    </div>

    <div>State<br>
        <select name="State" id="State">
                <option value="Choose">Choose</option>
        <?php while($getClist=$getC->fetch_assoc()){?>
                    <option value="<?php  echo $getClist['State'];?>" <?php if(isset($filtvals['State']) && $filtvals['State']==$getClist['State']){echo "Selected='Selected'";}?>><?php  echo $getClist['State'];?></option>
            <?php }$e=error_get_last(); print_r($e); ?>
        </select>
    </div>

    <div>Native<br>
        <select name="NativeTown" id="native">
                <option value="Choose">Choose</option>

        <?php while($getClist=$getC->fetch_assoc()){?>
                <option value="<?php  echo $getClist['NativeTown'];?>" <?php if(isset($filtvals['NativeTown']) && $filtvals['NativeTown']==$getClist['NativeTown']){echo "Selected='Selected'";}?>><?php  echo $getClist['NativeTown'];?></option>
            <?php } ?>
        </select>
    </div>


    <div>Language<br>
        <!--<input type="text" name="native" id="native" Placeholder="Native Town" <?php //if(isset($filtvals['native'])){echo "Value ='$filtvals[native]'";}?>>-->
        <select name="Language" id="Language">
                <option value="Choose">Choose</option>

        <?php while($getClist=$getC->fetch_assoc()){?>
                <option value="<?php  echo $getClist['Language'];?>" <?php if(isset($filtvals['Language']) && $filtvals['Language']==$getClist['Language']){echo "Selected='Selected'";}?>><?php  echo $getClist['Language'];?></option>
            <?php } ?>
        </select>
    </div>

RESULT After City, other Select boxes do not get populated !

Answer 1

You should change all your PHP code. You are triyng to fetch the query results one time for each column, and that is not possible. You have to do some thing like this:

$getC=$maindb->query("SELECT City, NativeTown, Language, State FROM candidates 
WHERE ApprovalCode!='' AND Gender='$gtype' 
ORDER BY City,NativeTown,State,Language ASC") or die("LIST ERROR :".$maindb->error);
data=[];
while($getClist=$getC->fetch_assoc()){
    data[]=[$getClist['City'].$getClist['State'],$getClist['NativeTown']]
}

So now you have an array with data. Now in the code you need to iterate over that array.