1 | <?php |
---|
2 | // +-----------------------------------------------------------------------+ |
---|
3 | // | PhpWebGallery - a PHP based picture gallery | |
---|
4 | // | Copyright (C) 2002-2003 Pierrick LE GALL - pierrick@phpwebgallery.net | |
---|
5 | // | Copyright (C) 2003-2006 PhpWebGallery Team - http://phpwebgallery.net | |
---|
6 | // +-----------------------------------------------------------------------+ |
---|
7 | // | branch : BSF (Best So Far) |
---|
8 | // | file : $Id: functions_search.inc.php 1537 2006-08-15 02:06:06Z rvelices $ |
---|
9 | // | last update : $Date: 2006-08-15 02:06:06 +0000 (Tue, 15 Aug 2006) $ |
---|
10 | // | last modifier : $Author: rvelices $ |
---|
11 | // | revision : $Revision: 1537 $ |
---|
12 | // +-----------------------------------------------------------------------+ |
---|
13 | // | This program is free software; you can redistribute it and/or modify | |
---|
14 | // | it under the terms of the GNU General Public License as published by | |
---|
15 | // | the Free Software Foundation | |
---|
16 | // | | |
---|
17 | // | This program is distributed in the hope that it will be useful, but | |
---|
18 | // | WITHOUT ANY WARRANTY; without even the implied warranty of | |
---|
19 | // | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU | |
---|
20 | // | General Public License for more details. | |
---|
21 | // | | |
---|
22 | // | You should have received a copy of the GNU General Public License | |
---|
23 | // | along with this program; if not, write to the Free Software | |
---|
24 | // | Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, | |
---|
25 | // | USA. | |
---|
26 | // +-----------------------------------------------------------------------+ |
---|
27 | |
---|
28 | |
---|
29 | /** |
---|
30 | * Prepends and appends a string at each value of the given array. |
---|
31 | * |
---|
32 | * @param array |
---|
33 | * @param string prefix to each array values |
---|
34 | * @param string suffix to each array values |
---|
35 | */ |
---|
36 | function prepend_append_array_items($array, $prepend_str, $append_str) |
---|
37 | { |
---|
38 | array_walk( |
---|
39 | $array, |
---|
40 | create_function('&$s', '$s = "'.$prepend_str.'".$s."'.$append_str.'";') |
---|
41 | ); |
---|
42 | |
---|
43 | return $array; |
---|
44 | } |
---|
45 | |
---|
46 | /** |
---|
47 | * returns search rules stored into a serialized array in "search" |
---|
48 | * table. Each search rules set is numericaly identified. |
---|
49 | * |
---|
50 | * @param int search_id |
---|
51 | * @return array |
---|
52 | */ |
---|
53 | function get_search_array($search_id) |
---|
54 | { |
---|
55 | if (!is_numeric($search_id)) |
---|
56 | { |
---|
57 | die('Search id must be an integer'); |
---|
58 | } |
---|
59 | |
---|
60 | $query = ' |
---|
61 | SELECT rules |
---|
62 | FROM '.SEARCH_TABLE.' |
---|
63 | WHERE id = '.$search_id.' |
---|
64 | ;'; |
---|
65 | list($serialized_rules) = mysql_fetch_row(pwg_query($query)); |
---|
66 | |
---|
67 | return unserialize($serialized_rules); |
---|
68 | } |
---|
69 | |
---|
70 | /** |
---|
71 | * returns the SQL clause from a search identifier |
---|
72 | * |
---|
73 | * Search rules are stored in search table as a serialized array. This array |
---|
74 | * need to be transformed into an SQL clause to be used in queries. |
---|
75 | * |
---|
76 | * @param array search |
---|
77 | * @return string |
---|
78 | */ |
---|
79 | function get_sql_search_clause($search) |
---|
80 | { |
---|
81 | // SQL where clauses are stored in $clauses array during query |
---|
82 | // construction |
---|
83 | $clauses = array(); |
---|
84 | |
---|
85 | foreach (array('file','name','comment','author') as $textfield) |
---|
86 | { |
---|
87 | if (isset($search['fields'][$textfield])) |
---|
88 | { |
---|
89 | $local_clauses = array(); |
---|
90 | foreach ($search['fields'][$textfield]['words'] as $word) |
---|
91 | { |
---|
92 | array_push($local_clauses, $textfield." LIKE '%".$word."%'"); |
---|
93 | } |
---|
94 | |
---|
95 | // adds brackets around where clauses |
---|
96 | $local_clauses = prepend_append_array_items($local_clauses, '(', ')'); |
---|
97 | |
---|
98 | array_push( |
---|
99 | $clauses, |
---|
100 | implode( |
---|
101 | ' '.$search['fields'][$textfield]['mode'].' ', |
---|
102 | $local_clauses |
---|
103 | ) |
---|
104 | ); |
---|
105 | } |
---|
106 | } |
---|
107 | |
---|
108 | if (isset($search['fields']['allwords'])) |
---|
109 | { |
---|
110 | $fields = array('file', 'name', 'comment', 'author'); |
---|
111 | // in the OR mode, request bust be : |
---|
112 | // ((field1 LIKE '%word1%' OR field2 LIKE '%word1%') |
---|
113 | // OR (field1 LIKE '%word2%' OR field2 LIKE '%word2%')) |
---|
114 | // |
---|
115 | // in the AND mode : |
---|
116 | // ((field1 LIKE '%word1%' OR field2 LIKE '%word1%') |
---|
117 | // AND (field1 LIKE '%word2%' OR field2 LIKE '%word2%')) |
---|
118 | $word_clauses = array(); |
---|
119 | foreach ($search['fields']['allwords']['words'] as $word) |
---|
120 | { |
---|
121 | $field_clauses = array(); |
---|
122 | foreach ($fields as $field) |
---|
123 | { |
---|
124 | array_push($field_clauses, $field." LIKE '%".$word."%'"); |
---|
125 | } |
---|
126 | // adds brackets around where clauses |
---|
127 | array_push( |
---|
128 | $word_clauses, |
---|
129 | implode( |
---|
130 | "\n OR ", |
---|
131 | $field_clauses |
---|
132 | ) |
---|
133 | ); |
---|
134 | } |
---|
135 | |
---|
136 | array_walk( |
---|
137 | $word_clauses, |
---|
138 | create_function('&$s','$s="(".$s.")";') |
---|
139 | ); |
---|
140 | |
---|
141 | array_push( |
---|
142 | $clauses, |
---|
143 | "\n ". |
---|
144 | implode( |
---|
145 | "\n ". |
---|
146 | $search['fields']['allwords']['mode']. |
---|
147 | "\n ", |
---|
148 | $word_clauses |
---|
149 | ) |
---|
150 | ); |
---|
151 | } |
---|
152 | |
---|
153 | foreach (array('date_available', 'date_creation') as $datefield) |
---|
154 | { |
---|
155 | if (isset($search['fields'][$datefield])) |
---|
156 | { |
---|
157 | array_push( |
---|
158 | $clauses, |
---|
159 | $datefield." = '".$search['fields'][$datefield]['date']."'" |
---|
160 | ); |
---|
161 | } |
---|
162 | |
---|
163 | foreach (array('after','before') as $suffix) |
---|
164 | { |
---|
165 | $key = $datefield.'-'.$suffix; |
---|
166 | |
---|
167 | if (isset($search['fields'][$key])) |
---|
168 | { |
---|
169 | array_push( |
---|
170 | $clauses, |
---|
171 | |
---|
172 | $datefield. |
---|
173 | ($suffix == 'after' ? ' >' : ' <'). |
---|
174 | ($search['fields'][$key]['inc'] ? '=' : ''). |
---|
175 | " '".$search['fields'][$key]['date']."'" |
---|
176 | |
---|
177 | ); |
---|
178 | } |
---|
179 | } |
---|
180 | } |
---|
181 | |
---|
182 | if (isset($search['fields']['cat'])) |
---|
183 | { |
---|
184 | if ($search['fields']['cat']['sub_inc']) |
---|
185 | { |
---|
186 | // searching all the categories id of sub-categories |
---|
187 | $cat_ids = get_subcat_ids($search['fields']['cat']['words']); |
---|
188 | } |
---|
189 | else |
---|
190 | { |
---|
191 | $cat_ids = $search['fields']['cat']['words']; |
---|
192 | } |
---|
193 | |
---|
194 | $local_clause = 'category_id IN ('.implode(',', $cat_ids).')'; |
---|
195 | array_push($clauses, $local_clause); |
---|
196 | } |
---|
197 | |
---|
198 | // adds brackets around where clauses |
---|
199 | $clauses = prepend_append_array_items($clauses, '(', ')'); |
---|
200 | |
---|
201 | $where_separator = |
---|
202 | implode( |
---|
203 | "\n ".$search['mode'].' ', |
---|
204 | $clauses |
---|
205 | ); |
---|
206 | |
---|
207 | $search_clause = $where_separator; |
---|
208 | |
---|
209 | return $search_clause; |
---|
210 | } |
---|
211 | |
---|
212 | /** |
---|
213 | * returns the list of items corresponding to the advanced search array |
---|
214 | * |
---|
215 | * @param array search |
---|
216 | * @return array |
---|
217 | */ |
---|
218 | function get_regular_search_results($search) |
---|
219 | { |
---|
220 | $items = array(); |
---|
221 | |
---|
222 | $search_clause = get_sql_search_clause($search); |
---|
223 | |
---|
224 | if (!empty($search_clause)) |
---|
225 | { |
---|
226 | $query = ' |
---|
227 | SELECT DISTINCT(id) |
---|
228 | FROM '.IMAGES_TABLE.' |
---|
229 | INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON id = ic.image_id |
---|
230 | WHERE '.$search_clause.' |
---|
231 | ;'; |
---|
232 | $items = array_from_query($query, 'id'); |
---|
233 | } |
---|
234 | |
---|
235 | $search = get_search_array($search_id); |
---|
236 | |
---|
237 | if (isset($search['fields']['tags'])) |
---|
238 | { |
---|
239 | $tag_items = get_image_ids_for_tags( |
---|
240 | $search['fields']['tags']['words'], |
---|
241 | $search['fields']['tags']['mode'] |
---|
242 | ); |
---|
243 | |
---|
244 | switch ($search['mode']) |
---|
245 | { |
---|
246 | case 'AND': |
---|
247 | { |
---|
248 | if (empty($search_clause)) |
---|
249 | { |
---|
250 | $items = $tag_items; |
---|
251 | } |
---|
252 | else |
---|
253 | { |
---|
254 | $items = array_intersect($items, $tag_items); |
---|
255 | } |
---|
256 | break; |
---|
257 | } |
---|
258 | case 'OR': |
---|
259 | { |
---|
260 | $items = array_unique( |
---|
261 | array_merge( |
---|
262 | $items, |
---|
263 | $tag_items |
---|
264 | ) |
---|
265 | ); |
---|
266 | break; |
---|
267 | } |
---|
268 | } |
---|
269 | } |
---|
270 | |
---|
271 | return $items; |
---|
272 | } |
---|
273 | |
---|
274 | |
---|
275 | if (!function_exists('array_intersect_key')) { |
---|
276 | function array_intersect_key() |
---|
277 | { |
---|
278 | $arrs = func_get_args(); |
---|
279 | $result = array_shift($arrs); |
---|
280 | foreach ($arrs as $array) { |
---|
281 | foreach ($result as $key => $v) { |
---|
282 | if (!array_key_exists($key, $array)) { |
---|
283 | unset($result[$key]); |
---|
284 | } |
---|
285 | } |
---|
286 | } |
---|
287 | return $result; |
---|
288 | } |
---|
289 | } |
---|
290 | |
---|
291 | |
---|
292 | function get_qsearch_like_clause($q, $field) |
---|
293 | { |
---|
294 | $tokens = preg_split('/[\s,.;!\?]+/', $q); |
---|
295 | for ($i=0; $i<count($tokens); $i++) |
---|
296 | { |
---|
297 | $tokens[$i]=str_replace('*','%', $tokens[$i]); |
---|
298 | if (preg_match('/^[+<>]/',$tokens[$i]) ) |
---|
299 | $tokens[$i]=substr($tokens[$i], 1); |
---|
300 | else if (substr($tokens[$i], 0, 1)=='-') |
---|
301 | { |
---|
302 | unset($tokens[$i]); |
---|
303 | $i--; |
---|
304 | } |
---|
305 | } |
---|
306 | |
---|
307 | if (!empty($tokens)) |
---|
308 | { |
---|
309 | $query = '('; |
---|
310 | for ($i=0; $i<count($tokens); $i++) |
---|
311 | { |
---|
312 | if ($i>0) $query .= 'OR '; |
---|
313 | $query .= ' '.$field.' LIKE "%'.$tokens[$i].'%" '; |
---|
314 | } |
---|
315 | $query .= ')'; |
---|
316 | return $query; |
---|
317 | } |
---|
318 | return null; |
---|
319 | } |
---|
320 | |
---|
321 | |
---|
322 | /** |
---|
323 | * returns the search results corresponding to a quick search |
---|
324 | * |
---|
325 | * @param string q |
---|
326 | * @return array |
---|
327 | */ |
---|
328 | function get_quick_search_results($q) |
---|
329 | { |
---|
330 | global $user, $page; |
---|
331 | $search_results = array(); |
---|
332 | |
---|
333 | $q_like_clause = get_qsearch_like_clause($q, 'CONVERT(name, CHAR)' ); |
---|
334 | $by_tag_weights=array(); |
---|
335 | if (!empty($q_like_clause)) |
---|
336 | { |
---|
337 | $query = ' |
---|
338 | SELECT id |
---|
339 | FROM '.TAGS_TABLE.' |
---|
340 | WHERE '.$q_like_clause; |
---|
341 | $tag_ids = array_from_query( $query, 'id'); |
---|
342 | if (!empty($tag_ids)) |
---|
343 | { |
---|
344 | $query = ' |
---|
345 | SELECT image_id, COUNT(tag_id) AS q |
---|
346 | FROM '.IMAGE_TAG_TABLE.' |
---|
347 | WHERE tag_id IN ('.implode(',',$tag_ids).') |
---|
348 | GROUP BY image_id'; |
---|
349 | $result = pwg_query($query); |
---|
350 | while ($row = mysql_fetch_array($result)) |
---|
351 | { |
---|
352 | $by_tag_weights[(int)$row['image_id']] = $row['q']; |
---|
353 | } |
---|
354 | } |
---|
355 | } |
---|
356 | |
---|
357 | $query = ' |
---|
358 | SELECT |
---|
359 | i.id, i.file, CAST( CONCAT_WS(" ", |
---|
360 | IFNULL(i.name,""), |
---|
361 | IFNULL(i.comment,""), |
---|
362 | IFNULL(GROUP_CONCAT(DISTINCT co.content),""), |
---|
363 | IFNULL(GROUP_CONCAT(DISTINCT c.dir),""), |
---|
364 | IFNULL(GROUP_CONCAT(DISTINCT c.name),""), |
---|
365 | IFNULL(GROUP_CONCAT(DISTINCT c.comment),"") ) AS CHAR) AS ft |
---|
366 | FROM ( |
---|
367 | ( |
---|
368 | '.IMAGES_TABLE.' i LEFT JOIN '.COMMENTS_TABLE.' co on i.id=co.image_id |
---|
369 | ) |
---|
370 | INNER JOIN |
---|
371 | '.IMAGE_CATEGORY_TABLE.' ic on ic.image_id=i.id |
---|
372 | ) |
---|
373 | INNER JOIN |
---|
374 | '.CATEGORIES_TABLE.' c on c.id=ic.category_id |
---|
375 | WHERE category_id NOT IN ('.$user['forbidden_categories'].') |
---|
376 | GROUP BY i.id'; |
---|
377 | |
---|
378 | $query = 'SELECT id, MATCH(ft) AGAINST( "'.$q.'" IN BOOLEAN MODE) AS q FROM ('.$query.') AS Y |
---|
379 | WHERE MATCH(ft) AGAINST( "'.$q.'" IN BOOLEAN MODE)'; |
---|
380 | |
---|
381 | $q_like_clause = get_qsearch_like_clause($q, 'file' ); |
---|
382 | if (! empty($q_like_clause) ) |
---|
383 | { |
---|
384 | $query .= ' OR '.$q_like_clause; |
---|
385 | } |
---|
386 | |
---|
387 | $by_weights=array(); |
---|
388 | $result = pwg_query($query); |
---|
389 | while ($row = mysql_fetch_array($result)) |
---|
390 | { |
---|
391 | $by_weights[(int)$row['id']] = $row['q'] ? $row['q'] : 0; |
---|
392 | } |
---|
393 | |
---|
394 | foreach ( $by_weights as $image=>$w ) |
---|
395 | { |
---|
396 | $by_tag_weights[$image] = 2*$w+ (isset($by_tag_weights[$image])?$by_tag_weights[$image]:0); |
---|
397 | } |
---|
398 | |
---|
399 | if ( empty($by_tag_weights) or isset($page['super_order_by']) ) |
---|
400 | { |
---|
401 | if (! isset($page['super_order_by']) ) |
---|
402 | { |
---|
403 | arsort($by_tag_weights, SORT_NUMERIC); |
---|
404 | $search_results['as_is']=1; |
---|
405 | } |
---|
406 | $search_results['items'] = array_keys($by_tag_weights); |
---|
407 | } |
---|
408 | else |
---|
409 | { |
---|
410 | $query = ' |
---|
411 | SELECT DISTINCT(id) |
---|
412 | FROM '.IMAGES_TABLE.' |
---|
413 | INNER JOIN '.IMAGE_CATEGORY_TABLE.' AS ic ON id = ic.image_id |
---|
414 | WHERE id IN ('.implode(',', array_keys($by_tag_weights) ).') |
---|
415 | AND category_id NOT IN ('.$user['forbidden_categories'].')'; |
---|
416 | |
---|
417 | $allowed_image_ids = array_from_query( $query, 'id'); |
---|
418 | $by_tag_weights = array_intersect_key($by_tag_weights, array_flip($allowed_image_ids)); |
---|
419 | arsort($by_tag_weights, SORT_NUMERIC); |
---|
420 | $search_results = array( |
---|
421 | 'items'=>array_keys($by_tag_weights), |
---|
422 | 'as_is'=>1 |
---|
423 | ); |
---|
424 | } |
---|
425 | return $search_results; |
---|
426 | } |
---|
427 | |
---|
428 | /** |
---|
429 | * returns an array of 'items' corresponding to the search id |
---|
430 | * |
---|
431 | * @param int search id |
---|
432 | * @return array |
---|
433 | */ |
---|
434 | function get_search_results($search_id) |
---|
435 | { |
---|
436 | $search = get_search_array($search_id); |
---|
437 | if ( !isset($search['q']) ) |
---|
438 | { |
---|
439 | $result['items'] = get_regular_search_results($search); |
---|
440 | return $result; |
---|
441 | } |
---|
442 | else |
---|
443 | { |
---|
444 | return get_quick_search_results($search['q']); |
---|
445 | } |
---|
446 | } |
---|
447 | ?> |
---|