Login

11-03-2009, 02:34 PM

[eluser]georgerobbo[/eluser]
Hello.

I have the following query which selects a profile from the database based on keyword/s. However, the problem I am having is if you search for either a first name or surname of a user the database returns relevant results. However if you use both the first name and surname in the query the database returns zero results.

How can I adjust my model so?

Code:
function search($keyword)

    {

        $this->db->select('*');

        $this->db->from('member');

        $this->db->like('ID', $keyword);

        $this->db->or_like('Firstname', $keyword);

        $this->db->or_like('Surname', $keyword);

        $this->db->group_by('ID');

        $query = $this->db->get();

        return $query->result_array();

    }

11-03-2009, 02:49 PM

[eluser]pistolPete[/eluser]
Please post the generated query:

Code:
echo $this->db->last_query();

11-03-2009, 02:54 PM

[eluser]georgerobbo[/eluser]

Code:
SELECT * FROM (`member`) WHERE `ID` LIKE '%lorem ipsum%' OR `Firstname` LIKE '%lorem ipsum%' OR `Surname` LIKE '%lorem ipsum%' GROUP BY `ID`

The user in the database has a first name of Lorem and surname of Ipsum

11-03-2009, 04:12 PM

[eluser]JoostV[/eluser]
You don't get any results because you search for an entire string 'lorem ipsum'. You should search for each separate word in each field

The following code returns a result for 'lorem ipsum' if 'lorem' AND 'ipsum' are present in at least one of the fields. This is how it's done:
1. Store the keywords in an array.
2. Create a search query based on that array

Code:
// Explode words into array, using space as delimiter

$words = explode(' ', html_entities(trim($this->input->post('search'))));

// Set fields that you wish to search

$searchFields = array('ID', 'Firstname', 'Surname');

// Construct query, creating a WHERE clause for every word

foreach ($words as $word) {

    // Set condition for this word

    // E.g. (ID LIKE '%foo%' OR Firstname LIKE '%foo%' OR Surname LIKE '%foo%')

    $where_string = '(';

    foreach ($searchFields as $field) {

        $where_string .= ' OR ' . $field . ' LIKE \'%' . $field . '%\'';

    }

    // Remove first ' OR ' and close parenthesis

    $where_string .= substr($where_string, 4) . ')';

    // Add condition to query

    $this->db->where($where_string);

}

// Finish search query

$this->db->group_by('ID');

$query = $this->db->get();

return $query->result_array();

EDIT: you should add some code that spots if the string does NOT include a space and takes appropriate action.

06-06-2014, 08:55 AM

[eluser]Lykos22[/eluser]
[quote author="JoostV" date="1257289945"]

Code:
// Explode words into array, using space as delimiter

$words = explode(' ', html_entities(trim($this->input->post('search'))));

// Set fields that you wish to search

$searchFields = array('ID', 'Firstname', 'Surname');

// Construct query, creating a WHERE clause for every word

foreach ($words as $word) {

    // Set condition for this word

    // E.g. (ID LIKE '%foo%' OR Firstname LIKE '%foo%' OR Surname LIKE '%foo%')

    $where_string = '(';

    foreach ($searchFields as $field) {

        $where_string .= ' OR ' . $field . ' LIKE \'%' . $field . '%\'';

    }

    // Remove first ' OR ' and close parenthesis

    $where_string .= substr($where_string, 4) . ')';

    // Add condition to query

    $this->db->where($where_string);

}

// Finish search query

$this->db->group_by('ID');

$query = $this->db->get();

return $query->result_array();

[/quote]

@JoostV: I tried to follow your example in my search engine, but doesn't seems to work correct.

this is my code

Code:
// article model

public function search() {

  // separate the words

  $words = preg_split('/[\s]+/', htmlentities(trim($this->input->post('search'))) );

  // set fields that you wish to search

  $search_fields = array('title', 'meta_keywords');

  // build the query

  foreach ($words as $word) {

   // set condition for this word

      $where_str = '(';

   foreach ($search_fields as $field) {

    $where_str .= ' OR ' . $field . ' LIKE \'%' . $word . '%\'';

    // $this->db->or_like($field, $word);

   }

   // remove first ' OR ' and close parenthesis

      $where_str .= substr($where_str, 4) . ')';

      // add condition to query

   // dump($where_str);

      $this->db->where($where_str);

  }

  return $this;

 }

// controller

$this->article->get_published()->search();

$this->data['articles'] = $this->article->get_data();

This is the expected query tested in phpmyadmin

Code:
// expected query (what i want)

SELECT `articles`. * , `categories`.`category_id` , `categories`.`category_name` AS category, `categories`.`slug` AS cat_slug

FROM (

`articles`

)

LEFT JOIN `categories` ON `articles`.`category_id` = `categories`.`category_id`

WHERE `articles`.`pubdate` <= '2014-06-06'

AND `articles`.`visible` =1

AND (

`title` LIKE '%codeigniter,%'

OR `meta_keywords` LIKE '%codeigniter,%'

OR `title` LIKE '%wordpress%'

OR `meta_keywords` LIKE '%wordpress%'

)

