Query not following rules!! - Printable Version

Query not following rules!! - 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: Query not following rules!! (/showthread.php?tid=54422)

Pages: 1 2

Query not following rules!! - El Forum - 09-10-2012

[eluser]codemonkey[/eluser]
Yea it works when commenting out the OR_LIKE.

This is why I was thinking the OR_LIKE/LIKE was overriding the WHERE clause, if that's even possible.

I checked the user guide and I don't see any other way to format this.

I tried putting the WHERE in different places as well, before and after the LIKE, no difference.

Thanks and I know you are trying to help.

Query not following rules!! - El Forum - 09-10-2012

[eluser]TWP Marketing[/eluser]
[quote author="codemonkey" date="1347049651"]

Code:
$this->db->where('added_by', $tech);

$this->db->like('ticket_num','match');

$this->db->or_like('part_num','match')

$this->db->or_like('make','match');

$this->db->or_like('model','match');;

$this->db->or_like('user_first_name','match');

$this->db->or_like('user_last_name','match');

$this->db->or_like('reason','match');

$this->db->or_like('date_created','match');

$this->db->limit(100);

$this->db->order_by('date_created', 'DESC');

$query = $this->db->get('item_history');

return $query->result();

Code:
AND  `ticket_num`  LIKE '%%'

OR  `serial_num`  LIKE '%%'

OR  `part_num`  LIKE '%%'

OR  `make`  LIKE '%%'

OR  `model`  LIKE '%%'

OR  `user_first_name`  LIKE '%%'

OR  `user_last_name`  LIKE '%%'

OR  `reason`  LIKE '%%'

OR  `date_created`  LIKE '%%'

ORDER BY `date_created` DESC

LIMIT 100

Thank you[/quote]

I see two things. First, the SQL query string contains a LIKE string of '%%' where it should contain '%match%'. That tells me that your original POST to set the value of $match is not returning the string 'match':

Code:
$match = $this->input->post('search');

Second, Note the top code block, above, where I hand coded the match string as 'match' for each OR_LIKE clause.
If you try that, what happens?
If it fails, then comment out all but one OR_LIKE clause and test it again.

Query not following rules!! - El Forum - 09-10-2012

[eluser]codemonkey[/eluser]
The empty strings are from me not putting in a search term, which returns everything, which works as intended.

With a search term:

Code:
SELECT *

FROM (`item_history`)

WHERE `added_by` =  'tech1'

AND  `ticket_num`  LIKE '%tom%'

OR  `serial_num`  LIKE '%tom%'

OR  `part_num`  LIKE '%tom%'

OR  `make`  LIKE '%tom%'

OR  `model`  LIKE '%tom%'

OR  `user_first_name`  LIKE '%tom%'

OR  `user_last_name`  LIKE '%tom%'

OR  `reason`  LIKE '%tom%'

OR  `date_created`  LIKE '%tom%'

ORDER BY `date_created` DESC

LIMIT 100

Query not following rules!! - El Forum - 09-10-2012

[eluser]codemonkey[/eluser]
Okay, I think I found an issue. It seems having the blank search string, while worked fine before, doesn't work with this implementation.

What I suppose I'll need to do is put in a IF check to check if the search string is empty and run a different search query to return everything with the added_by by the logged in tech.

Thoughts?

Query not following rules!! - El Forum - 09-10-2012

[eluser]codemonkey[/eluser]
[quote author="codemonkey" date="1347289658"]Okay, I think I found an issue. It seems having the blank search string, while worked fine before, doesn't work with this implementation.

What I suppose I'll need to do is put in a IF check to check if the search string is empty and run a different search query to return everything with the added_by by the logged in tech.

Thoughts? [/quote]

Actually this does not work... Let re-break this down again.

Query not following rules!! - El Forum - 09-10-2012

[eluser]codemonkey[/eluser]
Holy *%*& raptor-jesus

Using HAVING inplace of WHERE works.

If someone agrees on this being ok, then I'm done.

Query not following rules!! - El Forum - 09-10-2012

[eluser]TWP Marketing[/eluser]
[quote author="codemonkey" date="1347288270"]The empty strings are from me not putting in a search term, which returns everything, which works as intended.

With a search term:

Code:
SELECT *

FROM (`item_history`)

WHERE `added_by` =  'tech1'

AND  `ticket_num`  LIKE '%tom%'

OR  `serial_num`  LIKE '%tom%'

OR  `part_num`  LIKE '%tom%'

OR  `make`  LIKE '%tom%'

OR  `model`  LIKE '%tom%'

OR  `user_first_name`  LIKE '%tom%'

OR  `user_last_name`  LIKE '%tom%'

OR  `reason`  LIKE '%tom%'

OR  `date_created`  LIKE '%tom%'

ORDER BY `date_created` DESC

LIMIT 100

For the second part, your example is actually how I had the code layed out before putting it all into a array, I'm telling you, I tried everything before posting.

I changed it back to match your example and it still did not work. I removed the other OR_LIKEs and left just one of them and it tested working.

So, does that mean OR_LIKE and WHERE don't play nice together?[/quote]

So you left the first LIKE and the first OR_LIKE and it works? or just the first LIKE works?
If one OR_LIKE works, then logically, they all should, except they don't... Hmmm!

RE using HAVING instead of WHERE. If it works, go with it, although it would be nice to know why WHERE doesn't work here...

Query not following rules!! - El Forum - 09-10-2012

[eluser]codemonkey[/eluser]

Quote:So you left the first LIKE and the first OR_LIKE and it works? or just the first LIKE works?
If one OR_LIKE works, then logically, they all should, except they don't... Hmmm!

The first LIKE did work but once the OR_LIKE was added it broke again.

Quote:RE using HAVING instead of WHERE. If it works, go with it, although it would be nice to know why WHERE doesn't work here...

Yep, I agree. Knowing why this is happening would be great.

Maybe this kindof explains it
http://stackoverflow.com/questions/3159739/sql-using-where-and-instead-of-having

Query not following rules!! - El Forum - 09-10-2012

[eluser]TWP Marketing[/eluser]

Quote:RE using HAVING instead of WHERE. If it works, go with it, although it would be nice to know why WHERE doesn't work here...

Yep, I agree. Knowing why this is happening would be great.

Maybe this kind of explains it
http://stackoverflow.com/questions/3159739/sql-using-where-and-instead-of-having

As I read that stackoverflow thread, it looks like HAVING was put in as kludge to make something work. It can apparently be used with WHERE in the same statement but the scope is narrow. I"m out of my depth here, as I seldom use statements that complex.
There was a further link:

http://blog.sqlauthority.com/2007/07/04/sql-server-definition-comparison-and-difference-between-having-and-where-clause/
which give more detail about HAVING vs WHERE:

Quote:Answer in one line is : HAVING specifies a search condition for a group or an aggregate function used in SELECT statement.

HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

Which is why HAVING is working here, but doesn't explain why WHERE fails with OR_LIKE. I have no proof, but it may be that CI's implementation is at fault (grasping for straws here).

Query not following rules!! - El Forum - 09-10-2012

[eluser]codemonkey[/eluser]
Well, either way, thanks for the help.