/**
* Converts a filter specific name/value field pair into SQL
*
* The filter array has a special syntax for field names which can be used to control
* how the name/value pair is constructed in the WHERE clause. For example, it
* is possible to specify a LIKE, NOT LIKE, =, >, >=, <, <=, !=, IS NULL, or IS NOT NULL
* format using this function.
*
* Valid field name syntax characters:
*
* =field field = 'value'
* >field field > 'value'
* >=field field >= 'value'
* <field field < 'value'
* <=field field <= 'value'
* !=field field != 'value'
* %field field like '%%value%%'
* !%field field not like '%%value%%'
* *field field is null
* !*field field is not null
*
* @param string $field_name the name of the field optionally including a special syntax
* character specified above
* @param string $field_value the value of the field
* @return string the name/value pair in SQL format which can placed within a WHERE clause
*/
function sql_filter_get_sql_field_component($field_name, $field_type, $field_value)
{
/* WHERE field = 'value' */
if (substr($field_name, 0, 1) == "=")
{
$field_component = substr($field_name, 1) . " = " . sql_get_quoted_string($field_type, $field_value);
}
/* WHERE field >= 'value' */
elseif (substr($field_name, 0, 2) == ">=")
{
$field_component = substr($field_name, 2) . " >= " . sql_get_quoted_string($field_type, $field_value);
}
/* WHERE field > 'value' */
elseif (substr($field_name, 0, 1) == ">")
{
$field_component = substr($field_name, 1) . " > " . sql_get_quoted_string($field_type, $field_value);
}
/* WHERE field >= 'value' */
elseif (substr($field_name, 0, 2) == "<=")
{
$field_component = substr($field_name, 2) . " <= " . sql_get_quoted_string($field_type, $field_value);
}
/* WHERE field < 'value' */
elseif (substr($field_name, 0, 1) == "<")
{
$field_component = substr($field_name, 1) . " < " . sql_get_quoted_string($field_type, $field_value);
}
/* WHERE field != 'value' */
elseif (substr($field_name, 0, 2) == "!=")
{
$field_component = substr($field_name, 2) . " != " . sql_get_quoted_string($field_type, $field_value);
}
/* WHERE field like '%%value%%' */
elseif (substr($field_name, 0, 1) == "%")
{
$field_component = substr($field_name, 1) . " like " . sql_get_quoted_string($field_type, "%%$field_value%%");
}
/* WHERE field not like '%%value%%' */
elseif (substr($field_name, 0, 2) == "!%")
{
$field_component = substr($field_name, 2) . " not like " . sql_get_quoted_string($field_type, "%%$field_value%%");
}
/* WHERE field is null */
elseif (substr($field_name, 0, 1) == "*")
{
$field_component = substr($field_name, 1) . " is null";
}
/* WHERE field is not null */
elseif (substr($field_name, 0, 2) == "!*")
{
$field_component = substr($field_name, 2) . " is not null";
}
/* WHERE field like '%%value%%' (default) */
else
{
$field_component = $field_name . " like " . sql_get_quoted_string($field_type, "%%$field_value%%");
}
return $field_component;
}
function db_delete($table_name, $fields) {
/* generate a WHERE statement that reflects the list of keys */
$sql_key_where = ""; $i = 0;
if (sizeof($fields) > 0) {
foreach ($fields as $db_field_name => $db_field_array) {
$sql_key_where .= ($i == 0 ? "WHERE " : " AND ") . $db_field_name . " = " . sql_get_quoted_string($db_field_array["type"], $db_field_array["value"]);
$i++;
}
}
$sql = "DELETE FROM $table_name $sql_key_where";
/* execute the sql statement and return the result */
if (db_execute($sql)) {
return true;
}else{
return false;
}
}
