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 !

Edit your question and show sample data and the results you are getting. — Gordon Linoff, Aug 03 '17 at 11:00
your `SELECT` queries are just impossible to return only `city` field rows. Dig into your php code. — Tedo G., Aug 03 '17 at 11:02
@GordonLinoff , I have mentioned that I have the listed columns with data but the result is that the select boxes do not get populated for columns other than City — user3526204, Aug 03 '17 at 11:05
@GordonLinoff Please check. I have updated everything you had asked. Do give me a solution now — user3526204, Aug 03 '17 at 11:17
This is why I find this forum pathetic sometimes. Downvotes galore (Shoot and Scoot ) ! But No Solutions ! — user3526204, Aug 03 '17 at 11:24
Yes, however, I have seen somewhere that "SELECT DISTINCT column_name" is same as "SELECT column_name GROUP BY column_name" — user3526204, Aug 03 '17 at 11:46

score 2 · Accepted Answer · answered Aug 03 '17 at 11:24

2

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.

answered Aug 03 '17 at 11:24

nacho

5,280
2
25
34

I did think about it, @nacho. But I wanted trying to fetch everything in one go. And thank you for pointing out that it is not possible. That is a real answer. Unlike the vain 'No-Solution-Down-Voters' ! – user3526204 Aug 03 '17 at 11:29
1

And don't use the GROUP BY clause, you don't need it – nacho Aug 03 '17 at 11:32
Reason is, I want only DISTINCT values – user3526204 Aug 03 '17 at 11:42
So then use SELECT DISTINCT City, ... it's more efficient & clear – nacho Aug 03 '17 at 11:47
I have tried that too and I have seen somewhere that "SELECT DISTINCT column_name" is same as "SELECT column_name GROUP BY column_name" – user3526204 Aug 03 '17 at 11:50

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

1 Answers1