Select lists

crush123

I have a couple of questions re select lists using editor

1. Order of labels from a recordset

As an example, i have this syntax in my ajax page

$data['refitemtype'] = $db
    ->selectDistinct( 'refitemtype', 'ItemTypeID as value, ItemTypeDescription as label' )
    ->fetchAll();
}

Is there a way I can set the ORDER of the select labels without resorting to a view ?

1. Is it possible to add a static option to the top of the list, without a value, saying something like 'please choose an option from the list' rather than having the first row displayed ?

Thnaks

allan

1) Is there a way I can set the ORDER of the select labels without resorting to a view ?

Certainly - the selectDistinct method has an optional order option as the fourth parameter.

Is it possible to add a static option to the top of the list, without a value, saying something like 'please choose an option from the list' rather than having the first row displayed ?

You could modify the $data['refitemtype'] array once it has been retrieved and unshift the "Please select..." text to it. You would also need to make sure your field validator will reject the default value!

Allan

crush123

re 1) How do I skip the 3rd parameter ?

I tried double comma, quotes etc but get a syntax error.

eg if I want to order by sizedescription

    ->selectDistinct( 'refsize', 'SizeID as value, SizeDescription as label','','SizeDescription' )

allan

Just put in null :-)

->selectDistinct( 'refsize', 'SizeID as value, SizeDescription as label',null,'SizeDescription ASC' )

Allan

crush123

well maybe its me, but that gives me a json error

for example, if I try

        ->selectDistinct( 'refsize', 'SizeID as value, SizeDescription as label',null,'SizeDescription')

i get

Notice: Undefined variable: identifier in ~\DataTables-1.10.4\extensions\Editor-1.3.3\php\Database\Query.php on line 403 Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column '' in 'order clause'' in ~\DataTables-1.10.4\extensions\Editor-1.3.3\php\Database\Driver\Mysql\Query.php:112...

allan

Odd. Could you try:

->selectDistinct( 'refsize', 'SizeID as value, SizeDescription as label',null,'label')

please?

Allan

crush123

Sorry, that gives me the same error

allan

Ah - sorry, you are missing the direction specified for the order clause that I had in my post above. I know it isn't always required in SQL, but the Editor libraries do need it:

->selectDistinct(
  'refsize',
  'SizeID as value, SizeDescription as label',
  null,
  'label ASC'
)

Allan

edit - reformatted the code to make it clearer).

crush123

cool, that's it.

(Also works with the original field name, as well as the label alias)