ORDER BY `pubdate` DESC , `articles`.`category_id` ASC , `article_id` DESC

Instead I get a sql error

Code:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OR title LIKE '%codeigniter,%' OR meta_keywords LIKE '%codeigniter,%' title LIKE' at line 6

SELECT `articles`.*, `categories`.`category_id`, `categories`.`category_name` AS category, `categories`.`slug` AS cat_slug FROM (`articles`) LEFT JOIN `categories` ON `articles`.`category_id` = `categories`.`category_id` WHERE `articles`.`pubdate` <= '2014-06-06' AND `articles`.`visible` = 1 AND ( OR title LIKE '%codeigniter,%' OR meta_keywords LIKE '%codeigniter,%' title LIKE '%codeigniter,%' OR meta_keywords LIKE '%codeigniter,%') AND ( OR title LIKE '%wordpress%' OR meta_keywords LIKE '%wordpress%' title LIKE '%wordpress%' OR meta_keywords LIKE '%wordpress%') ORDER BY `pubdate` DESC, `articles`.`category_id` ASC, `article_id` DESC

// line 6 reffers to this: OR title LIKE '%codeigniter,%'

06-08-2014, 02:05 PM

[eluser]JoostV[/eluser]
@Lykos22 : The error is here:

Code:
// set condition for this word

$where_str = '(';

foreach ($search_fields as $field) {

    $where_str .= ' OR ' . $field . ' LIKE \'%' . $word . '%\'';

    // $this->db->or_like($field, $word);

}

// remove first ' OR ' and close parenthesis

$where_str .= substr($where_str, 4) . ')';

When you do $where_str .= substr($where_str, 4) . ')'; you are removing the first parenthesis as well. Try this:

Code:
$where_str = '';

foreach ($search_fields as $field) {

    $where_str .= ' OR ' . $field . ' LIKE \'%' . $word . '%\'';

}

// Wrap in parentheses and remove first OR

$where_str = '(' . ltrim($where_str, ' OR ') . ')';

06-09-2014, 01:31 AM

[eluser]Lykos22[/eluser]
[quote author="JoostV" date="1402261555"]@Lykos22 : The error is here:

Code:
When you do $where_str .= substr($where_str, 4) . ')'; you are removing the first  parenthesis as well. Try this:

[code]

$where_str = '';

foreach ($search_fields as $field) {

    $where_str .= ' OR ' . $field . ' LIKE \'%' . $word . '%\'';

}

// Wrap in parentheses and remove first OR

$where_str = '(' . ltrim($where_str, ' OR ') . ')';

[/quote]

@JoostV: I tried the code you posted, but still getting error

Code:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server 

version for the right syntax to use near 'OR `title` LIKE '%codeigniter%' OR `meta_keywords` 

LIKE '%codeigniter%' OR `titl' at line 6

SELECT `articles`.*, `categories`.`category_id`, `categories`.`category_name` AS category, `categories`.`slug` AS cat_slug 

FROM (`articles`) LEFT JOIN `categories` 

ON `articles`.`category_id` = `categories`.`category_id`

 WHERE `articles`.`pubdate` <= '2014-06-09' 

AND `articles`.`visible` = 1 

AND 

(( OR `title` LIKE '%codeigniter%' 

OR `meta_keywords` LIKE '%codeigniter%' 

OR `title` LIKE '%tips%' 

OR `meta_keywords` LIKE '%tips%' ) 

ORDER BY `pubdate` DESC, `articles`.`category_id` ASC, `article_id` DESC

However I found out a way to do this, which fetches the correct results:

Code:
// build the query

     $where_str = '(';

  $like_str = '';

  foreach ($words as $word) {   

   // set condition for this word

   foreach ($search_fields as $field) {

    $like_str .= ' OR `' . $field . '` LIKE \'%' . $word . '%\' ';

   }

  }

  // remove first ' OR ' and close parenthesis

     $where_str .= substr($like_str, 4);

     $where_str .= ')';

// which generates the expected query:

SELECT `articles`.*, `categories`.`category_id`, `categories`.`category_name`

FROM (`articles`)

LEFT JOIN `categories` ON `articles`.`category_id` = `categories`.`category_id`

WHERE `articles`.`pubdate` <= '2014-06-06'

AND `posts`.`visible` =  1

// this is where the query changes

AND  

( `title`  LIKE '%Lorem,%'

OR  `keywords`  LIKE '%Lorem,%'

OR  `title`  LIKE '%Ipsum%'

OR  `keywords`  LIKE '%Ipsum%' )

ORDER BY `pubdate` DESC, `articles`.`category_id` ASC, `article_id` DESC

I have one question though. How can I paginate the results?

To be more specific when I click the link to get me to the othe pages, the query changes and return all data from database (not those with matching keywords).

Well I thought to put the keywords as parameters on the url and get them for the other pages, but the url will be dynamic and can't define the $config['uri_segment'] in pagination, based on its length.