Searching with serialized id's

When the user types in the search PHP it should find the questions that have category id of 1

I store my category id in a column in question called "tags" it's serialized of couse because the user may have chosen multiple tags / categories.

The category table name is category and columns "category_id" "name" "status"

a:2:{i:0;s:1:"1";i:1;s:1:"4";}

I can search for questions fine but cannot search if it is a category they are searching for.

Any idea's and examples thanks for your time

PHP Code:

public function get_questions($search) {
    $this->db->select('*');
    $this->db->from($this->db->dbprefix . 'question');
    $this->db->like('title', $search);
    $query1 = $this->db->get();

    if ($query->num_rows() > 0) {

        foreach ($query->result_array() as $key) {
            $data[] = array(
                'question_id' => $key['question_id'],
                'title' => $key['title'],
                'votes' => $key['votes'],
                'answers' => $this->answer_model->count_answers($key['question_id']),
                'views' => ''
            );
        }
    }

    return $data;
} 


Controller

PHP Code:

<?php 

class Search extends MY_Controller {

    public function __construct() {
        parent::__construct();
        $this->load->library('user_agent');

        if ($this->input->get('q') == FALSE) {
            redirect($this->agent->referrer());
        }

        $this->load->model('answers/answer_model');
    }

    public function index() {
        if ($this->input->post('search')) {

            $q = str_replace('+',  ' ', $this->input->get('q'));

            $data['title'] = 'Questions ' . "'" . $q . "'";

            $question_results = $this->get_questions($q);

            $data['questions'] = array();

            if (isset($question_results)) {
                foreach ($results as $result) {
                    $data['questions'][] = array(
                        'question_id' => $result['question_id'],
                        'title' => $result['title'],
                        'votes' => $result['votes'],
                        'answers' => $this->answer_model->count_answers($result['question_id']),
                        'views' => ''
                    ); 
                }
            }

            $data['page'] = 'search/search_results';

            $this->load->view($this->config->item('config_template') . '/template/template_view', $data);

        }
    }
} 


Attached Files

  Search.php (Size: 1.59 KB / Downloads: 60)

There's only one rule - please don't tell anyone to go and read the manual.  Sometimes the manual just SUCKS!

If you want it to be searchable, don't serialize it.

If you want it to be searchable, don't serialize it.

What would you recommend.

There's only one rule - please don't tell anyone to go and read the manual.  Sometimes the manual just SUCKS!

You need a one-to-many relationship with two tables, one for questions, one for categories/tags. Then use a table join to select the tags based on the question id.

So your DB might be for example:

Code:

question
_____________
id    title...
1     The title here
2     Another title

category
_____________
id    question_id            name
1     1                 My Category
2     1                 My Second Category
3     2                 A Third One

You need a one-to-many relationship with two tables, one for questions, one for categories/tags. Then use a table join to select the tags based on the question id.

So your DB might be for example:

Code:

question
_____________
id    title...
1     The title here
2     Another title

category
_____________
id    question_id            name
1     1                 My Category
2     1                 My Second Category
3     2                 A Third One

Thank you I will look in to it tonight.  Am trying to get bit more better in my coding.

There's only one rule - please don't tell anyone to go and read the manual.  Sometimes the manual just SUCKS!

Use mongoDb for search, or ElasticSearch is good option