• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Database andlike()

#1
[eluser]Hyra[/eluser]
Hey hey,

Im building a query which does a few joins and has a couple of where's.
Works fine.

Now I want to add "search" functionality so am playing with the $this->db->like and orlike

But I want to search on at least 2 fields, and "orlike" will then break the query as it's standalone, making the previous where's obsolete.

The query produced (simplified):

Code:
SELECT
     Field1, Field2
FROM
     TableA
JOIN
     TableB
          ON TableA.SomeID = TableB.SomeID
JOIN
     TableC
          ON TableA.OtherID = TableC.OtherID
WHERE
     TableB.TextField = 'condition1'
AND
     TableC.OtherField = 'condition2'
// here the problem starts
AND
     TableA.Title LIKE '%search%'
OR
     TableB.Description LIKE '%search%'
ORDER BY
     TableA.InsertionDate DESC
LIMIT 30

What i actually want is the where's to be encapsulated by ( )'s, so it would be like:

Code:
AND ( TableA.Title LIKE '%search%' OR TableB.Description LIKE '%search%' )

Anyone know how to achieve this?
I'm using method chaining, so every method adds another where, and the final method should add the like's

Code:
function getBySearch($search = "") {
     if($search != "") {
          $this->db->like('TableA.Title', $search);
          // adding this line breaks the other where's
          $this->db->orlike('TableA.Description', $search);
     }
}

#2
[eluser]Armchair Samurai[/eluser]
You'll have to do something like this:
Code:
$search = $this->db->escape("%$search%");

//AR stuff here, then...

$this->db->where("(TableA.Title LIKE $search OR TableB.Description LIKE $search)");

//Finish off query.

#3
[eluser]Hyra[/eluser]
Ah!

Didn't know you could just put a full condition into the where() param

Niceness, works like a charm.

Cheers Smile


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.