Dynamic PHP Code creation based on MySQL Column Da

Question

I am developing a tool that creates PHP classes based on tables in my database. Most of the functionality is based on data from SHOW COLUMNS FROM $table. Each column typically relates to an instance variable in the new class being created.

What I have now is a situation where if a user passes in "twelve" for a variable related to a column of type INT, it doesn't fail, but the value in the database becomes 0.

What I would like is for the class to return false, identifying that the UPDATE or INSERT could not be completed.

What approach would you recommend for this?

What I'm trying now, is to construct an array of column names that contains each column that has a numeric data type (INT, FLOAT etc.). And when the user tries to set a variable, it checks to see if the variable is in this list and then checks the value being set to determine if it is a number.

PHP 5.3.3 using MySQLi

I'm assuming any column that isn't numeric will accept text data.

Answer 1

What are you are asking is called ORM. There are some good ORM libraries in PHP.

1. Doctrine
2. Propel
3. Outlet ORM
4. Leap ORM for Kohana ( I use it )
5. PHP Data Mapper

BTW, I answer your question.

If any invalid data is set you can throw an Exception.

See a sample.

class MyTable extends DbTable{
    function __set($var, $val){
        switch($var){
            case 'age': // age should be numeric type
                if(!is_numeric($val)){
                    throw new InvalidDataTypeException("$val of $var is not numeric");
                }
             /// do other operation here

             ... 
             ... // more cases
        }
    }
}

Answer 2

My approach would be for each field to create not only a variable for the value, but also one for the validation function.

Now have some static validation functions such as MyClass::ValidateInt(), MyClass::ValidateFloat() and friends, the validation function variable for an INT field would have MyClass::ValidateInt, call this while generating SQL for updating or inserting

Answer 3

UI advocates might say accept "twelve", and convert it to "12".

From a programming standpoint, if you must indicate failure outside of the current scope of the function, the typical way to handle this is to raise an exception and let the caller handle the situation.

Answer 4

If you want to validate the input, maybe you can integrate jQuery Validate plugin into your kernel class that creates the instance of the database.

For example, you have a column that is INT, with SHOW COLUMNS you can to stablish that this column must have a required decimal number so no one can to submit the form and then insert it to the database if is not correctly validate.

• info: http://bassistance.de/jquery-plugins/jquery-plugin-validation/