Validation library for PHP/mysql

Question

Hi!

Is there any lightweight validation library available for PHP that easily can check if a specific string or value is valid for a known database type -

Something like this:

 if (is_MEDIUMINT($var)) {
      $this->db->insert($anothervar);
 }

Thanks!

Answer 1

Not as far as I know. You could, of course, create your own functions or class to do this, based on the rules of your specific database type.

Sorry I can't be more help. (And happy to be educated by any other users if there is a class/functions out there.)

Answer 2

This isn't as simple as it seems, your is_MEDIUMINT() function could easily become:

is_MEDIUMINT()
is_MEDIUMINT_NULL()
is_MEDIUMINT_NOTNULL()
is_MEDIUMINT_UNSIGNED()
is_MEDIUMINT_UNSIGNED_NULL()
is_MEDIUMINT_UNSIGNED_NOTNULL()

Then you run into the problem of different databases types, SQLite for instance has only one INT type while MySQL has at least 5 (TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT), not counting aliases (such as INTEGER, BOOL, BOOLEAN and SERIAL) and float types - which would be even harder to implement due to the variable precision argument. Bare in mind that I'm still ignoring several crucial features such as UNIQUE and Foreign Key Constrains which could only be validated on the DB.

I don't understand why you think such functions would be useful, because if you could set up your database to work in strict mode and then simply try to insert the values, if the query fails you know something is wrong, quoting the MySQL Manual:

In nonstrict mode, when an out-of-range value is assigned to an integer column, MySQL stores the value representing the corresponding endpoint of the column data type range. If you store 256 into a TINYINT or TINYINT UNSIGNED column, MySQL stores 127 or 255, respectively.

What you be the point of validating the value prior to insertion anyway?

if (is_MEDIUMINT($var)) {
  $this->db->insert($anothervar);
}

else {
  // do what?
}

If you're trying to avoid errors run the query in a transaction or use the INSERT OR IGNORE syntax.

Answer 3

Are you just looking for the is_* functions in PHP?

is_integer, is_float etc. ?

There is also get_type, but it shouldn't be used for type checking

Answer 4

The INFORMATION_SCHEMA database is part of the ANSI 2003 specification, so you could use that across any DB vendor that supports it (MySQL, Postgresql, SQLite, MSSQL 2k5+).

/*public static*/ function is_dbtype($table, $column, $type) {
    $db = (...)::getInstance(); // assuming PDO
    $sql = 'SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS '.
        'WHERE TABLE_NAME = :table AND COLUMN_NAME = :column';
    $st = $db->prepare($sql);
    $st->execute(array(':table' => $table, ':column' => $column));
    return ($type == $st->fetchColumn());
}

Change COLUMN_TYPE to DATA_TYPE if you just want "varchar" instead of "varchar(64)". If you need more, there's plenty: IS_NULLABLE, NUMERIC_PRECISION, CHARACTER_SET_NAME, etc.

(Not sure I'd use personally use this though, the is_* functions usually do enough without an extra database call. More importantly, info_schema holds the structure of every database on the server, so granting read permissions to it might (should) be a big deal. If you're on a shared host you likely won't have access to it at all.)

MySQL-only alternate: do similar but with DESCRIBE [table]. It's pretty explicit though, you'll have to fish out the "bigint" in "bigint(21) unsigned" yourself if that's all you want.

Answer 5

Throw it into the database and see if it returns errors. If it doesn't, you types are good enough.

This also means that the dbms you are using will handle the valiation, which means you don't have to update all your validation functions when they decide to change theirs on a whim. And you will most likely not notice this until everything dies and you can't work out why. The less code you have to maintain yourself, the easier your life is :)