/** * Returns the SQL clause for a search. * Transforms the array returned by get_search_array() into SQL sub-query. * * @param array $search * @return string */ function get_sql_search_clause($search) { // SQL where clauses are stored in $clauses array during query // construction $clauses = array(); foreach (array('file', 'name', 'comment', 'author') as $textfield) { if (isset($search['fields'][$textfield])) { $local_clauses = array(); foreach ($search['fields'][$textfield]['words'] as $word) { if ('author' == $textfield) { $local_clauses[] = $textfield . "='" . $word . "'"; } else { $local_clauses[] = $textfield . " LIKE '%" . $word . "%'"; } } // adds brackets around where clauses $local_clauses = prepend_append_array_items($local_clauses, '(', ')'); $clauses[] = implode(' ' . $search['fields'][$textfield]['mode'] . ' ', $local_clauses); } } if (isset($search['fields']['allwords'])) { $fields = array('file', 'name', 'comment'); if (isset($search['fields']['allwords']['fields']) and count($search['fields']['allwords']['fields']) > 0) { $fields = array_intersect($fields, $search['fields']['allwords']['fields']); } // in the OR mode, request bust be : // ((field1 LIKE '%word1%' OR field2 LIKE '%word1%') // OR (field1 LIKE '%word2%' OR field2 LIKE '%word2%')) // // in the AND mode : // ((field1 LIKE '%word1%' OR field2 LIKE '%word1%') // AND (field1 LIKE '%word2%' OR field2 LIKE '%word2%')) $word_clauses = array(); foreach ($search['fields']['allwords']['words'] as $word) { $field_clauses = array(); foreach ($fields as $field) { $field_clauses[] = $field . " LIKE '%" . $word . "%'"; } // adds brackets around where clauses $word_clauses[] = implode("\n OR ", $field_clauses); } array_walk($word_clauses, create_function('&$s', '$s="(".$s.")";')); // make sure the "mode" is either OR or AND if ($search['fields']['allwords']['mode'] != 'AND' and $search['fields']['allwords']['mode'] != 'OR') { $search['fields']['allwords']['mode'] = 'AND'; } $clauses[] = "\n " . implode("\n " . $search['fields']['allwords']['mode'] . "\n ", $word_clauses); } foreach (array('date_available', 'date_creation') as $datefield) { if (isset($search['fields'][$datefield])) { $clauses[] = $datefield . " = '" . $search['fields'][$datefield]['date'] . "'"; } foreach (array('after', 'before') as $suffix) { $key = $datefield . '-' . $suffix; if (isset($search['fields'][$key])) { $clauses[] = $datefield . ($suffix == 'after' ? ' >' : ' <') . ($search['fields'][$key]['inc'] ? '=' : '') . " '" . $search['fields'][$key]['date'] . "'"; } } } if (isset($search['fields']['cat'])) { if ($search['fields']['cat']['sub_inc']) { // searching all the categories id of sub-categories $cat_ids = get_subcat_ids($search['fields']['cat']['words']); } else { $cat_ids = $search['fields']['cat']['words']; } $local_clause = 'category_id IN (' . implode(',', $cat_ids) . ')'; $clauses[] = $local_clause; } // adds brackets around where clauses $clauses = prepend_append_array_items($clauses, '(', ')'); $where_separator = implode("\n " . $search['mode'] . ' ', $clauses); $search_clause = $where_separator; return $search_clause; }
/** * Perform history search. * * @param array $data - used in trigger_change * @param array $search * @param string[] $types * @param array */ function get_history($data, $search, $types) { if (isset($search['fields']['filename'])) { $query = ' SELECT id FROM ' . IMAGES_TABLE . ' WHERE file LIKE \'' . $search['fields']['filename'] . '\' ;'; $search['image_ids'] = array_from_query($query, 'id'); } // echo '<pre>'; print_r($search); echo '</pre>'; $clauses = array(); if (isset($search['fields']['date-after'])) { $clauses[] = "date >= '" . $search['fields']['date-after'] . "'"; } if (isset($search['fields']['date-before'])) { $clauses[] = "date <= '" . $search['fields']['date-before'] . "'"; } if (isset($search['fields']['types'])) { $local_clauses = array(); foreach ($types as $type) { if (in_array($type, $search['fields']['types'])) { $clause = 'image_type '; if ($type == 'none') { $clause .= 'IS NULL'; } else { $clause .= "= '" . $type . "'"; } $local_clauses[] = $clause; } } if (count($local_clauses) > 0) { $clauses[] = implode(' OR ', $local_clauses); } } if (isset($search['fields']['user']) and $search['fields']['user'] != -1) { $clauses[] = 'user_id = ' . $search['fields']['user']; } if (isset($search['fields']['image_id'])) { $clauses[] = 'image_id = ' . $search['fields']['image_id']; } if (isset($search['fields']['filename'])) { if (count($search['image_ids']) == 0) { // a clause that is always false $clauses[] = '1 = 2 '; } else { $clauses[] = 'image_id IN (' . implode(', ', $search['image_ids']) . ')'; } } if (isset($search['fields']['ip'])) { $clauses[] = 'IP LIKE "' . $search['fields']['ip'] . '"'; } $clauses = prepend_append_array_items($clauses, '(', ')'); $where_separator = implode("\n AND ", $clauses); $query = ' SELECT date, time, user_id, IP, section, category_id, tag_ids, image_id, image_type FROM ' . HISTORY_TABLE . ' WHERE ' . $where_separator . ' ;'; // LIMIT '.$conf['nb_logs_page'].' OFFSET '.$page['start'].' $result = pwg_query($query); while ($row = pwg_db_fetch_assoc($result)) { $data[] = $row; } return $data; }