Tired with adding quotes around vars for SQL query? Do you want to make sure supplied values are valid for fields? Do you want to use database default value, if user send no values and default value is defined for the field? Then this function might be useful.
By : yohgaki
<?php
/*
* Description:
* array get_db_values(resource db_link_id, string table_name, array fields)
*
* Parameters:
* db_linkid PostgreSQL link resource
* table_name table name - needed to get metadata.
* fields array(<field name> => <user input>) All field name should much database field name.
*
* Return Value:
* array(<field name> => <value for INSERT/UPDATE>) Return NULL if error.
*
* It converts assoc array data so that values are sutable for INSERT/UPDATE
* SQL statements. (WHERE clause also)
* All array key should match database field names. Return NULL if error.
*
* It checks if there is a default field value in the table definition. If there
* is, it does not return those fields.
* If there is no input (null string for field), it set NULL for the field.
* If quote is required, add slashes and put quotes.
*
* Format checks are limited. Complex format checks should be done
* by other functions/class other than this function.
* (For example, HTML FORM class)
*
* Not all PostgreSQL data types/formats are supported.
* OID is treated as invalid. You need to modify to allow if you want to
* update/insert OID type.
*
* Function name sounds odd to me. Anyone have better name for it?
* email: yohgaki@hotmail.com
*
*/
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
// http://www.zend.com/codex.php?id=481&single=1
function Get_MetaData($db, $table) {
$rows = 0; // Number of rows
$qid = NULL; // Query result resource
$meta = array(); // Metadata array - return value
// See PostgreSQL developer manual (www.postgresql.org) for system table spec.
// 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;
}
?>
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
More Database Code Articles
More By Codewalkers
 developerWorks - FREE Tools!
|
Achieving true agility is a never-ending effort. We will showcase how you can become agile incrementally, a few practices at the time.Which practices should any agile team strive to adopt? What additional practices should you consider based on your needs to scale? Adopting practices are however made much easier with the right tool support. What about if your tools adapt to your practices? We will take a look at how the Jazz technology can be leveraged to make your process change the behavior of your tools.
FREE! Go There Now!
|
|
|
|
You probably have thousands of lines of COBOL code loaded with business intelligence and being used to run your business, along with an army of developers maintaining these applications. Learn how to prepare your applications and developers so you can keep that competitive edge and move to a service-oriented architecture with the IBM Rational Enterprise Modernization solutions. Replay is available for 9 months.
FREE! Go There Now!
|
|
|
|
CakePHP is a stable production-ready, rapid-development aid for building Web sites in PHP. This "Cook up Web sites fast with CakePHP" series shows you how to build an online product catalog using CakePHP.
FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to download a free trial version of WebSphere Business Modeler Advanced V6.1.1, IBM’s premier business process modeling and analysis tool for business users that offers process modeling, simulation, and analysis capabilities. IBM WebSphere Business Modeler helps you visualize, understand, and document business processes for continuous improvement.
FREE! Go There Now!
|
|
|
|
Manage, govern, and share services across your organization by using WebSphere Service Registry and Repository. Follow the hands-on exercises to learn how to navigate the Web interface to publish, find, reuse, and update services.
FREE! Go There Now!
|
|
|
|
XML has become a common way of storing business data as flat files and many data server vendors including IBM have provided ways to store this data within relational database systems. Increasingly collections of XML files are accessed like databases using an xQuery and other XML standard mechanisms. Businesses find the need to combine the traditional tabular structured data with XML formatted data. In this webcast, you’ll learn about IBM’s WebSphere Federation Server technology, which provides users with the ability to integrate these two data formats.
FREE! Go There Now!
|
|
|
|
Regression testing -- in which code is thoroughly tested to ensure that changes have not produced unexpected results -- is an important part of any development process. But many testing environments neglect the terminal-based applications that still form the backbone of many industries. In this tutorial, you'll learn how the Rational Functional Tester Extension for Terminal-Based Applications works with other Rational Functional Tester to help test terminal-based applications quickly and easily.
FREE! Go There Now!
|
|
|
|
Get a free trial download of the latest version of IBM Rational Performance Tester V7.0.1, a load and performance testing solution for teams concerned about the scalability of their Web-based applications. Combining multiple ease-of-use features with granular detail, Rational Performance Tester simplifies the test-creation, load-generation and data-collection processes that help teams ensure the ability of their applications to accommodate required user loads.
FREE! Go There Now!
|
|
|
|
You can now evaluate IBM Rational Asset Manager V7.0 online without installing or configuring it on your own system! Rational Asset Manager helps create, modify, govern, find, and reuse any type of development assets, including SOA and systems development assets. Rational Asset Manager helps you reduce software development costs and improve quality by facilitating the reuse of all types of software development-related assets. Visit developerWorks to learn more about this product and register to explore its capabilities online.
FREE! Go There Now!
|
|
|
|
With IBM Rational Systems Development Solution, you can deliver products faster with higher quality. Within this kit, Read the “Model Driven Systems Development” white paper to see how to improve product quality and communication. Then check out the rest of the e-Kit to learn more about important topics that can affect the success of any software project through customer examples, tutorials, informative Webcasts, and best practices for designing, building and managing systems. From start to finish, at every stage in your projects, Rational Systems Development Solution can help your company reach its full potential.
FREE! Go There Now!
|
|
|
|
All FREE IBM® developerWorks Tools!
|
