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; }