Solved

codeigniter query  returning too many results

Posted on 2016-10-29
3
41 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 25

Expert Comment

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

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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
In this Micro Tutorial viewers will learn how to restore their server from Bare Metal Backup image created with Windows Server Backup feature. As an example Windows 2012R2 is used.
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…

864 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

20 Experts available now in Live!

Get 1:1 Help Now