combine like and get_where search statements

[eluser]Developer13[/eluser]
[quote author="Randy Casburn" date="1225668257"]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.[/quote]

Huh? Why do you keep sticking WHERE's all over the query? Only one is needed. That's why all of the examples you give him keep crashing.

[eluser]Randy Casburn[/eluser]
No, they the single one didn't work because I took him out of Active Record without warning him to escape his query. I made a poor assumption there. I then corrected that assumption since I realized the sting had not be escaped.

The essence of fault isolation is to start at square one. We're starting at the most basic SELECT statement there is. It's that simple.

Thanks for your concern.

Randy

[eluser]Developer13[/eluser]
Please go and try to run a query via command line or phpMyAdmin or whatever that includes multiple WHERE keywords in a non-nested query. It won't work.

[eluser]Randy Casburn[/eluser]
OK... :red:

My query analyzer strips um...you are correct my friend, I've made a mistake. (damn it! But I know you'll go rejoice with your merry band of positive well wishers)

@Philipp - Sorry for misleading you with an inaccurate query. My friend Developer13 has discovered an error that I've made. Please change the query to:

Code:

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

@Develper13 - thanks for the help.

Randy

[eluser]Developer13[/eluser]
[quote author="Randy Casburn" date="1225670397"]@Develper13 - thanks for the help.[/quote]

Absolutely.

[eluser]Randy Casburn[/eluser]
[quote author="Developer13" date="1225670476"][quote author="Randy Casburn" date="1225670397"]@Develper13 - thanks for the help.[/quote]

Absolutely.[/quote]

This is an example of tools going to far to "help" some times.

By my query analyzer stripping, I meant it would actually allow me run this stupid query with all these SELECTs, but it was removing them on the fly. Rather than say "hey stupid, you're violating the syntax".

I wonder how else it has 'helped' me! Time to dump it and go back to my brain I think.

Randy

[eluser]Philipp Datz[/eluser]
firstly thank you all so much for your qualified help.
but now, after working on another project a couple of days i came back to the problem, copied the last version of the query into the controller an still getting an error message:


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 'dekora'%' AND body LIKE '%'dekora'%' AND published = 1' at line
SELECT * FROM news WHERE title LIKE '%'dekora'%' AND body LIKE '%'dekora'%' AND published = 1

have i misconfigured something?

philipp

[eluser]Philipp Datz[/eluser]
should say it is working now changed the query to
$sql = "SELECT * FROM `news` WHERE `title` LIKE '%$this->db->escape($query)%' AND `body` LIKE '%$this->db->escape($query)%' AND `published` = 1";

which has the variables in the string, nasty but works.
thanks you all again very much.

-philipp