MySQL Cookbook

Validation Using a Lookup Table

Problem

You need to check values to make sure they’re listed in a lookup table.

Solution

Issue queries to see if the values are in the table. But the way you do this depends on the number of input values and on the size of the table.

Discussion

To validate input values against the contents of a lookup table, you can use techniques somewhat similar to those shown in Recipe 10.28 on checking ENUM and SET columns. However, whereas ENUM and SET columns are limited to a maximum of 65,536 and 64 member values respectively, a lookup table can have an essentially unlimited number of values. You may not want to read them all into memory.

Validation of input values against the contents of a lookup table can be done several ways, as illustrated in the following discussion. The tests shown in the examples perform comparisons against values exactly as they are stored in the lookup table. To perform case-insensitive comparisons, remember to convert all values to a consistent lettercase.

Issue Individual Queries

For one-shot operations, you can test a value by checking whether it’s listed in the lookup table. The following query returns true (nonzero) a value that is present and false otherwise:

$valid = $dbh->selectrow_array (
                "SELECT COUNT(*) FROM $tbl_name WHERE val = ?",
                undef, $val);

This kind of test may be suitable for purposes such as checking a value submitted in a web form, but is inefficient for validating large datasets. It has no memory for the results of ...