MySQL Cookbook, 2nd Edition

Using a Lookup Table to Validate Data

Problem

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

Solution

Issue statements to see whether the values are in the table. However, the way you do this depends on the number of input values and 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 Using Table Metadata to Validate Data for checking ENUM and SET columns. However, whereas ENUM and SET columns are limited to a maximum of 65,535 and 64 member values, respectively, a lookup table can have an essentially unlimited number of values. You might 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 statements

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 ...