combine like and get_where search statements

[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

[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

[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.

[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.

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

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

[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

[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

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


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 '

[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