[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();
}
[eluser]pistolPete[/eluser]
Please post the generated query:
Code: echo $this->db->last_query();
[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
[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.
[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,%'
[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 ') . ')';
[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.
|