[ic] select widget with lookup_select and sql

[Aaron Berg]

Hi All,

I'm having issues getting IC to jump through the hoops I want it to
(or jumping through IC's hoops).  I need to restrict which items
appear in a dropdown based on a query.

Quick DB overview:

listing table
----------
listing_id
many fields...

rooms table
---------
room_id
listing_id
other fields....

image table
--------
image_id
room_id
listing_id

When editing an image I'd like to have a list of rooms in a drop down
based on rooms related to the same listing as the image.  I've tried
putting a query in the extended.lookup_query box in the admin UI (it's
labeled 'SQL for options').  The query looks like this:

SELECT room_id, room_name FROM listing_rooms WHERE listing_id = [cgi item_id]

I realize I should escape the cgi value, but that's left out of this example.

When I put that query in the field it's passed verbatim to MySQL.
I've also tried using the [table-editor] with a
lookup_query.room_id="same query as above" and had the same results.

Is there any way to interpolate the data put into 'lookup_query'?  Is
there another way to accomplish this short of manually creating the
select box?  It seems that IC has the tools in place to do this, but I
don't know how to tie them together to get the result I want.

Thanks.