Drop Shadow

Matching to a lookup table

If you need an unrestricted lookup to a value in this or another table, use “match(fieldname, fieldvalue, tablename)”. If you have a table called 'individuals' with information about people stored by MySanDiego account name, you can get that row with:

<? $accountInfo = $db->match('username', $authenticatedUser, 'individuals'); $accountInfo = $db->matchAll('username', $authenticatedUser, 'individuals'); ?>

The first one (match) will always return one row. If more than one row matches, it will choose one row arbitrarily. In general, “match” should not be used on a field in a table that will match more than one row.

The second one (matchAll) will return all rows that match; it will return an array of matches, even if only one matches.

If the table is in a different database, you can provide that database name as a fourth option.

These functions will never affect the main search.

A match list for selects

If you want all the rows that match for use in a select statement, use “matches” instead of “match”. You’ll also need to give it the field to use for the title.

<? $choices = $db->matches('type', 'Intramural', 'class', 'classes'); $db->setFieldChoices('intramural', $choices); $db->getFormHTML('intramural'); ?>

Use “matches(fieldname, fieldvalue, titlefield, tablename)”. The result will be a list for each matching row, with the ID as the first item in the list and the title as the second item.

Complex match lists for selects

Like the above, but you provide the WHERE portion of the query. The syntax is “matchWhere('title field', 'WHERE statement', 'table name')”.

<? $where = 'live="yes" AND semester="spring"'; $choices = $db->matchWhere('class', $where, 'classes'); $db->setFieldChoices('intramural', $choices); $db->getFormHTML('intramural'); ?>

Finally, if you need a really complex lookup, use matchQuery(). The syntax is “matchQuery('id field', 'title field', 'from portion', 'where portion', 'order portion')”. The “where” portion of the query is optional. For example, you might have something like this to get the state as well as the city in a pull-down menu:

<? $title = 'CONCAT(city.name, ", ", state.name)'; $from = 'city JOIN state ON city.state=state.ID'; $choices = $db->matchQuery('city.ID', $title, $from); $db->setFieldChoices('city', $choices); $db->getFormHTML('city'); ?>