Changes are made at your own risk and are not guaranteed to work. Always make backups of the database and files prior to implementing hacks. Make a special backup of the file and/or template you are modifying so that you can roll back quickly.
Hacks are dangerous and can cause your site to stop working. They make later updates to ExpressionEngine more difficult; you should track your hacks for post-update re-implementation.
Most hacks are unnecessary, please review the Development Documentation for information on expanding ExpressionEngine via Modules, Extensions, and Plugins.
Hacks are never officially supported.
Overview
This hack extends the “Drop-down List” type of custom field, allowing you to dynamically populate your “Select Options” from an SQL statement.
Have you ever wanted to have a Custom Field that allows you to pick from a dynamic list, such as Site Members, member groups, categories or even other entries?
Normally, this would require manually updating the “Select Options” for a drop-down list Custom Field. But by implementing this hack, you can create a SQL statement that will dynamically generate the drop-down list whenever an entry is created or edited.
This hack does not interfere with the working of regular drop-down lists, so should not cause any problems with existing Custom Fields.
Written for, and tested with EE 1.3.1
Installing the Hack
Note: in all file references, I’ve assumed the default /system folder. If you’ve changed the name of your /system folder, update the paths as necessary.
* Locate the file: /system/cp/cp.publish.php
* Save a backup of this file, then open the original.
* Locate the following text (around line 2250):
//--------------------------------
// Drop-down lists
//--------------------------------
* A few lines into this routine, replace this code:
foreach (explode("\n", trim($row['field_list_items'])) as $v)
{
$v = trim($v);
$selected = ($v == $field_data) ? 1 : '';
$r .= $DSP->input_select_option($v, $v, $selected);
}
* with this code:
// SQL_Dropdown_Mod Begins
$sqlOpts = explode("\n", trim($row['field_list_items']));
$sqlEnabled = (strtolower(trim($sqlOpts[0])) == 'selectfromquery') ? true : false ;
if ($sqlEnabled)
{
$ss = array_shift($sqlOpts); // discard 'selectfromquery' line
$ss = explode("=", $sqlOpts[0]);
if (strtolower(trim($ss[0])) == 'prompt')
{
$selected = ($field_data == "") ? 1 : "";
$r .= $DSP->input_select_option("", trim($ss[1]), $selected);
$ss = array_shift($sqlOpts); // discard 'prompt= ' line
}
$ss = implode(" ", $sqlOpts); // implode remaining lines back into a string of SQL
$query = $DB->query($ss);
if ($query->num_rows > 0)
{
foreach ($query->result as $opt)
{
$keys = array_keys($opt);
$v = trim($opt[$keys[0]]);
if (count($opt) > 1)
{
$t = trim($opt[$keys[1]]);
}
else
{
$t = $v;
}
$selected = ($v == $field_data) ? 1 : "";
$r .= $DSP->input_select_option($v, $t, $selected);
}
}
}
else // regular dropdown list...
{
foreach (explode("\n", trim($row['field_list_items'])) as $v)
{
$v = trim($v);
$selected = ($v == $field_data) ? 1 : "";
$r .= $DSP->input_select_option($v, $v, $selected);
}
}
// SQL_Dropdown_Mod Ends
* Save your changes to cp.publish.php.
Using the Hack
Using the code word “selectFromQuery” in the first line of your Select Options activates the hack.
* Open the Edit Field form for the Custom Field you want to make dynamic.
* Make sure the Field Type is: Drop-down List
* Make the first line of the Select Options list: selectFromQuery
Now the remainder of the Select Options field will be interpreted as a SQL statement.
* The SQL statement can break across as many lines as you like.
* The first column returned will be the field value in the dropdown list.
* The second column returned will be the label shown in the dropdown list.
* if there is no second column, the first column will be repeated as the label.
Examples
Enter the following into the Select Options of a dropdown list:
selectFromQuery
SELECT member_id, username
FROM exp_members
You should now have a drop-down list of site members.
If you wanted to restrict the list to members in the Admin group, you could write a slightly more complex query:
selectFromQuery
SELECT member_id, username
FROM exp_members
WHERE group_id = 1
Obviously, this is a trivial example; the SQL can be as complex as you like!
Additional Options
You can optionally enter a prompt for the first line of your drop-down list. The prompt option has a value of ‘’ (empty string), so is useful when you do not want a default option.
To implement this, enter the following as the second line in your Select Options: prompt = text
Example
selectFromQuery
prompt = Select an Administrator...
SELECT member_id, username
FROM exp_members
WHERE group_id = 1
More Examples
If you’re not a SQL guru, try out these useful examples.
Select a category in a specific category group
In this example, the Category Group ID = 1
selectFromQuery
SELECT cat_id, cat_name
FROM exp_categories
WHERE group_id = 1
Select a sub-category of another category
In this example, the Category ID of the Parent category = 3
selectFromQuery
SELECT cat_id, cat_name
FROM exp_categories
WHERE parent_id = 3
Select a gallery image
selectFromQuery
prompt = Select Gallery image...
SELECT entry_id, title
FROM exp_gallery_entries
Feel free to contribute more…
——
If you have any comments or questions about this hack, feel free to email me: che AT shift DOT co DOT nz
Category:Tricks Category:Hacks