Exclude options in category drop down in form

Hey there

Yes, you can do this, but you'll need to make some changes to the Get Options From Database setting in order to exclude the hidden ones.

Use the Get Options from MySQL Query (Advanced). Look at the following default query:

SELECT fieldname1, fieldname2
FROM `<?php echo $TABLE_PREFIX ?>tableName`

Fieldname1 = the field you had selected for option values

Fieldname2 = the field you had selected for option labels

TableName = the name of the table for this section

Once you've got those in, change it like so:

SELECT fieldname1, fieldname2
FROM `<?php echo $TABLE_PREFIX ?>tableName` WHERE hidden = 0

I can't tell if it's pending or hidden that's the name of the field, but hopefully you get the idea. Any records that are marked as hidden or pending will be excluded.

Let me know if this helps!

Claire,

Thank so much, that worked perfectly. Can I include an order by clause  so that the options in the drop down will display in the same drag/sort order that I have them in the category menu vs sorting them by id num?

Thanks!

April

Yes of course. It's a straightforward SQL statement so you can just add this to it:

ORDER BY dragSortOrder

Like this:

Feel like I am missing some syntax

SELECT fieldname1, fieldname2
FROM `<?php echo $TABLE_PREFIX ?>tableName` WHERE hidden = 0 ORDER BY dragSortOrder

Nope, looks good to me. Just make sure you change the fieldnames/tablename.

Claire,

I just noticed something. Need your help. This option works great but it is effecting the drop down in the CMS backend as well as on the form because of the advanced coding. I only want the excluded options to show on the form not in the backend. Can I just include some code on the actual form page to exclude options in the list only for form page and not in backend?

Ah I see, I thought you meant the CMS form, not a front end form.

You can use something similar on the front end to make this work as well. Can you show me the code you're using to get the options for the front end dropdown?

You'll have to change this. You can't use getListOptions with a custom WHERE clause. What's the name and structure of the table where you're storing the categories?