0 ? (int) $_GET['anzPerPage'] : 25; $anzPerPage = $_GET['anzPerPage']; /* categories[]: array(cat1=>subcats1,cat2=>subcats2) subcats ggf. Komma separierte Liste */ $_GET['categories'] = isset($_GET['categories']) ? $_GET['categories'] : null; /* Mindestgröße: original_x,original_y */ $_GET['original_x'] = isset($_GET['original_x']) ? (int) $_GET['original_x'] : 0; $_GET['original_y'] = isset($_GET['original_y']) ? (int) $_GET['original_y'] : 0; /* type: 0=Cartoons,1=Illustrationen,-1=egal */ $_GET['type'] = isset($_GET['type']) ? (int) $_GET['type'] : -1; /* orderby: date (uploads_profi_de.date desc), title (uploads.title), name (user.name) */ $_GET['orderby'] = isset($_GET['orderby']) ? $_GET['orderby'] : ''; /* format: ggf. Komma separiert 0=quer,1=hoch,2=quadratisch oder leer(egal) */ $_GET['querformat'] = isset($_GET['querformat']) ? (int) $_GET['querformat'] : 0; $_GET['hochformat'] = isset($_GET['hochformat']) ? (int) $_GET['hochformat'] : 0; $_GET['quadratformat'] = isset($_GET['quadratformat']) ? (int) $_GET['quadratformat'] : 0; $_GET['format'] = isset($_GET['format']) ? $_GET['format'] : ''; $_GET['artist'] = isset($_GET['artist']) ? $_GET['artist'] : ''; $_GET['offset'] = isset($_GET['offset']) ? (int) $_GET['offset'] : 0; $_GET['method'] = isset($_GET['method']) ? (int) $_GET['method'] : 0; $_GET['search_qi'] = isset($_GET['search_qi']) ? rawurldecode(trim($_GET['search_qi'])) : ''; $_GET['country'] = isset($_GET['country']) ? (int) $_GET['country'] : 0; $_GET['language'] = isset($_GET['language']) ? (int) $_GET['language'] : 0; $_GET['technique'] = isset($_GET['technique']) ? (int) $_GET['technique'] : 0; /* * detail_title, detail_imageid, detail_name für die Suche unwichtig, nur für die Ausgabe */ $_GET['detail_title'] = isset($_GET['detail_title']) ? (int) $_GET['detail_title'] : 1; $_GET['detail_imageid'] = isset($_GET['detail_imageid']) ? (int) $_GET['detail_imageid'] : 1; $_GET['detail_name'] = isset($_GET['detail_name']) ? (int) $_GET['detail_name'] : 0; //Steuert das speichern der Suche: $_GET['recentsearch'] = isset($_GET['recentsearch']) ? (int) $_GET['recentsearch'] : 0; // Ähnlichkeitssuche zu einem ausgewählten Bild $_GET['similar'] = isset($_GET['similar']) ? (int) $_GET['similar'] : 0; //Benötigte Variablen mit Standardwerten initialisieren $sql = ''; $navi_link = ''; $search_feedback = ''; $cartoons = array(); $anzG = 0; //Suchquery zusammenbauen, navi_link erzeugen und search_feedback erzeugen createSearch(); $offsetid = $_GET['offset']; $db->query($sql); while ($db->next_record()) { $cartoons[] = getThumbData($db->row(),$offsetid++); } $db->query("SELECT FOUND_ROWS()"); $anzG = $db->result(); //Navigationleiste generieren $navi = printNavi($anzG, $_GET['offset'], $anzPerPage, $navi_link, ''); if ($_SESSION['profiid'] > 1 && $_GET['recentsearch'] == 0) { $navi_link .= ($navi_link == '') ? ($_GET['offset'] > 0 ? '?offset='.$_GET['offset'] : '') : ($_GET['offset'] > 0 ? '&offset='.$_GET['offset'] : ''); $db->query("INSERT INTO profi_search_history (profiid,query,feedback,date) VALUES (".(int)$_SESSION['profiid'].",'".$db->es(htmlspecialchars_decode($navi_link))."','".$db->es($search_feedback)."',".time().")"); $recentsearch = substr($recentsearch,0,24)."recentsearch.items[".$db->li()."] = {query:\"".addslashes(htmlspecialchars_decode($navi_link))."\",feedback:'".addslashes($search_feedback)."'};".substr($recentsearch,24); } // Spezialfall: (auch) nach Tags gesucht, aber keine Bilder gefunden... if (($anzG == 0) && ($_GET['search_qi'] != '')) { $fuzzy_search = 1; createFuzzy(); } else { $fuzzy_search = 0; } require TP_DOCUMENT_ROOT.'toonagent/includes/templates/tpl_gallery.php'; function findTags ($all) { $tags1 = array(); $erg = array(); while (preg_match('/(\+|-)?(?:"|\')(.+?)(?:"|\')/',$all,$erg)) { $tags1[] = $erg[1].$erg[2]; $all = str_replace($erg[0],'',$all); } while (strpos($all,' ') !== false) { $all = str_replace(' ',' ',$all); } $tags2 = explode(' ',$all); return array_filter(array_merge($tags1,$tags2),'findTagsFilter'); } function findTagsFilter($v) { if (!empty($v)) { return true; } else { return false; } } function createFuzzy() { global $db,$fuzzy,$navi_data; $fuzzy = array(); $href = '/toonagent/gallery.php?'; foreach ($navi_data as $v) { if (substr($v,0,10) != 'search_qi=') { $href .= $v.'&'; } } $tags = findTags($_GET['search_qi']); $sql = ''; foreach ($tags as $tag) { $sql .= (($sql == '') ? '' : ',').'\''.$db->es(koeln::getCode($tag)).'\''; } $sql = 'SELECT ptag,count FROM ptags_de WHERE ptag_koeln IN ('.$sql.') ORDER BY count DESC'; $db->query($sql); while ($db->next_record()) { $fuzzy[] = array('href' => $href.'search_qi='.rawurlencode($db->f('ptag')), 'tag' => $db->f('ptag'), 'title' => $db->f('ptag').' ('.$db->f('count').')'); } } function createSearch() { global $db,$sql,$anzPerPage,$navi_link,$search_feedback,$navi_data; //navi_link $navi_link .= '/toonagent/gallery'; $navi_data = array(); //SQL $sql_start = 'SELECT '; $sql_distinct = ''; $sql_fields = 'SQL_CALC_FOUND_ROWS up.imageid,up.userid,up.title,up.imageuid,up.thumb_x,up.thumb_y,plu.imageid as plu_exists, upp.date AS orderDate, user.name AS userName, up.title AS uploadTitle '; $sql_from = 'FROM '; $sql_join = ' LEFT OUTER JOIN profi_lightbox_uploads plu ON ('.(int)$_SESSION['profiid'].',upp.imageid) = (plu.profiid,plu.imageid) '; $sql_where = 'WHERE up.imageid = upp.imageid '; $sql_order = 'ORDER BY orderDate DESC '; $sql_limit = 'LIMIT '.$_GET['offset'].','.$anzPerPage; $sqlUnionStart = ""; $sqlUnion = ""; $sqlUnionWhere = ""; // Tabellennamen $upp = 'uploads_profi_de'; $ptags = 'ptags_de'; $ptags_search = 'ptags_search_de'; $categories_search = 'categories_search_de'; $user_table = 'user'; //Für die Navigation, muss hier stehen, falls man alle Bilder sehen möchte und nichts eingegeben hat $navi_data[] = 'search_qi='.$_GET['search_qi']; // Prinzipiell anzPerPage mit in Navi-Data übernehmen $navi_data[] = 'anzPerPage='.$anzPerPage; if ($_GET['search_qi'] != '') { if (is_numeric($_GET['search_qi'])) { $sql_where .= 'AND up.imageid = ' . $db->es((int)$_GET['search_qi']) . ' '; } else { //SQL-Statement //Zuerstmal die BEgriffe in Hochkommas filtern $tags = findTags($_GET['search_qi']); $anzT = min(count($tags),28);//AVOID a JOIN over more than 61 tables if ($_GET['method'] == 1) {//AND Suche // !!! Achtung, mySql braucht den Straight-Join-Hinweis, wenn viele Tags gesucht werden $sql_start = 'SELECT STRAIGHT_JOIN '; //Feedback für den Benutzer $search_feedback .= ''.GAL_TAGS.GAL_TAGS_METHOD1.': '.implode(',',$tags).', '; //Nur hier method in den Navi-Link aufnehmen. $navi_data[] = 'method='.$_GET['method']; $a = 0; // Anzahl AND-Tags for ($i = 0; $i < $anzT; $i++) { if ($tags[$i][0] != '-') { $a++; } } for ($i = 0; $i < $anzT; $i++) { if ($tags[$i][0] == '-') { // NOT-Semantik: -Tag soll immer unterdrückt werden $sql_where .= 'AND NOT EXISTS '; $sql_where .= '(SELECT * FROM '.$ptags_search.' s,'.$ptags.' t WHERE up.imageid=s.imageid AND s.ptagid=t.ptagid AND t.ptag LIKE \''; $sql_where .= $db->es(str_replace(array('+','-'),array('',''),$tags[$i]),true); $sql_where .= '%\') '; } else { --$a; $sql_from .= $ptags.' a'.$a.', '.$ptags_search.' b'.$a.', '; $sql_where .= 'AND a'.$a.'.ptag LIKE \''; $sql_where .= $db->es(str_replace(array('+','-'),array('',''),$tags[$i]),true); $sql_where .= '%\' AND a'.$a.'.ptagid=b'.$a.'.ptagid AND b'.$a.'.imageid='.(($a>0)?'b'.($a-1):'up').'.imageid '; } } } else if ($_GET['method'] == 0) {//OR Suche //LS NEW: UNION the result with a search in the normal keywords.. $sqlUnionStart = "("; $sqlUnion = ") UNION (SELECT up.imageid,up.userid,up.title,up.imageuid,up.thumb_x,up.thumb_y,plu.imageid as plu_exists, upp.date AS orderDate, user.name AS userName, up.title AS uploadTitle, user.name FROM "; $sqlUnionWhere = $sql_where; //Feedback für den Benutzer $search_feedback .= ''.GAL_TAGS.GAL_TAGS_METHOD0.': '.implode(',',$tags).', '; $sql_distinct = 'DISTINCT '; $sql_from .= $ptags.' a0, '.$ptags_search.' b0, '; $sqlUnion .= '(tags a0, tags_search b0, uploads up, ' .$upp .' upp, user) '; $sql_where .= 'AND a0.ptagid = b0.ptagid AND b0.imageid = up.imageid AND ('; $sqlUnionWhere .= 'AND a0.tagid = b0.tagid AND b0.imageid = up.imageid AND ('; if ($tags[0][0] == '-') { $sql_where .= 'a0.ptag NOT LIKE \''.$db->es(str_replace(array('+','-'),array('',''),$tags[0]),true).'%\' '; $sqlUnionWhere .= $sql_where .= 'a0.tag NOT LIKE \''.$db->es(str_replace(array('+','-'),array('',''),$tags[0]),true).'%\' '; } else { $sql_where .= 'a0.ptag LIKE \''.$db->es(str_replace(array('+','-'),array('',''),$tags[0]),true).'%\' '; $sqlUnionWhere .= 'a0.tag LIKE \''.$db->es(str_replace(array('+','-'),array('',''),$tags[0]),true).'%\' '; } for ($i = 1; $i < $anzT; $i++) { if ($tags[0][0] == '-') { $sql_where .= 'OR a0.ptag NOT LIKE \''.$db->es(str_replace(array('+','-'),array('',''),$tags[$i]),true).'%\' '; $sqlUnionWhere .= 'OR a0.tag NOT LIKE \''.$db->es(str_replace(array('+','-'),array('',''),$tags[$i]),true).'%\' '; } else { $sql_where .= 'OR a0.ptag LIKE \''.$db->es(str_replace(array('+','-'),array('',''),$tags[$i]),true).'%\' '; $sqlUnionWhere .= 'OR a0.tag LIKE \''.$db->es(str_replace(array('+','-'),array('',''),$tags[$i]),true).'%\' '; } } $sql_where .= ') '; //LS $sqlUnion .= $sql_join; $sqlUnionWhere .= ") AND up.userid = user.userid) "; } } } // Sortier-Ordnung festlegen if ($_GET['orderby'] == 'date') { $navi_data[] = 'orderby=date'; $sql_order = 'ORDER BY orderDate DESC '; $search_feedback .= ''.GAL_ORDER.': '.GAL_ORDER_DATE .', '; } elseif ($_GET['orderby'] == 'title') { $navi_data[] = 'orderby=title'; $sql_order = 'ORDER BY uploadTitle '; $search_feedback .= ''.GAL_ORDER.': '.GAL_ORDER_TITLE .', '; } elseif ($_GET['orderby'] == 'name') { $navi_data[] = 'orderby=name'; $sql_order = 'ORDER BY userName ASC,orderDate DESC '; $search_feedback .= ''.GAL_ORDER.': '.GAL_ORDER_NAME .', '; } // Typ auf Catoon/Illustration einschränken if ($_GET['type'] <> -1) { $navi_data[] = 'type='.$_GET['type']; $search_feedback .= ''.GAL_TYPE.': '.(($_GET['type'] == 0) ? GAL_TYPE_0 : GAL_TYPE_1).', '; $sql_where .= 'AND up.type = '.$_GET['type'].' '; } // Mindestbreite einschränken if ($_GET['original_x'] > 0) { $navi_data[] = 'original_x='.$_GET['original_x']; $search_feedback .= ''.GAL_ORIGINAL_X.': '.$_GET['original_x'].', '; $sql_where .= 'AND up.original_x > '.$_GET['original_x'].' '; } // Mindesthöhe einschränken if ($_GET['original_y'] > 0) { $navi_data[] = 'original_y='.$_GET['original_y']; $search_feedback .= ''.GAL_ORIGINAL_Y.': '.$_GET['original_y'].', '; $sql_where .= 'AND up.original_y > '.$_GET['original_y'].' '; } // Länderauswahl einschränken if ($_GET['country'] > 0) { $navi_data[] = 'country='.$_GET['country']; $db->query('SELECT long_desc FROM countries_de WHERE countryid = '.$_GET['country']); $search_feedback .= ''.GAL_COUNTRY.': '.$db->result().', '; $sql_where .= 'AND upp.pcountryid = '.$_GET['country'].' '; } // Sprache einschränken if ($_GET['language'] > 0) { $navi_data[] = 'language='.$_GET['language']; $db->query('SELECT long_desc FROM languages_de WHERE languageid = '.$_GET['language']); $search_feedback .= ''.GAL_LANGUAGE.': '.$db->result().', '; $sql_where .= 'AND upp.planguageid = '.$_GET['language'].' '; } // Künstler einschränken if ($_GET['artist'] != '') { $navi_data[] = 'artist='.$_GET['artist']; $db->query('SELECT userid FROM user WHERE name LIKE \''.$db->es($_GET['artist'],true).'%\''); $search_feedback .= ''.GAL_ARTIST.': '.$_GET['artist'].', '; $sql_where .= 'AND user.userid = '.(int)$db->result().' '; } // Technik einschränken if ($_GET['technique'] > 0) { $navi_data[] = 'technique='.$_GET['technique']; $db->query('SELECT technique FROM techniques_de WHERE techid = '.$_GET['technique']); $search_feedback .= ''.GAL_TECH.': '.$db->result().', '; $sql_where .= 'AND upp.ptechid = '.$_GET['technique'].' '; } // Format-Selektion aufbereiten, entweder format angegeben oder // die drei Selektoren querformat,hochformat und quadratformat if ($_GET['format'] === '') { if ($_GET['querformat'] != 0) { $_GET['format'] .= '0,'; } if ($_GET['hochformat'] != 0) { $_GET['format'] .= '1,'; } if ($_GET['quadratformat'] != 0) { $_GET['format'] .= '2,'; } if ($_GET['format'] !== '') { $_GET['format'] = substr($_GET['format'],0,-1); } if ($_GET['format'] == '0,1,2') { $_GET['format'] = ''; } } // Formate einschränken if ($_GET['format'] !== '') { $navi_data[] = 'format='.str_replace(',','%2C',$_GET['format']); if ($_GET['format'] != '0,1,2') { $search_feedback .= ''.GAL_FORMAT.': '; foreach (explode(',',$_GET['format']) as $form) { $search_feedback .= (($form == 0) ? SI_QUERFORMAT : (($form == 1) ? SI_HOCHFORMAT : SI_QUADRATFORMAT )).', '; } $sql_where .= 'AND up.format IN ('.$_GET['format'].') '; } } // Kategorien einschränken, Subkategorien als Komma-separierte Liste if (is_array($_GET['categories'])) { $sql_cats = ''; foreach ($_GET['categories'] as $cat => $subcats) { $navi_data[] = 'categories%5B'.$cat.'%5D='.str_replace(',','%2C',$subcats); if ($sql_cats !== '') { $sql_cats .= ' OR'; } if ($subcats != '') { // Kategorie mit Unterkategorie(n) ausgewählt $sql_cats .= ' upp.pcatid = '.(int)$cat.' AND upp.imageid IN (SELECT imageid FROM '. $categories_search.' WHERE catid IN ('.(int)$subcats.'))'; $db->query('SELECT category FROM categories_de WHERE catid = '.(int)$cat); $search_feedback .= ''.GAL_CAT.': '.$db->result().', '; $db->query('SELECT category FROM categories_de WHERE catid IN ('.$subcats.')'); $search_feedback .= ''.GAL_SUBCAT.': '; while ($db->next_record()) { $search_feedback .= $db->f('category').', '; } } else { // nur Kategorie ohne Unterkategorien ausgewählt $sql_cats .= ' upp.pcatid = '.$cat.' '; $db->query('SELECT category FROM categories_de WHERE catid = '.(int)$cat); $search_feedback .= ''.GAL_CAT.': '.$db->result().', '; } } if ($sql_cats != '') { $sql_where .= 'AND ( '.$sql_cats.') '; } } // Zusätzliche Informationen: detail_name, detail_imageid, detail_title $sql_where .= 'AND up.userid = user.userid '; $sql_fields .= ',user.name '; if ($_GET['detail_name'] > 0) { $navi_data[] = 'detail_name=1'; } else { // $navi_data[] = 'detail_name=0'; } if ($_GET['detail_imageid'] > 0) { // $navi_data[] = 'detail_imageid=1'; } else { $navi_data[] = 'detail_imageid=0'; } if ($_GET['detail_title'] > 0) { // $navi_data[] = 'detail_title=1'; } else { $navi_data[] = 'detail_title=0'; } if ($_GET['similar'] > 0) { //Performance Boost $perfor = ''; $db->query('SELECT s.secondid AS what FROM profi_similar s WHERE s.imageid = '.$_GET['similar'].' UNION SELECT s.imageid AS what FROM profi_similar s WHERE s.secondid = '.$_GET['similar']); while ($db->next_record()) { $perfor .= ','.$db->f('what'); } $sql_where .= 'AND upp.imageid IN ('.substr($perfor,1).')'; $navi_data[] = 'similar='.$_GET['similar']; $search_feedback .= ''.GAL_SIMILAR.': '.$_GET['similar'].', '; } $_SESSION['sql_last_query'] = $sql_start.$sql_distinct.$sql_fields.$sql_from.'uploads up, ' . $user_table . ','.$upp.' upp' . $sql_join.$sql_where.$sql_order; $sql = $sqlUnionStart.$sql_start.$sql_distinct.$sql_fields.$sql_from.'uploads up, ' . $user_table . ','.$upp.' upp' . $sql_join.$sql_where.$sqlUnion.$sqlUnionWhere.$sql_order.$sql_limit; //Navi-Link zusammensetzen if (count($navi_data) > 0) { $navi_link .= '?'.implode('&',$navi_data); } //search_feedback bereinigen $search_feedback = substr($search_feedback,0,-2); $search_feedback .= ($search_feedback != '') ? '' : '<'.GAL_NEWPICS.'>'; } ?>