Joomla Form field type sql="....WHERE condition"?

Question

In Joomla 2.5, how can I do a form field type "SQL" with the condition "WHERE" in the SQL query?

Example:

<field name="catid" type="sql" query="SELECT id, title FROM #__table WHERE {condition}" />

There isn't anything about this in the joomla docs. I've found this link but it doesn't refer anything of using a WHERE condition in the SQL query ( http://docs.joomla.org/SQL_form_field_type).

Can someone please tell me if this is possible or not to do in Joomla?

I have a table with products and a table with shops associated to brands. If I edit one shop I would like to show a combobox with all the products from the brand of the shop. Is that possible? SELECT * FROM #__products WHERE brand = {the brand id associated to the shop I selected for edit} Is this possible?

I was able to do this with a custom form field =)

http://docs.joomla.org/Creating_a_custom_form_field_type

Thank you RHR for your answer !

score 3 · Answer 1 · answered Nov 19 '15 at 03:34

Here's my solution:

You can replace the query completely by doing the following:

1)Define you query:

$query = 'SELECT * from table_name WHERE table_id = '.$your_value;

2)Update your form field attribute:

$this->form->setFieldAttribute('field_name', 'query', $query);

score 1 · Accepted Answer · answered Sep 29 '12 at 04:30

1

Yes its possible

My code which i used

<fields name="request">
 <fieldset name="request" >
<field name="id" type="sql" query="SELECT id , title FROM #__table name where published=1" multiple="single" key_field="id" value_field="title" class="inputbox" label="fieldlabel" description="fielddesc" required="true" />
</fieldset> 
</fields>

You can do this in another way as custom field

answered Sep 29 '12 at 04:30

rynhe

2,509
1
21
27

1

Thank you for your answer. But my question is can I do something like this? I have a table with products and a table with shops associated to brands. If I edit one shop I would like to show a combobox with all the products from the brand of the shop. Is that possible? SELECT * FROM #__products WHERE brand = {the brand id associated to the shop I selected for edit} Is this possible? Thank you! – Snitram200 Sep 29 '12 at 11:33

score 1 · Answer 3 · answered May 23 '13 at 20:33

Just had the same issue. After some digging I've figured out it is actually possible.

1. Step - You need a custom form field: (I named it vssql.php)

JFormHelper::loadFieldClass('sql');

class JFormFieldVSSQL extends JFormFieldSQL
{
    /**
     * The form field type.
     */
    public $type = 'VSSQL';

    /**
     * Overrides parent's method
     */
    protected function getOptions()
    {
        // Get the ID
        $query_id = $this->form->getField((string) $this->element['query_id']);

        // Override query
        $this->element['query'] = str_replace('{query_id}', (string) $query_id->value, (string) $this->element['query']);

        return parent::getOptions();
    }
}

2. Step - You need to know hot to use it:

In you JForm xml add:

  <field
    name="field_name"
    type="vssql"
    query_id="user_id"
    query="SELECT id AS value, CONCAT(street,' ',city) AS addresses FROM #__addresses WHERE user_id = {query_id}"
    label="List of user addresses"
    required="required" />

In your SQL query the placeholder {query_id} will be replaced with the real value of parameter specified in query_id, which is *user_id* in this case. That will return a list of addresses from some table assigned to given user

score 1 · Answer 4 · answered Sep 29 '15 at 11:18

Some updates to @WooDzu solution:

class JFormFieldICategory extends JFormFieldSQL
{

    public $type = 'ICategory';

    /**
     * Overrides parent's method
     */
    protected function getOptions()
    {
        // Get the ID
        $cat_id = $this->form->getField((string) $this->element['cat_id']);

        // Override query
        $this->query = str_replace('{cat_id}', (string) $cat_id->value, (string) $this->element['query']);
        echo $this->query.' test '; //exit();

        return parent::getOptions();
    }

}

Type should be public and query should be assign to $this->query. Works great!

score 0 · Answer 5 · answered May 24 '13 at 20:52

0

This works for me (I used this to select user plugins from #__extensions table) in Joomla 2.5+ and 3.x+) as WHERE condition:

<field name="plugin" type="sql" query="SELECT extension_id as value, name as title FROM #__extensions WHERE type = 'plugin' AND folder = 'user'" />

answered May 24 '13 at 20:52

Lahmizzar

487
6
6

I think it's all about how to use variable in where clausule. – Barto May 25 '15 at 16:12

score 0 · Answer 6 · answered May 21 '15 at 10:30

I had the same problem. You cannot make where condition based on variable, only constant:(

BUT You can change the form field and reload the field in view with form->setField function. Thanks to this You can make any condition in field sql.

$NewFieldDefinition=new SimpleXMLElement('<field name="catid" type="sql" query="SELECT id, title FROM #__table WHERE title='.$condition.'" />');
$this->form->setField($NewFieldDefinition, null, true);

Joomla Form field type sql="....WHERE condition"?

6 Answers6