Php Programming Code Examples
Php > Database Related Code Examples
Convert array vars for SQL statement (Postgres)
Convert array vars for SQL statement (Postgres)
<?php
function Get_DB_Values($db, $table, $fields) {
// Get Metadata
$meta = get_Metadata($db, $table);
if (!$meta) {
trigger_error('Get_DB_Values(): Failed to getting metada for '. $table, E_USER_WARNING);
return NULL;
}
// Check values
foreach($fields as $k => $v) {
if (!isset($meta[$k])) {
trigger_error('Get_DB_Values(): Non-existent field. Table: '. $table .'. Field: '.$k, E_USER_WARNING);
return NULL;
}
$v = trim($v);
// Value is NULL and default is defined, use DB default.
if ($v !== '' || !$meta[$k]['hasdefault']) {
switch ($meta[$k]['type']) {
case 'bool':
// Does not support numeric type. Must be string.
$v = strtolower($v);
if ($v === '') {
$ret[$k] = 'NULL';
}
elseif ($v == 't' || $v == 'true' || $v == 'y' || $v == 'yes' || $v == 'on') {
$ret[$k] = 'TRUE';
}
elseif ($v == 'f' || $v == 'false' || $v == 'n' || $v == 'no' || $v == 'off') {
$ret[$k] = 'FALSE';
}
else {
trigger_error('Get_DB_Values(): BOOLEAN type has invalid value. ', E_USER_WARNING);
return NULL;
}
break;
case 'cidr':
case 'inet':
if ($v === '') {
$ret[$k] = 'NULL';
}
// Check format - 10.10.10.10 or 10.10.10.10/32
elseif (!ereg('^([0-9]{1,3}\.){3}[0-9]{1,3}(/[0-9]{1,2}){0,1}$',$v)) {
trigger_error('Get_DB_Values(): INET/CIDR type has invalid format. ', E_USER_WARNING);
return NULL;
}
else {
$ret[$k] = "'$v'";
}
break;
case 'float':
case 'float8':
case 'numeric':
case 'money':
if ($v === '') {
$ret[$k] = 'NULL';
}
// Check format - Does not support '1.111E+10' notation.
elseif (!ereg('^[0-9]*\.[0-9]*$|^[0-9]*$', $v)) {
trigger_error('Get_DB_Values(): FLOAT/NUMERIC/MONEY type has invalid value. ', E_USER_WARNING);
return NULL;
}
else {
$ret[$k] = $v;
}
break;
case 'interval': // Only allow INTEGER for interval
case 'int':
case 'int2':
case 'int4':
case 'int8':
if ($v === '') {
$ret[$k] = 'NULL';
}
// Check format
elseif (!ereg('^[0-9]*$',$v)) {
trigger_error('Get_DB_Values(): INTEGER/INTERVAL type has invalid value. ', E_USER_WARNING);
return NULL;
}
else {
$ret[$k] = $v;
}
break;
case 'bpchar': // char type
case 'char':
case 'text':
case 'varchar':
// NO length check - char/varchar will be truncated by DB if it's too long.
$ret[$k] = ($v !== '') ? "'".addslashes($v)."'" : 'NULL';
break;
case 'timestamp':
case 'datetime':
case 'date':
// Check format - "2001/02/11 14:59:01", "2001/4/1 15:10" or "2001/11/15".
if ($v === '') {
$ret[$k] = 'NULL';
}
elseif (!ereg('^([0-9]{4}[/-][0-9]{1,2}[/-][0-9]{1,2})([ \t]+(([0-9]{1,2}:[0-9]{1,2}){1}(:[0-9]{1,2}){0,1})){0,1}$',$v, $regs)) {
trigger_error('Get_DB_Values(): DATE/TIMESTAMP type has invalid value. ', E_USER_WARNING);
return NULL;
}
else {
$ret[$k] = "'{$regs[1]} {$regs[3]}'";
}
break;
case 'oid': // OID type should not be INSERTed nor UPDATEd. (Add oid type if you use oid for BLOD)
case 'serial': // Serial type should not be INSERTed nor UPDATEd. So treat as invalid type.
default:
trigger_error('Get_DB_Values(): INVALID datatype ', E_USER_WARNING);
return NULL;
break;
}
}
}
return $ret;
}
// Return matadata
// return NULL if error
// This is the same function
function Get_MetaData($db, $table) {
$rows = 0; // Number of rows
$qid = NULL; // Query result resource
$meta = array(); // Metadata array - return value
// Get catalog data from system tables.
$query=<<<EOQ
SELECT
a.attname,
a.attnum,
t.typname,
a.attlen,
a.atttypmod,
a.attnotNULL,
a.atthasdef
FROM
pg_class as c,
pg_attribute a,
pg_type t
WHERE
a.attnum > 0 AND
a.attrelid = c.oid AND
c.relname = '$table' AND
a.atttypid = t.oid
ORDER BY
a.attnum;
EOQ;
$qid = pg_Exec($db, $query);
// Check error
if (!is_resource($qid)) {
trigger_error('Get_Metadata(): metadata query fialed ', E_USER_WARNING);
return NULL;
}
$rows = pg_NumRows($qid);
// Store meta data
for ($i = 0; $i < $rows; $i++) {
$field_name = pg_Result($qid,$i,0); // Field Name
$meta[$field_name]['id'] = pg_Result($qid,$i,1); // Attrbute ID
$meta[$field_name]['type'] = pg_Result($qid,$i,2); // Data type name
$meta[$field_name]['len'] = pg_Result($qid,$i,3); // Length: -1 for variable length
$meta[$field_name]['modifier'] = pg_Result($qid,$i,4); // Modifier
$meta[$field_name]['notnull'] = (pg_Result($qid,$i,5) === 't' ? TRUE : FALSE); // Not NULL?
$meta[$field_name]['hasdefault'] = (pg_Result($qid,$i,6) === 't' ? TRUE : FALSE); // Has default value?
}
pg_FreeResult($qid);
return $meta;
}
?>
