Welcome my friends, many times I hear this question: what's the cleanest way to make an advanced search, many times I have busy to answer.

Today I going to answer this question one time on my blog to share with all my friends, I will split this tutorial into two main sections:

1- How to search in multiple tables clean way

2- How to search with many criteria clean way

1- How to search in multiple tables clean way

Suppose you have a project with many modules, like news, articles, videos and you want to give the visitor the ability to search in these modules SQL tables, how to do this?

You have to use two SQL techniques together (Unions, Views), unions used frequently to combine or collect results from many tables - it should be similar columns to allow Union – and views is another SQL technique to summarize long SQL statements rather than writing it many times, this will be a clean way especially if you have many unions

Step one

Create database with name ‘demo’ and import these tables scheme first

--
-- Database: `demo`
--
CREATE TABLE IF NOT EXISTS `TbJobs` (
  `JobId` int(11) NOT NULL AUTO_INCREMENT,
  `JobTitle` varchar(255) NOT NULL,
  `JobDesc` varchar(255) NOT NULL,
  `JobFile` varchar(255) NOT NULL ,
  `Status` int(11) NOT NULL,
  `Order` int(11) NOT NULL,
  `CreatedAt` int(11) NOT NULL,
  PRIMARY KEY (`JobId`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `TbJobs`
--

INSERT INTO `TbJobs` (`JobId`, `JobTitle`, `JobDesc`, `JobFile`, `Status`, `Order`,  `CreatedAt`) VALUES
  (1, 'doctor', 'simple dec', 'cv.pdf', 1, 1,  1484231349),
  (2, 'developer', 'simple dec', 'cv2.doxc', 1, 2, 1484481322),
  (3,'lawer', 'simple dec', 'mycv.docx', 1, 3,  1484481862);
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `TbPosts` (
  `PostId` int(11) NOT NULL AUTO_INCREMENT,
  `PostTitle` varchar(300) NOT NULL,
  `PostSlug` varchar(255) NOT NULL,
  `PostDesc` text NOT NULL,
  `PostSubject` longtext NOT NULL,
  `PostType` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1=news 2-articles ',
  `CreatedAt` int(11) NOT NULL,
  `CountVisit` int(11) NOT NULL,
  `PostImage` varchar(255) CHARACTER SET latin1 NOT NULL,
  `status` int(11) NOT NULL,
  `order` int(11) NOT NULL,
  PRIMARY KEY (`PostId`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=97 ;
--
-- Dumping data for table `TbPosts`
--
INSERT INTO `TbPosts` (`PostId`, `PostTitle`, `PostDesc`, `PostType`, `CreatedAt`, `CountVisit`, `PostImage`, `status`, `order`) VALUES
(81, 'first article', 'article desc demo', '2', 1484044582, 50, 'image.jpg', 1, 4),
(82, 'first new post', 'new desc demo', '1', 1484044582, 40,'image.png', 1, 4);

-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tbvideos` (
  `VideoId` int(11) NOT NULL AUTO_INCREMENT,
  `VideoName` varchar(150) NOT NULL,
  `PhotoUrl` varchar(255) NOT NULL,
  `status` int(11) NOT NULL,
  `CountVisit` int(11) NOT NULL,
  `Order` int(11) NOT NULL,
  `CreatedAt` int(11) NOT NULL,
  PRIMARY KEY (`VideoId`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='videos table' AUTO_INCREMENT=32 ;

--
-- Dumping data for table `tbvideos`
--

INSERT INTO `tbvideos` (`VideoId`, `VideoName`, `PhotoUrl`, `status`, `CountVisit`, `Order`, `CreatedAt`) VALUES
  (30, 'HYBRID-CAR WASH TUNNEL', 'apJnxEIItXE', 1, 0, 0, 1454837954),
  (31, 'Hoppy''s Handwash Cafe', 'Nb6rIIDg9OI', 1, 0, 0, 1454837977);

Step two

Then create views with name 'searchpaging’ and put Union inside views, to Try this code, copy and paste in PHPMYADMIN and go ahead

CREATE  VIEW `searchpaging` AS SELECT (
  `TbJobs`.`JobId`          AS `id`,
  `TbJobs`.`JobTitle`       AS `name`,
  ifnull(`TbJobs`.`JobDesc`, `TbJobs`.`JobDesc`) AS `descrip`,
  `TbJobs`.`JobFile`        AS `FileName`,
  'jobs/show'               AS `ModuleName`
FROM (`TbJobs`) WHERE (`TbJobs`.`JobTitle` <> '')
UNION

SELECT
        `TbPosts`.`PostId`    AS `id`,
        `TbPosts`.`PostTitle` AS `name`,
        `TbPosts`.`PostDesc`  AS `descrip`,
        `TbPosts`.`PostImage`  AS `FileName`,
        'posts/show'          AS `ModuleName`
      FROM (`TbPosts`) WHERE (`TbPosts`.`PostTitle` <> '')
UNION 

SELECT
        `TbVideos`.`VideoId`   AS `id`,
        `TbVideos`.`VideoName` AS `name`,
        NULL                   AS `descrip`,
        concat('https://img.youtube.com/vi/', `TbVideos`.`PhotoUrl`,
               'default.jpg')  AS `FileName`,
        'videos/all_videos'    AS `ModuleName`
      FROM `TbVideos`WHERE (`TbVideos`.`VideoName` <> '')
      )
--
-- VIEW  `searchpaging`
-- Data: None

You may notice two important tricks in this code

1-First we tell the view to select from five columns (id, name, descrip, FileName, ModuleName) which we will be renamed on the fly in every Union statement by using as a keyword, this trick used to overcome Non-similarity column names.

2-Second we created a static column (ModuleName) for every select statement to save links that point to the targeted module to displays the right result.

Step three

In Codeigniter controller, we will create two functions

Search function to check if input sent and paginate_view function to generate pagination list of the search result also we save the keyword in the session to Highlight the search keyword in the results, you can read more about this library from here

Look to this example here:

// functions in Search controller

    public function search() {

        $this->load->model('search/search_model', 'search');
        $st_name = $this->input->post('st_name');
// save search word in the session to use it with highlight jquery library in results
        $this->session->set_tempdata("search_word","$st_name");

        //  echo 'if no input fields';
        if ($st_name == NULL ) {
            // if no pagination and new empty search redirect
            $this->session->set_flashdata('notice_msg', 'ٍSorry you should use at least One word to Search');
            redirect('search/no_data/');
        } else {
            // drop first - create new - paginate view
            $this->paginate_view();
        }
    }
    function paginate_view($row = 0) {

        // print session
        $this->load->library('pagination');
        $config['base_url'] = base_url() . "search/search/paginate_view";
        $config['total_rows'] = $this->search->search_counter();
        $config['per_page'] = '10';
        $config['uri_segment'] = 4;
        $config['full_tag_open'] = '<ul class="pagination">';
        $config['full_tag_close'] = '</ul>';
        $config['first_link'] = false;
        $config['last_link'] = false;
        $config['first_tag_open'] = '<li>';
        $config['first_tag_close'] = '</li>';
        $config['prev_link'] = '&laquo';
        $config['prev_tag_open'] = '<li class="prev">';
        $config['prev_tag_close'] = '</li>';
        $config['next_link'] = '&raquo';
        $config['next_tag_open'] = '<li>';
        $config['next_tag_close'] = '</li>';
        $config['last_tag_open'] = '<li>';
        $config['last_tag_close'] = '</li>';
        $config['cur_tag_open'] = '<li class="active"><a href="#">';
        $config['cur_tag_close'] = '</a></li>';
        $config['num_tag_open'] = '<li>';
        $config['num_tag_close'] = '</li>';
        $this->pagination->initialize($config);
        $data['row'] = $row;
        $data['total_rows'] = $this->search->search_counter();
        $data['links'] = $this->pagination->create_links();
        $data['result1'] = $this->search->search_view($row);
        $data['no_results'] = '';
        if (!$data['result1']) {
            $data['no_results'] = TRUE;
        } else {
            $data['no_results'] = FALSE;
        }
        $this->view('search/site/results', $data);
    }

In the CodeIgniter model we have also two functions search_view function to search for the keyword from 'searchpaging’ view and search_counter function to count results which we will use in pagination, also you will notice $condition array which we using it to store conditions to search in all columns with the same keyword.

 // put this code into Search model

   function search_view($row)
    {
        $st_name = $this->session->userdata('search_word');

        $conditions = array();

        if (!empty($st_name)) {

            $conditions[] = 'searchpaging.name  LIKE "%' . $st_name . '%"';
            $conditions[] = 'searchpaging.descrip  LIKE "%' . $st_name . '%"';
            $conditions[] = 'searchpaging.FileName  LIKE "%' . $st_name . '%"';
            $sqlStatement = "SELECT * FROM searchpaging WHERE ".implode(' OR ', $conditions)." LIMIT $row,10";
            $result = $this->db->query($sqlStatement)->result_array();
        }else{
            $result = '';
        }

        return $result;
    }

    function search_counter()
    {
        $st_name = $this->session->userdata('search_word');

        $conditions = array();

        if (!empty($st_name)) {

            $conditions[] = 'searchpaging.name  LIKE "%' . $st_name . '%"';
            $conditions[] = 'searchpaging.descrip  LIKE "%' . $st_name . '%"';
            $conditions[] = 'searchpaging.FileName  LIKE "%' . $st_name . '%"';
            $sqlStatement = "SELECT * FROM searchpaging WHERE ".implode(' OR ', $conditions)." ORDER BY id";
            $result = $this->db->query($sqlStatement)->num_rows();
        }else{
            $result = '';
        }

        return $result;
    }

Step Four

You should see the result in your list results.php file, you may notice that we use the session keyword in highlight library to highlight all results that matched the same keyword, and it will be like this

<!DOCTYPE html>
<html lang = "en">

<head>
    <meta charset = "utf-8">
    <title>CodeIgniter Search Example</title>
</head>

<body>
<?php if (!empty($result1)) {?>
    <article class="container search-results">

        <table class="table table-hover">

            <tr>
                <th>#</th>
                <th>Name</th>
                <th>Image</th>
                <th>Description</th>
            </tr>

            <?php
            $i = 0;
            foreach ($result1 as $st): $i++; ?>
                <tr<?php if (($i % 2) == 1) { ?> class="active" <?php } ?>>
                    <td><?= $i ?></td>
                    <td><a href="<?= base_url() ?><?= $st['ModuleName'] ?>/<?= $st['id'] ?>"><?= $st['name'] ?></a> </td>
                    <td>
                        <?php if(!empty($st['FileName'])){?>
                                <img alt="<?= $st['name']; ?>" src="<?= base_url() ?>root/<?=$st['FileName'] ?>" class="media-object img-responsive"  title="<?= $st['name']?>" >
                            <?php  } ?>
                    </td>
                    <td><?= strip_tags(word_split($st['descrip'],25)) ?></td>
                </tr>

            <?php  endforeach; ?>
        </table>

        <table class="table table-bordered table-hover">
            <tr>
                <td colspan="3" class="pager">
                    <div class="resultsCount"><b> Results From <?= $row + 1 ?> To <?= $row + 10 ?>&nbsp;FROM ALL&nbsp;<?= $total_rows; ?> Result</b></div>
                    <!--<div class="pages">-->
                    <?= $links; ?>
                    <!--</div>-->
                </td>
            </tr>
        </table>
    </article>
<?php } else { ?>
    <p class='notice information'>ٍsorry there is no result for this keyword,Try using another</p>
<?php  } ?>
<script src="<?= base_url() ?>/root/js/jquery.highlight.js"></script>
<script>
    $(document).ready(function(){
        $('.search-results').highlight("<?= $search_word['search_word'] ?>");
    });
</script>
</body>

</html>

Final Word

the second tutorial will be about  2- How to search with many criteria clean way, but i like to mention that i like this way to search because it was clean, simple,even if your tables structures changen or added new tables nothing changed in your html or php code, all you do add a few lines to UNION the result in the 'searchpaging’  view, I hope this tutorial help you ,bye