combine like and get_where search statements

combine like and get_where search statements - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: combine like and get_where search statements (/showthread.php?tid=12848)

Pages: 1 2

combine like and get_where search statements - El Forum - 11-02-2008

[eluser]Philipp Datz[/eluser]
hello,

im having a problem restricting search results to a specific condition.
my controller-code looks as follws:

$this->db->like('title', $query);
$this->db->or_like('body', $query);
$results_news = $this->db->get('news');

now i have to restrict the results to news, which are published.
simple thing i thought, but combining it with a
$this->db->where('published', 1); statement doesnt work.
neither before the like, nor changing the get to get_where.

is it posible to do this?

with kind regards, philipp

combine like and get_where search statements - El Forum - 11-02-2008

[eluser]Randy Casburn[/eluser]
Hi Philipp,

It would be helpful if we could actually see the SQL that is built up by CI's active record. Can you capture that and post it here?

I'm thinking it's likely you need to use $this->db->or_where(‘published’, 1); instead but I'm not sure.

Thanks,

Randy

combine like and get_where search statements - El Forum - 11-02-2008

[eluser]Randy Casburn[/eluser]
Scratch that...after re-reading, it is certainly an AND condition. Still would like to see the output from Active Record.

combine like and get_where search statements - El Forum - 11-02-2008

[eluser]Philipp Datz[/eluser]
ar gives me no errors, instead i get both for the search results: published und un-published news in the search result. my statements are:

$this->db->like('title', $query);
$this->db->or_like('body', $query);
$this->db->where('published', 1);
$results_news = $this->db->get('news');

i tried also the following:

$this->db->where('published', 1);
$this->db->like('title', $query);
$this->db->or_like('body', $query);
$results_news = $this->db->get('news');

but the result is the same.
i think the problem is that the where clause does not work as a filter but as a addition/alternative

philipp

edit: i also tried the or_where-statement, with the same result.

combine like and get_where search statements - El Forum - 11-02-2008

[eluser]Randy Casburn[/eluser]
And you still did not provide the SQL statement that is built by CI...

combine like and get_where search statements - El Forum - 11-02-2008

[eluser]Philipp Datz[/eluser]
oh sorry but how do i get it ? is there a way to echo/print it?

combine like and get_where search statements - El Forum - 11-02-2008

[eluser]Randy Casburn[/eluser]
Try this...it generally works for folks when they just cannot get AR to work for them..

Write the SQL by hand:

Code:
$sql = "SELECT * FROM news WHERE title LIKE '%{$query}%' AND WHERE body LIKE '%{$query}%' AND WHERE published = 1";

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

$results_news = $query->result_array();

Something close to that should work. It depends on how you are using the results.

Randy

combine like and get_where search statements - El Forum - 11-02-2008

[eluser]Randy Casburn[/eluser]
[quote author="Philipp Datz" date="1225664682"]oh sorry but how do i get it ? is there a way to echo/print it?[/quote]

Yes, you can exectute:

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

and it will echo the query string.

Randy

combine like and get_where search statements - El Forum - 11-02-2008

[eluser]Philipp Datz[/eluser]
now i get an error Wink

A Database Error Occurred

Error Number: 1064

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 'WHERE body LIKE '

combine like and get_where search statements - El Forum - 11-02-2008

[eluser]Randy Casburn[/eluser]
I should have warned you about this sorry. Active Record escapes your data automatically. That's one of the nice features.

You'll have to escaped the variable that is being placed into the query. The little highlighted question mark indicates a character code was used that was likely not recognized by the character set employed along the way (various places.)

So do this:

Code:
$sql = "SELECT * FROM news WHERE title LIKE '%{$this->db->escape($query)}%' AND WHERE body LIKE '%{$this->db->escape($query)}%' AND WHERE published = 1";

That will prevent the error.

If you can, it would be nice to compare this to the query being generated by AR. AR's query will look similar but without the extra WHEREs like this probably:

Code:
SELECT * FROM news 

         WHERE title LIKE '%{$query}%' 

         AND body LIKE '%{$query}%' 

         AND published = 1

Randy