PHP自动验证MySql 数据表字段
变量自行替换,$this-> 自行删除
/**
* 数据对比
* @param type $param 数据
* @param type $table 表名
* @return boolean false|需要数据
*/
function data_contrast($param, $table) {
//获取数据
$columns = $this->mysql_columns($table);
$data = array();
foreach ($param as $key => $val) {
foreach ($columns as $k => $v) {
if ($v['column_name'] == $key) {
//是否允许为空
$strlen = strlen($val);
if ($v['null_able'] == 'NO' && $strlen == 0) {
return false;
}
//判断数据类型
switch ($v['data_type']) {
case 'int':
if ($val + 1 - 1 != $val)
return false;
break;
case 'tinyint':
if ($strlen > $v['num_length'])
return false;
default:break;
}
$columns[$k] = null;
unset($columns[$k]);
$data[$key] = $val;
$param[$key] = null;
unset($param[$key]);
}
}
}
foreach ($columns as $key => $val) {
if ($val['null_able'] == 'NO' && $val['column_name'] != 'id') {
return false;
}
if ($val['column_name'] == 'id') {
$data['addtime'] = time();
$data['uptime'] = time();
}
}
if (!empty($data['id']))
$data['uptime'] = time();
return $data;
}
/**
* 获取数据库某个结构
* @param type $table 表名
* @param type $database 数据库名称
* @param type $prefix 表前缀
* @return type 表结构
*/
function mysql_columns($table, $database = '', $prefix = '') {
empty($database) and $database = config('database.database');
empty($prefix) and $prefix = config('database.prefix');
if (empty($table))
return [];
$sql = "SELECT TABLE_NAME AS 'table_name',COLUMN_NAME AS 'column_name',"
. "COLUMN_COMMENT AS 'column_comment',IS_NULLABLE AS 'null_able',"
. "DATA_TYPE AS 'data_type',CHARACTER_MAXIMUM_LENGTH AS 'str_length',"
. "NUMERIC_PRECISION AS 'num_length',NUMERIC_SCALE AS 'num_bit' "
. "FROM information_schema.`COLUMNS` " . "WHERE TABLE_SCHEMA = '" . $database
. "'AND TABLE_NAME = '" . $prefix . $table . "' "
. " ORDER BY TABLE_NAME, ORDINAL_POSITION;";
$columns = Db::query($sql);
return $columns;
}