Often, one or more of your columns will be an ID that points to another table. For example, you might have a list of people and a list of cities they were born in. You would have a table of cities, and in the people table, a city field. Obviously, when you display a form for this, you want to show a pull-down menu of the available cities, not a blank field to type a city ID into.
Tell the plugin about these related (foreign) fields using:
<? $db->foreignKey('ID column', 'foreign title column'); ?>
As long as the ID column is the singular form of the foreign table (for example, the ID column is “state” and the foreign table is “states”) that is all you need. If your ID column doesn’t easily map to the table name, give the table name as a third option. For example:
<? $db->foreignKey('city', 'City', 'Cities'); ?>
If the foreign table is in a different database, add the database as a fourth parameter.
Getting related values
If you’ve used $db->foreignKey() to set up a related table based on a column in the current table, you can also get values from that table other than the title column.
<? $db->foreignValue(localColumn, foreignColumn); ?>
So, if you have set up, say, “city” in the current table to point to a table called “cities”, you can get the column called “state” from that table using:
<? $db->foreignValue('city', 'state'); ?>
Sometimes, your foreign keys go through a lookup table. This allows you to have more than one value on the foreign side matching a single entry on the local side. For example, artists might appear on more than one album. Use foreignKeys instead of foreignKey to specifiy a foreign key that goes through a lookup table to get to the foreign table.
You can set up a foreign key lookup table four ways.
<? $db->foreignKeys('foreign table', 'foreign title column'); $db->foreignKeys('foreign table', 'foreign title column', 'lookup table'); $db->foreignKeys('foreign table', 'foreign title column', 'lookup table', 'lookup table’s local ID column'); $db->foreignKeys('foreign table', 'foreign title column', 'lookup table', 'lookup table’s local ID column', 'lookup table’s foreign ID column'); ?>
That’s a mouthful, so let’s take it a piece at a time. For example, if you are matching artists to albums they appear in, you might have an ‘artists’ table, an ‘albums’ table, and an ‘artist_albums’ lookup table. Inside the albums table, the name of the album is ‘name’.
- The foreign table is the name of the table that ultimately contains the information you want. In the example, this is ‘albums’.
- The foreign title column is the column in the foreign table that provides the value for this foreign key. In the example, this is ‘name’.
- The lookup table is the name of the lookup table. In the example, this is ‘artist_albums’. If you leave this out, the system will use the local table (singular), an underscore, and the foreign table (unchanged).
- The lookup table’s local ID is the column in the lookup table that refers to the local table’s ID field. Leave it blank, and the system will assume the local table’s name (singular) and “ID”. In our example, the system will assume “artistID” for the local ID column.
- The lookup table’s foreign ID is the column in the lookup table that referes to the foreign table’s ID field. Leave it blank, and the system will assume the foreign table’s name (singular) and “ID”. In our example, the system will assume “albumID” for the foreign ID column.
You can only have one lookup per foreign table, because the system keeps track of each lookup using the foreign table’s name.
Sometimes you’ll want to restrict the choices on the form. You can use the foreignFilter() method to do this.
<? $stateRestriction = array('name'=>array('operator'=>'not', 'value'=>'Michigan')); $db->foreignFilter('state', $stateRestriction); ?>
The foreignFilter method uses the same format as restrictions do. The above example will remove any state named “Michigan” from the list of states.
More often, you’ll want to remove a list of values.
<? $invalidStates = array('Michigan', 'Massachusetts'); $stateRestriction = array('name'=>array('operator'=>'not', 'value'=>$invalidStates)); $db->foreignFilter('state', $stateRestriction); ?>
You can also use the before and after operators.
By default, they will be in the order of the title field. Sometimes you need the items to come up in a special order. For that, use the foreignOrder method.
<? $db->foreignOrder('state', 'rank'); ?>
You can also provide it with an array of column names.
Extra empty selects
When there are a lot of foreign keys, they will be presented as a series of select menus, including some empty ones for adding new foreign keys. The default is four extra empty selects. You can change this using the “extraSelects” method.
<? $db->extraSelects(2); ?>
The change will apply to all sets of select menus on your form.
Use checkboxes instead of pull-downs
If you want to have lots of checkboxes instead of pull-down menus, you can raise the checkbox limit using:
<? $db->checkboxLimit(40); ?>
for example, to raise the threshold where the system switches from checkboxes to selects to 40.
Pull-down choice for empty selects
The value of the empty select is always empty. The text displayed in the menu for the empty value can be set using something like $db->setEmptySelect('Please select...').
Changing the empty select’s displayed value will affect all select menus in your form.