Page 1 of 1

MySQL and DISTINCT

Posted: Thu Dec 03, 2009 8:04 am
by ianhull
Hi all,

I am trying to return fields from my database table 'locations'

However, I have multiple entries of the same locationName but the lat and lon fields are different.
I just want to return unique locationName + other fields.

Code: Select all

 
 
<locations>
<location>
  <locationID>1</locationID>
  <locationName>Aberdeen</locationName>
  <locationLink>/aberdeen/</locationLink>
  <locationLatitude>9.9999</locationLatitude>
  <locationLongtitude>-2.9260</locationLongtitude>
  <locationPostcode>AB56</locationPostcode>
</location>
<location>
  <locationID>1</locationID>
  <locationName>Aberdeen</locationName>
  <locationLink>/aberdeen/</locationLink>
  <locationLatitude>9.9999</locationLatitude>
  <locationLongtitude>-3.0670</locationLongtitude>
  <locationPostcode>AB36</locationPostcode>
</location>
<location>
  <locationID>1</locationID>
  <locationName>Aberdeen</locationName>
  <locationLink>/aberdeen/</locationLink>
  <locationLatitude>9.9999</locationLatitude>
  <locationLongtitude>-3.3510</locationLongtitude>
  <locationPostcode>AB37</locationPostcode>
</location>
<locations>
 
 
 
How would I achive this?

Code: Select all

 
 
public function all_locations($data){
        
        $q = mysql_query("SELECT  DISTINCT locationName AS 'locationName1', locationLink, locationID, locationPostcode,     locationLatitude, locationLongtitude FROM locations ORDER BY locationName ASC")
        or die(mysql_error());
        
        if(mysql_affected_rows() == 0){
            
            return array('output'=>'0');
            
        }else{
            
            while($r = mysql_fetch_array($q)){
                
                extract($r);
                
                if($data['locationLink'] == $locationLink){
                    
                    $selected = 'selected="selected"';
                    
                }else{
                    
                    $selected = '';
                    
                }//end if
                
                $location[] = array('locationID'=>$locationID,
                                    'locationName'=>$locationName1,
                                    'locationLink'=>$locationLink,
                                    'locationSlash'=>$locationLink.'/',
                                    'locationLatitude'=>$locationLatitude,
                                    'locationLongtitude'=>$locationLongtitude,
                                    'locationPostcode'=>$locationPostcode,
                                    'selected'=>$selected,
                                    'output'=>'1');
                
            }//end while
            
        return $location;
        
        }//end if
        
    }//end function
    
 
 
Is it something like SELECT DISTINT locationName, NON-DISTINCT locationLink

I just can't seem to get this to work.
Any help greatly appreciated.


Thanks in advance

Re: MySQL and DISTINCT

Posted: Thu Dec 03, 2009 9:36 am
by Eran
You can return unique values by using a GROUP BY operator on a specific field (for example, GROUP BY locationName). Note however, that if columns have multiple values for the same unique field, you can't control which values out of those are returned for any particular row.