function generate_search_sql($_SEARCH_INPUT = null)
{
global $label;
// from the languages file.
if (!is_array($_SEARCH_INPUT)) {
$_SEARCH_INPUT = $_REQUEST;
// get the search input that was posted
}
JBPLUG_do_callback('generate_search_sql_before', $where_sql, $this->form_id, $_SEARCH_INPUT);
if ($where_sql) {
return $where_sql;
}
// $where_sql was generated by a plugin
if ($_SEARCH_INPUT['action'] == 'search') {
foreach ($this->tag_to_search as $key => $val) {
$name = $this->tag_to_search[$key]['field_id'];
switch ($this->tag_to_search[$key]['field_type']) {
case 'IMAGE':
case 'FILE':
case 'YOUTUBE':
if ($_SEARCH_INPUT[$name] != '') {
$where_sql .= " AND (`" . $name . "`) != '' ";
}
break;
case 'SELECT':
if ($_SEARCH_INPUT[$name] != '') {
$where_sql .= " AND (\t`" . $name . "` = '" . JB_escape_sql($_SEARCH_INPUT[$name]) . "') ";
}
break;
case 'CHECK':
$tmp = '';
$comma = '';
## process all possible options
$sql = "SELECT * from codes where field_id='" . JB_escape_sql($name) . "' ";
$code_result = JB_mysql_query($sql) or die(mysql_error());
$i = 0;
while ($code = mysql_fetch_array($code_result, MYSQL_ASSOC)) {
$val = $code['field_id'] . "-" . $code['code'];
if ($_SEARCH_INPUT[$val] != '') {
if ($i > 0) {
$comma = 'OR';
}
$tmp .= $comma . " `{$name}` LIKE '%" . JB_escape_sql($code['code']) . "%' ";
$i++;
}
}
if ($i > 0) {
$where_sql .= " AND (" . $tmp . ") ";
}
break;
case 'MSELECT':
$tmp = '';
$comma = '';
$selected_codes = array();
$selected_codes = $_SEARCH_INPUT[$name];
for ($i = 0; $i < sizeof($selected_codes); $i++) {
if ($i > 0) {
$comma = 'OR';
}
$tmp .= $comma . " `{$name}` LIKE '%" . JB_escape_sql($selected_codes[$i]) . "%' ";
}
if ($i > 0) {
$where_sql .= " AND (" . $tmp . ") ";
}
break;
case 'CATEGORY':
$where_range = '';
$range_or = '';
//$_SEARCH_INPUT[$name] can either be an array of numbers & string 'all',
// or a scalar string all or scalar number
if (!is_array($_SEARCH_INPUT[$name]) && trim($_SEARCH_INPUT[$name]) == '') {
break;
}
// init the $search-set & $cat_ids_str as strings
// similar to: JB_search_category_tree_for_posts()
$search_set = '';
if (is_array($_SEARCH_INPUT[$name])) {
// if the category is a multiple select!
foreach ($_SEARCH_INPUT[$name] as $key => $val) {
if (!is_numeric($val) && $val != 'all') {
// validate
break;
}
}
$cat_ids_str = implode(',', $_SEARCH_INPUT[$name]);
} else {
$cat_ids_str = (int) $_SEARCH_INPUT[$name];
}
if (strpos($cat_ids_str, 'all') !== false) {
// return all categories
break;
// no need to filter
}
$sql = "SELECT search_set FROM categories WHERE category_id IN(" . jb_escape_sql($cat_ids_str) . ") ";
$result2 = JB_mysql_query($sql) or die(mysql_error());
$search_set = $cat_ids_str;
// search_set does not include the current category
while ($row2 = mysql_fetch_row($result2)) {
$search_set .= ',' . $row2[0];
}
// optimize the search set: remove duplicates & range it
$set = explode(',', $search_set);
sort($set, SORT_NUMERIC);
$prev = '';
// this removes duplicates
foreach ($set as $key => $val) {
if ($val == $prev) {
unset($set[$key]);
}
$prev = $val;
}
// sort again because after removing
// duplicates the keys were like swiss cheeze
sort($set, SORT_NUMERIC);
// Now this is the fun part!
// The code below summarizes the $set array
// which is a list of numbers in to rangers
for ($i = 0; $i < sizeof($set); $i++) {
$start = $set[$i];
// 6
//$end = $set[$i];
for ($j = $i + 1; $j < sizeof($set); $j++) {
// advance the array index $j if the sequnce
// is +1
if ($set[$j - 1] != $set[$j] - 1) {
// is it in sequence
$end = $set[$j - 1];
break;
}
$i++;
$end = $set[$i];
}
if ($end == '') {
$end = $set[$i];
}
if ($start != $end && $end != '') {
$where_range .= " {$range_or} ((`" . $name . "` >= {$start}) AND (`" . $name . "` <= {$end})) ";
} elseif ($start != '') {
$where_range .= " {$range_or} (`" . $name . "` = {$start} ) ";
}
$start = '';
$end = '';
$range_or = "OR";
}
$where_sql .= " AND ({$where_range}) ";
break;
case 'SKILL_MATRIX':
if (trim($_SEARCH_INPUT[$name . 'name']) != '') {
if (!is_numeric($_SEARCH_INPUT[$name . 'rating'])) {
$_SEARCH_INPUT[$name . 'rating'] = '0';
}
if (!is_numeric($_SEARCH_INPUT[$name . 'years'])) {
$_SEARCH_INPUT[$name . 'years'] = '0';
}
$where_sql .= " AND t2.name LIKE '" . JB_escape_sql(trim($_SEARCH_INPUT[$name . 'name'])) . "' AND t2.years >= " . JB_escape_sql($_SEARCH_INPUT[$name . 'years']) . " AND t2.rating >= " . JB_escape_sql($_SEARCH_INPUT[$name . 'rating']) . " ";
}
break;
case 'DATE':
$day = $_REQUEST[$name . "d"];
$month = $_REQUEST[$name . "m"];
$year = $_REQUEST[$name . "y"];
if ($year != '' && $month != '' && $day != '') {
// convert to ISO format
$value = "{$year}-{$month}-{$day}";
$where_sql .= " AND (`{$name}` >= '" . JB_escape_sql($value) . "') ";
}
break;
case 'DATE_CAL':
$value = $_SEARCH_INPUT[$name];
if ($value != '') {
// convert to ISO format before putting it through a search
$value = JB_SCWDate_to_ISODate($value);
$where_sql .= " AND (`{$name}` >= '" . JB_escape_sql($value) . " 00:00:00') ";
}
break;
case 'TIME':
$value = $_SEARCH_INPUT[$name];
$time = strtotime($value);
// gmt
$time = $time - 3600 * JB_GMT_DIF;
$later_time = $time + 3600 * 24;
// 24 hours later
$where_sql .= " AND ( \n\t\t\t\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t\t\t\t`{$name}` > '" . gmdate("Y-m-d H:i:s", $time) . "'\n\t\t\t\t\t\t\t\t\t\t\t\t) \n\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t\t\t\t`{$name}` < '" . gmdate("Y-m-d H:i:s", $later_time) . "'\n\t\t\t\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\t\t\t)\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t ";
break;
default:
$custom_sql = '';
$value = $_SEARCH_INPUT[$name];
JBPLUG_do_callback('generate_search_sql', $custom_sql, $this->tag_to_search[$key], $value);
if ($custom_sql != '') {
$where_sql .= $custom_sql;
} else {
if ($value != '') {
$list = preg_split("/[\\s,]+/", $value);
for ($i = 1; $i < sizeof($list); $i++) {
$or .= " AND (`{$name}` like '%" . JB_escape_sql($list[$i]) . "%') ";
}
$where_sql .= " AND ((`{$name}` like '%" . JB_escape_sql($list[0]) . "%') {$or})";
}
}
break;
}
// end switch
}
// end foreach
}
// end serach
JBPLUG_do_callback('generate_search_sql_after', $where_sql, $this->form_id, $_SEARCH_INPUT);
return $where_sql;
}
function get_sql_update_values($table_name, $primary_key_name, $primary_key_id, $user_id, &$assign)
{
$fields =& JB_schema_get_static_fields($this->form_id, JB_DB_MAP);
foreach ($fields as $field) {
if ($field['field_type'] == 'ID') {
continue;
// do not update the id
}
if (isset($assign[$field['field_id']])) {
$str .= "{$comma} `" . $field['field_id'] . "` = '" . JB_escape_sql($assign[$field['field_id']]) . "' ";
$comma = ',';
}
}
foreach ($this->tag_to_field_id as $tag => $field) {
if (!is_numeric($field['field_id']) || $field['field_type'] == 'BLANK' || $field['field_type'] == 'SEPERATOR' || $field['field_type'] == 'NOTE') {
continue;
}
$tmp = '';
$comma = '';
switch ($field['field_type']) {
case 'GMAP':
$str .= ", `" . $field['field_id'] . "_lat` = '" . JB_escape_sql($_REQUEST[$field['field_id'] . '_lat']) . "', `" . $field['field_id'] . "_lng` = '" . JB_escape_sql($_REQUEST[$field['field_id'] . '_lng']) . "', `" . $field['field_id'] . "` = '" . JB_escape_sql($_REQUEST[$field['field_id'] . '_zoom']) . "' ";
break;
case 'IMAGE':
if ($_FILES[$field['field_id']]['name'] != '') {
$_REQUEST[$field['field_id']] = $file_name;
// delete the old image
if ($primary_key_id != '') {
JB_delete_image_from_field_id($table_name, $primary_key_name, $primary_key_id, $field['field_id']);
}
$file_name = JB_saveImage($field['field_id'], $user_id);
$str .= ", `" . $field['field_id'] . "` = '" . JB_escape_sql($file_name) . "' ";
}
break;
case 'FILE':
if ($_FILES[$field['field_id']]['name'] != '') {
// delete the old file
if ($primary_key_id != '') {
JB_delete_file_from_field_id($table_name, $primary_key_name, $primary_key_id, $field['field_id']);
}
$file_name = JB_saveFile($field['field_id'], $user_id);
$str .= ", `" . $field['field_id'] . "` = '" . JB_escape_sql($file_name) . "' ";
}
break;
case 'DATE':
$day = $_REQUEST[$field['field_id'] . "d"];
$month = $_REQUEST[$field['field_id'] . "m"];
$year = $_REQUEST[$field['field_id'] . "y"];
$temp_date = $year . "-" . $month . "-" . $day;
if ($temp_time = strtotime($temp_date . ' 00:00:00')) {
// convert the date timezone to GMT
$temp_time = $temp_time - 3600 * JB_GMT_DIF;
$temp_date = gmdate('Y-m-d H:i:s', $temp_time);
}
$str .= ", `" . $field['field_id'] . "` = '" . JB_escape_sql($temp_date) . "' ";
break;
case 'DATE_CAL':
// Convert SCW Date to ISO Date format before saving in the DB
$temp_date = JB_SCWDate_to_ISODate($_REQUEST[$field['field_id']]);
$temp_date = trim($_REQUEST[$field['field_id']]);
if (strlen($temp_date) > 0) {
$temp_date = JB_SCWDate_to_ISODate($temp_date);
if ($temp_time = strtotime($temp_date . ' 23:59:59')) {
// convert the date timezone to GMT
$temp_time = $temp_time - 3600 * JB_GMT_DIF;
$temp_date = gmdate('Y-m-d H:i:s', $temp_time);
} else {
$temp_date = '';
}
}
$str .= ", `" . JB_escape_sql($field['field_id']) . "` = '" . JB_escape_sql($temp_date) . "' ";
break;
case 'CHECK':
$comma = '';
$tmp = '';
$selected_codes = array();
$selected_codes = $_REQUEST[$field['field_id']];
// the field comes in as an array
for ($i = 0; $i < sizeof($selected_codes); $i++) {
if ($i > 0) {
$comma = ',';
}
$tmp .= $comma . $selected_codes[$i] . "";
}
$_REQUEST[$field['field_id']] = $tmp;
$str .= ", `" . $field['field_id'] . "` = '" . JB_escape_sql($_REQUEST[$field['field_id']]) . "' ";
break;
case 'MSELECT':
$tmp = '';
$comma = '';
$selected_codes = array();
$selected_codes = $_REQUEST[$field['field_id']];
// the field comes in as an array
for ($i = 0; $i < sizeof($selected_codes); $i++) {
if ($i > 0) {
$comma = ',';
}
$tmp .= $comma . $selected_codes[$i] . "";
}
$str .= ", `" . $field['field_id'] . "` = '" . JB_escape_sql($tmp) . "' ";
break;
case 'SKILL_MATRIX':
JB_save_skill_matrix_data($field['field_id'], $primary_key_id, $user_id);
$str .= ", `" . $field['field_id'] . "` = '" . JB_escape_sql($_REQUEST[$field['field_id']]) . "' ";
break;
case 'TEXT':
case 'EDITOR':
$str .= ", `" . $field['field_id'] . "` = '" . JB_escape_sql($_REQUEST[$field['field_id']]) . "' ";
break;
case 'URL':
$str .= ", `" . $field['field_id'] . "` = '" . JB_escape_sql(strip_tags($_REQUEST[$field['field_id']])) . "' ";
break;
case 'NUMERIC':
case 'CURRENCY':
case 'INTEGER':
// featch only the numerical part
preg_match('/[\\+-]?(\\d+(\\.)?(\\d+)?)/', $_REQUEST[$field['field_id']], $m);
if ($m[1] === '0') {
// string zero
$str .= ", `" . $field['field_id'] . "` = '0' ";
} elseif (!$m[1]) {
// empty
$str .= ", `" . $field['field_id'] . "` = NULL ";
} else {
$str .= ", `" . $field['field_id'] . "` = '" . JB_escape_sql($m[1]) . "' ";
}
break;
case 'YOUTUBE':
// extract the video ID form the URL
// eg. http://www.youtube.com/watch?v=iuTNdHadwbk - extract iuTNdHadwbk
if (preg_match('/watch\\?v=([a-z0-9\\-_]+)/i', $_REQUEST[$field['field_id']], $m)) {
$str .= ", `" . $field['field_id'] . "` = '" . JB_escape_sql($m[1]) . "' ";
} elseif (preg_match('/src="http:\\/\\/www\\.youtube\\.com\\/v\\/([a-z0-9\\-_]+)/i', $_REQUEST[$field['field_id']], $m)) {
$str .= ", `" . $field['field_id'] . "` = '" . JB_escape_sql($m[1]) . "' ";
} elseif (preg_match('#http:\\/\\/youtu\\.be\\/([a-z0-9\\-_]+)\\/?#i', $_REQUEST[$field['field_id']], $m)) {
$str .= ", `" . $field['field_id'] . "` = '" . JB_escape_sql($m[1]) . "' ";
} else {
preg_match('/([a-z0-9\\-_]+)/i', $_REQUEST[$field['field_id']], $m);
$str .= ", `" . $field['field_id'] . "` = '" . JB_escape_sql($m[1]) . "' ";
}
break;
default:
$custom_sql = false;
// your plugin would have to generate $custom_sql string like the one after the else { starement
JBPLUG_do_callback('append_sql_update_values', $custom_sql, $field, $table_name, $primary_key_name, $primary_key_id, $user_id);
if ($custom_sql !== false) {
$str .= $custom_sql;
} else {
$str .= ", `" . $field['field_id'] . "` = '" . JB_escape_sql($_REQUEST[$field['field_id']]) . "' ";
}
break;
}
}
return $str;
}
