MySQL and DISTINCT

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ianhull
Forum Contributor
Posts: 310
Joined: Tue Jun 14, 2005 10:04 am
Location: Hull England UK

MySQL and DISTINCT

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: MySQL and DISTINCT

Post 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.
Post Reply