Populate select box from database

[Vera]

I searched the extensions and also the forum for a way to have tl fill a select box with options from a database. Couldn't find anything ops:

So for example I'd have used the taxonomy extension to create a list of all fruits there are for use in the catalog. But inside a request form I'd need the same list of fruits. I could create a select field and fill it by hand. But that means I have to maintain 2 lists! It would be nice to be able to say "fill this select box with options from table x, field y [but only if field z equals a]"

Any pointers appreciated!

[ga.n]

Hi Vera,

It would be nice to be able to say "fill this select box with options from table x, field y [but only if field z equals a]"Any pointers appreciated!

you can use 'options_callback' (http://www.typolight.org/reference.html#fields)

e.g.:

//... field configuration here
'options_callback' => array('myList', 'generateList')
//... field configuration here

class myList extends Backend {

    public function generateList() {
        $arrRet = array();
                
        $qry =  " SELECT id, y, z FROM x WHERE z=? ";
        
        $objRow = $this->Database->prepare($qry)->execute('a');
        
        while ($objRow->next())
        {
         
         $arrRet[$objRow->id] = $objPage->id . ' - ' . $objRow->y;
        }
        
        return $arrRet; 
   }

}

[tosca]

I'm trying to use this answer in a custom extension, but I don't understand what 'a' stands for, in the following instruction:

        $objRow = $this->Database->prepare($qry)->execute('a');

Any hint would be appreciated.

[ramjet]

hi tosca,
"a" is what the ? placeholder becomes (that is if "a" was 32 the query would be WHERE z = 32).
If you had two or more ? in your query they are read in order - "....WHERE name=? AND pid=?")->execute('fred',32) becomes
....WHERE name='fred' AND pid=32")

[tosca]

Thanks for your answer ... that leads to another question
Where/how do you assign a value to 'a'? It doesn't appear as a parameter of the generateList function ... so does it belong to the Backend class?

This is my very first extension and I'm a bit lost ops:

[ramjet]

Vera and ga.n are talking in pseudo code.... there is no x,y,z,a... they just stand for things.
For example table x just means the table youre selecting from (eg tl_myfruit), id, y and z stand for fields you are wanting returned from that table (the id number and for example price and fruit-type) but you only want a return WHERE z=a... that is where fruit-type = 'banana', so in this case a would be replaced by 'banana'.

        $qry =  " SELECT id, y, z FROM x WHERE z=? ";
        
        $objRow = $this->Database->prepare($qry)->execute('a');

would become

        $qry =  " SELECT id, price, fruit-type FROM tl_myfruit WHERE fruit-type=? ";
        
        $objRow = $this->Database->prepare($qry)->execute('banana');

Where/how do you assign a value to 'a'?

It depends on what your module is doing, but "a" can be any string, number or variable you want. There is no easy answer to that. In this case it is being used in the query to limit the return to bananas only.

You need to know what you are qerying for in the database, and what limits you want on your return.
The rest of ga.n's code shows how Typolight requires the code to be structured, and what you do with the returned values ...The database return is not a value, its an array of many records, so you run though each record and extract what you want, then do something with it.

[tosca]

Thanks for taking time to help me :P

ga.n are talking in pseudo code.... there is no x,y,z,a... they just stand for things.

This was rather clear

I was just wondering wether it was possible to set a value for 'a' from outside of the function.
In fact, I see no real advantage to code that:

        $qry =  " SELECT id, price, fruit-type FROM tl_myfruit WHERE fruit-type=? ";
        
        $objRow = $this->Database->prepare($qry)->execute('banana');

when it could be simply written:

        $qry =  " SELECT id, price, fruit-type FROM tl_myfruit WHERE fruit-type='banana' ";
        
        $objRow = $this->Database->prepare($qry)->execute();

or I am wrong?

It depends on what your module is doing, but "a" can be any string, number or variable you want.

Fact is I would like to use the same query with different values for the WHERE clause, but I don't know how to set the parameter a different value each time (I know how to do it in "ordinary" PHP, but not in POO-Class-Object mode).

[ramjet]

yes, it can also be written that way...

I don't know how to set the parameter a different value each time

I guess you would use a variable .....WHERE fruit-type='$myVar', and if the value for $myVar was generated outside of the function you would need to declare $myVar global and pass it in.
You can also do stuff within the function before the database query.

Are you just trying to populate a dropdown Select menu from a table, or do something else?

[tosca]

Are you just trying to populate a dropdown Select menu from a table, or do something else?

Just that, but from the taxonomy table, and each time within a different subtree; so I'm going to need a WHERE clause on the pid value.

[ramjet]

Sorry Tosca, i didn't notice that this post is in the extensions forum.
I've not used Taxonomy, so i haven't a clue.
Hopefully someone else will be able to help

[ramjet]

Thank you gan, that was an immensely helpful post with great code example.... (i'm just getting to a point where I'm starting to twig with all this stuff that was over my head.
:D