You need to check values to make sure they’re listed in a lookup table.
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.
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
SET columns. However, whereas
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.
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 ...