• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Query not following rules!!

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

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

#13
[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

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?

#14
[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?

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

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

Using HAVING inplace of WHERE works.

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

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

#18
[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/31597...-of-having

#19
[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/31597...-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/...re-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).

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


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


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