Solved

codeigniter query  returning too many results

Posted on 2016-10-29
3
31 Views
Last Modified: 2016-10-29
I've created a codeigniter query for a search function using the query builder class and it returns too many results. How could I adjust it to get a more specific result set?

My codeigniter query (shortened version):

function get_print_search() {
$match = $this->input->post('search');
$this->db->like('ExhibitionE','Provenance Online');  
$this->db->like('DepartmentE','Jewish Art');
$this->db->like('TitleE',$match);
$this->db->or_like('ArtistE',$match);
$this->db->or_like('ObjectNameE',$match);                  
$query = $this->db->get('combh');
return $query->result();
}

The type of sql query I would like to use:
SELECT TitleE, ArtistE, ObjectNameE FROM combh WHERE (ExhibitionE Like 'Provenance Online' AND DepartmentE Like 'Jewish Art' ) AND (TitleE Like $match OR ArtistE Like $match OR ObjectNameE Like $match)

There seems to be some confusion in the codeigniter query about where the parenthesis separate the query parts in the sql query. The current codeigniter query  returns all the TitleE, ArtistE and ObjectNameE results from  'Provenance Online' and I would like to get just the  'Provenance Online'  results from 'Jewish Art'.
Thanks for any assistance.
0
Comment
Question by:avir
3 Comments
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41865087
Can you give me few rows and the expected output?
0
 
LVL 42

Accepted Solution

by:
Chris Stanyon earned 500 total points
ID: 41865102
Yeah - CodeIgniter doesn't get on with adding parenthesis, so you'll need to build them manually:

$this->db
    ->select("TitleE, ArtistE, ObjectNameE")
    ->from("combh")
    ->like("ExhibitionE","Provenance Online")
    ->like("DepartmentE","Jewish Art")
    ->where("(TitleE LIKE '%$match%' OR ArtistE LIKE '%$match%' OR ObjectNameE LIKE '%$match%')");

return $this->db->get()->result();

Open in new window

1
 

Author Closing Comment

by:avir
ID: 41865112
Worked great. Thanks.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

What to do when Windows Update is not working correctly? What tools can I use to detect the cause of the malfunction problem? What does this numeric error code mean? These and other questions that you have been asking in the past are answered here (…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this Micro Tutorial viewers will learn how they can get their files copied out from their unbootable system without need to use recovery services. As an example non-bootable Windows 2012R2 installation is used which has boot problems.
The viewer will learn how to dynamically set the form action using jQuery.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now