search_model.php

<?php

    header('content-Type: text/plain');

    function create_sql_taxonomy($table_prefix, $link_table, $taxonomy_table, $terms_table, $taxonomies) {
        $t_count = 1;
        $sql = [];
        $sql_str = '';
        $terms_ = [];
        $terms = [];

        for ($i = 0; $i < count($taxonomies); $i++) {
            for ($j = 0; $j < count($taxonomies[$i]); $j++) {

                $t = $taxonomies[$i][$j];

                switch ($j) {
                    case 0:
                        $logic = $t;
                        break;
                    case 1:
                        $taxonomy = $t;
                        break;
                    case 2:
                        if (is_string($t) && strlen($t) > 0) {
                            $t = explode(',', $t);
                        } else {
                            $t = [];
                        }
                        if (is_array($t) && count($t)) {
                            $terms = $t;
                        } else {
                            $terms = [];
                        }
                        break;
                }
            }

            if (count($terms)) {
                if ($logic == 'AND') {
                    for ($x = 0; $x < count($terms); $x++) {
                        $term = $terms[$x];
                        if (strlen($term)) {
                            $sql_str = "\tJOIN `$table_prefix$link_table` AS `tr$t_count` ON `tr$t_count`.`object_id` = `p`.`id`\r\n";
                            $sql_str .= "\tJOIN `$table_prefix$taxonomy_table` AS `tt$t_count` ON `tt$t_count`.`term_id` = `tr$t_count`.`term_taxonomy_id`\r\n";
                            $sql_str .= "\tJOIN `$table_prefix$terms_table` AS `t$t_count` ON `t$t_count`.`id` = `tt$t_count`.`term_id`\r\n";
                            $sql_str .= "\t\tAND (`tt$t_count`.`taxonomy` = '$taxonomy' AND `t$t_count`.`name` = '$term')\r\n\r\n";
                            $sql[] = $sql_str;
                            $t_count++;
                        }
                    }
                }
                if ($logic == 'OR') {
                    for ($x = 0; $x < count($terms); $x++) {
                        $term = $terms[$x];
                        if (strlen($term))
                            $terms_[$x] = "'{$terms[$x]}'";
                    }
                    $sql_str = "\tJOIN `$table_prefix$link_table` AS `tr$t_count` ON `tr$t_count`.`object_id` = `p`.`id`\r\n";
                    $sql_str .= "\tJOIN `$table_prefix$taxonomy_table` AS `tt$t_count` ON `tt$t_count`.`term_id` = `tr$t_count`.`term_taxonomy_id`\r\n";
                    $sql_str .= "\tJOIN `$table_prefix$terms_table` AS `t$t_count` ON `t$t_count`.`id` = `tt$t_count`.`term_id`\r\n";
                    $sql_str .= "\t\tAND (`tt$t_count`.`taxonomy` = '$taxonomy' AND `t$t_count`.`name` IN (" . implode(',', $terms_) . "))\r\n\r\n";
                    $sql[] = $sql_str;
                    $t_count++;
                }
            }

        }
        return implode(" ", $sql);

    }

    function prepare_sql_like($table, $cells, $words, $logic) {
            $words = strlen($words) ? explode(' ', trim($words)) : [];
            $logic = in_array($logic, ['OR', 'AND']) ? $logic : 'OR';
            $like_expr = [];
            $cells_expr = [];

            if (count($words) > 0) {
                for ($i = 0; $i < count($words); $i++) {
                    $word = trim($words[$i]);
                    if ((strlen($word) > 0) && (count($cells) > 0) && (strlen($table) > 0)) {
                        foreach ($cells as $cell) {
                            $like_expr[$i][] = "`$table`.`$cell` LIKE '%$word%'";
                        }
                        $cells_expr[] = '(' . implode(' OR ', $like_expr[$i]) . ')';
                    }
                }
                $sql_like_str = '(' . implode(" $logic ", $cells_expr) . ')';
            } else {
                $sql_like_str = '';
            }
        return $sql_like_str;
    }

    function prepare_sql_match($table, $cells, $words, $logic) {
            $words = strlen($words) ? explode(' ', trim($words)) : [];
            $logic = in_array($logic, ['OR', 'AND']) ? $logic : 'OR';
            $match_expr = [];
            $cells_expr = [];

            if (count($words) > 0) {
                for ($i = 0; $i < count($words); $i++) {
                    $word = trim($words[$i]);
                    if ((strlen($word) > 0) && (count($cells) > 0) && (strlen($table) > 0)) {
                        foreach ($cells as $cell) {
                            $match_expr[$i][] = "MATCH `$table`.`$cell` AGAINST ('\"$word\"')";
                        }
                        $cells_expr[] = '(' . implode(' OR ', $match_expr[$i]) . ')';
                    }
                }
                $sql_like_str = '(' . implode(" $logic ", $cells_expr) . ')';
            } else {
                $sql_like_str = '';
            }
        return $sql_like_str;
    }

    function create_sql_query($q, $words_in, $words_logic, $search_logic, $category, $tags, $tags_logic, $limit) {
        $taxonomy = create_sql_taxonomy('ex_', 'term_relationships', 'term_taxonomy', 'terms',
            array(
                [$tags_logic, 'post_tag', $tags],
                ['AND', 'category', $category]
                )
            );
        $where = [];
        if ($words_in == 'all') {
            strlen($q) ? array_push($where, prepare_sql_like('p', ['post_title'], $q, $words_logic)) : '';
            strlen($q) ? array_push($where, prepare_sql_match('p', ['post_content'], $q, $words_logic)) : '';
        } else if ($words_in == 'title') {
            strlen($q) ? array_push($where, prepare_sql_like('p', ['post_title'], $q, $words_logic)) : '';
        } else if ($words_in == 'content') {
            strlen($q) ? array_push($where, prepare_sql_match('p', ['post_content'], $q, $words_logic)) : '';
        }

        $where = implode(" $search_logic\r\n\t", $where);
        if (strlen($where)) $where = "WHERE (\r\n\t" . $where . "\r\n\t)";

        $query = "SELECT \r\n\tSQL_CALC_FOUND_ROWS\r\n\t`p`.`id`\r\nFROM `ex_posts` AS `p`\r\n$taxonomy$where \r\nGROUP BY `p`.`id` LIMIT $limit";

        return $query;
    }


    echo create_sql_query('Ключевые слова для поиска', 'all', 'AND', 'OR', 'programming', 'php,mysql,delphi', 'AND', '0, 10');
?>