public static function buildFetchSql(&$identHash)
{
//echo "build sql start\n";
// print_r($identHash);
$all_fields = static::$fields;
$use_fields = array();
foreach ($all_fields as $field_name) {
array_push($use_fields, Db_Linked::sanitizeFieldName($field_name));
}
$fetchSql = 'SELECT ' . implode(',', $use_fields) . ' FROM ' . static::$dbTable . ' WHERE 1=1';
$keys_to_remove = [];
$key_vals_to_add = [];
$param_keys_counters = [];
foreach ($identHash as $k => $v) {
if (is_array($v)) {
if (count($v) <= 0) {
trigger_error(Db_Linked::$ERR_MSG_BAD_SEARCH_PARAM, E_USER_ERROR);
return;
}
array_push($keys_to_remove, $k);
$fetchSql .= ' AND ' . Db_Linked::sanitizeFieldName($k) . ' IN (';
for ($i = 0, $numElts = count($v); $i < $numElts; $i++) {
$newKey = "__{$k}{$i}";
$key_use_counter = 1;
if (array_key_exists($newKey, $param_keys_counters)) {
$key_use_counter = $param_keys_counters[$newKey] + 1;
$newKey = $newKey . '__' . $key_use_counter;
}
$param_keys_counters["__{$k}{$i}"] = $key_use_counter;
$key_vals_to_add[$newKey] = $v[$i];
if ($i > 0) {
$fetchSql .= ',';
}
$fetchSql .= ":{$newKey}";
}
$fetchSql .= ')';
} else {
$k_parts = preg_split('/\\s+/', $k);
$num_k_parts = count($k_parts);
if ($num_k_parts == 1) {
# handle repeated use of same field in the query
$newKey = $k;
$key_use_counter = 1;
if (array_key_exists($newKey, $param_keys_counters)) {
$key_use_counter = $param_keys_counters[$newKey] + 1;
$newKey = $newKey . '__' . $key_use_counter;
$key_vals_to_add[$newKey] = $v;
array_push($keys_to_remove, $k);
}
$param_keys_counters[$k] = $key_use_counter;
$fetchSql .= ' AND ' . Db_Linked::sanitizeFieldName($k) . ' = :' . $newKey;
} else {
$k_comp = strtoupper(implode(' ', array_slice($k_parts, 1, $num_k_parts - 1)));
$valid_comps = ['<', '<=', '>', '>=', '!=', 'LIKE', 'NOT LIKE', 'IS NULL', 'IS NOT NULL'];
if (in_array($k_comp, $valid_comps)) {
array_push($keys_to_remove, $k);
if ($k_comp == 'IS NULL' || $k_comp == 'IS NOT NULL') {
$fetchSql .= ' AND ' . $k_parts[0] . ' ' . $k_comp;
} else {
$k_field = $k_parts[0];
$newKey = $k_field;
# handle repeated use of same field in the query
$key_use_counter = 1;
if (array_key_exists($newKey, $param_keys_counters)) {
$key_use_counter = $param_keys_counters[$newKey] + 1;
$newKey = $newKey . '__' . $key_use_counter;
$key_vals_to_add[$newKey] = $v;
array_push($keys_to_remove, $k);
}
$param_keys_counters[$k_field] = $key_use_counter;
$key_vals_to_add[$newKey] = $v;
$fetchSql .= ' AND ' . Db_Linked::sanitizeFieldName($k_field) . ' ' . $k_comp . ' :' . $newKey;
}
}
}
}
}
foreach ($keys_to_remove as $k) {
unset($identHash[$k]);
}
foreach ($key_vals_to_add as $k => $v) {
$identHash[$k] = $v;
}
$newIdent = [];
foreach ($identHash as $k => $v) {
$newIdent[":{$k}"] = $v;
}
$identHash = $newIdent;
//echo "build sql end\n";
// print_r($identHash);
return $fetchSql;
}
function testSanitizeFieldName()
{
$f = 'order';
$this->assertEqual('`order`', Db_Linked::sanitizeFieldName($f));
}
