Query not following rules!!

[eluser]codemonkey[/eluser]
Hello,

Here is the query in question

Code:

$this->db->where('added_by', $tech);
$this->db->or_like($infoarray);
$this->db->limit(100);
$this->db->order_by('date_created', 'DESC');
$query = $this->db->get('item_history');
return $query->result();

Why doesn't the WHERE work? I want to limit the results based on the tech logged in.

Code:

$tech = $_SESSION['username'];
$match = $this->input->post('search');  
$infoarray = array(
'ticket_num' => $match,
'serial_num' => $match,
'part_num' => $match,
'make' => $match,
'model' => $match,
'user_first_name' => $match,
'user_last_name' => $match,
'reason' => $match,
'date_created' => $match
);

From profiler

Code:

SELECT *
FROM (`item_history`)
WHERE `added_by` =  'tech1'
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

[eluser]PhilTem[/eluser]
I guess this is just half or at least just some part of your code. I cannot see where $tech is defined at so I cannot give any help.

So please, post more of your code.


Thank you

[eluser]codemonkey[/eluser]
Post updated, thank you for looking.

[eluser]TWP Marketing[/eluser]
Do you know that the value of $tech is valid?
Try echoing it in the view to check

[eluser]codemonkey[/eluser]
It is, I use the same thought process and variable for other queries that don't have LIKE/OR_LIKE and they work fine.

I'm thinking the OR_LIKE is overriding the WHERE. Not sure though. I need the OR_LIKE or the query does not work.

Maybe another way to do it?

Thanks

EDIT: I just did a ECHO test and it worked, displayed the logged in user's name that also matches the DB value.

[eluser]TWP Marketing[/eluser]
How about the POSTed value of 'search' which you store in var $match.
What kind of var is it? boolean? string? I don't follow the logic of what your query requests unless it it boolean...

[eluser]codemonkey[/eluser]
String I suppose. The POST value is fine, the query works except the WHERE.

Here is the work flow:

A tech (named tech1) logs in -> goes to search box and types HP -> the result should display only computers matching HP and that have been submitted by the the logged in tech, tech1.

What happens, is everything is returned matching the search term reguardless of the WHERE.

In the profiler box, you can see the query being executed. In the posted example, no term has been entered returning everything, which is fine since putting in a term gives the same overall result.

[eluser]TWP Marketing[/eluser]
Reading through the user guide:
http://ellislab.com/codeigniter/user-gui...tml#select

It looks like your $match var should contain the string 'match'.
I don't know that is the case because the value of $match is whatever is POSTed from your form, which we haven't seen.
What is the value parameter of the 'search' input field on your form? What does it return via POST?

[eluser]codemonkey[/eluser]
I fail to see the relevance of the POST value when it has nothing to do whith the WHERE in question.

I can hard set the $match varible and still have the same issue.

[eluser]TWP Marketing[/eluser]
Just exploring options. I don't see anything that jumps out at me, so...

If you comment out the or_like condition, do you get all records for 'tech1', subject to the limit of 100?

Code:

$this->db->where('added_by', $tech);
//$this->db->or_like($infoarray);
$this->db->limit(100);
$this->db->order_by('date_created', 'DESC');
$query = $this->db->get('item_history');
return $query->result();