SQL Help [SOLVED]

[mark1million]

Hi,

I am writing a module to only display unique ICAO aircraft, this is what i have,

/**
    * Get an aircraft by unique name
    */
   public static function getAircraftByName1() {

       $sql = 'SELECT DISTINCT icao, fullname, weight, cruise, maxpax, maxcargo, imagelink, downloadlink
			FROM ' . TABLE_PREFIX . 'aircraft 
			WHERE `enabled` = 1 ';

       return DB::get_results($sql);
   }

That works fine, but when i try to add the range nothing is displayed so if i do,

/**
    * Get an aircraft by unique name
    */
   public static function getAircraftByName1() {

       $sql = 'SELECT DISTINCT icao, fullname, range, weight, cruise, maxpax, maxcargo, imagelink, downloadlink
			FROM ' . TABLE_PREFIX . 'aircraft 
			WHERE `enabled` = 1 ';

       return DB::get_results($sql);
   }

Not sure why but any help would be appreciated

Thanks.

[Guest lorathon]

try changing the field name like this

/**
    * Get an aircraft by unique name
    */
   public static function getAircraftByName1() {

       $sql = 'SELECT DISTINCT icao, fullname, range as acrange, weight, cruise, maxpax, maxcargo, imagelink, downloadlink
                               FROM ' . TABLE_PREFIX . 'aircraft 
                               WHERE `enabled` = 1 ';

       return DB::get_results($sql);
   }

see if that works

[Nabeel]

A couple things:

DISTINCT icao, fullname, range, weight ...

That looks for ALL of those fields as DISTINCT, you'd want to do DISTINCT(icao).

Aside from that, you might be looking to use GROUP BY instead of DISTINCT

edit: nevermind on the GROUP BY

edit: I see the problem.

Enclose field names with `:

$sql = 'SELECT DISTINCT `icao`, `fullname`, `range`, `weight`, `cruise`, `maxpax`, `maxcargo`, `imagelink`, `downloadlink`
                               FROM `' . TABLE_PREFIX . 'aircraft` 
                               WHERE `enabled` = 1 ';

[mark1million]

Thanks Jeff and Nabeel for your help.