CODEWITHSUNDEEP
mysqlcodeigniterwhere-clause
Avinash
Avinash

Reputation: 2005

Codeigniter mysql where not equal to query

Mysql codeigniter query is not working properly. Suppose if mysql table looks like this:

 user_id|user_name
       1|john
       2|alex
       3|sam

Here user_name is unique

The following query should return false if user_name=john and user_id=1 and true if say user_name=john and user_id=2.

$this->db->get_where('user', array('user_name' => $name,'user_id !=' => $userid));

But it returns true in the case user_name=john and user_id=1.

Can anyone suggest me an alternative way of querying not equal to.

print($this->db->last_query()) gives:

SELECT * FROM (user) WHERE user_name = 'john' AND user_id != '1'

Upvotes: 6

Views: 39509

Answers (6)

Ameen Maheen
Ameen Maheen

Reputation: 2747

Simply try this, Add the desired condition in the where function.

$this -> db -> where('invitee_phone !=', $user_phone);

Upvotes: 3

mickmackusa
mickmackusa

Reputation: 47894

The problem with using $this->db->query('your query'); is that it is not portable. One of the most important reasons to embrace the query builder methods is so that no matter what database driver you use, CodeIgniter ensures that the syntax is appropriate.

If a bit of discussion was possible, I'd probably like to hear why you need composite primary identifiers in your table and I'd like to see what your table schema looks like. However, I think the time for discussion has long passed.

Effectively, you want to return a boolean result stating the availability of the combination of the username AND the id -- if one is matched, but not both, then true (available).

To achieve this, you will want to search the table for an exact matching row with both qualifying conditions, count the rows, convert that integer to a boolean, then return the opposite value (the syntax is simpler than the explanation).

Consider this clean, direct, and portable one-liner.

return !$this->db->where(['user_name' => $name,'user_id' => $userid])->count_all_results('user');

this will return false if the count is > 0 and true if the count is 0.

Upvotes: 0

Gaurav
Gaurav

Reputation: 721

you can follow this code:

$query = $this->db->select('*')->from('employee')->where('user_name', $name)->where('user_id !=', $userid)->get();
$last_query = $this->db->last_query();
$result = $query->result_array();

if you pass $name = 'john' and $userid = '1' then it return empty array.

Upvotes: 0

Aravindh Gopi
Aravindh Gopi

Reputation: 2166

Type 1:

Using ->where("column_name !=",$columnname) is fine for one column.

But if you want to check multi columns, you have to form an array inside where clause.

Like this 

$whereArray = array(
                "employee_name" => $name,
                "employee_id !=" => $id,
        );

$this->db->select('*')->from('employee')->where($whereArray);

Type 2:

We can just write exactly what we want inside where.

Like

$thi->db->where(("employee_id =1 AND employee name != 'Gopi') OR designation_name='leader@gopis clan'");

Type 2 is good for working with combining queries, i mean paranthesis "()"

Upvotes: 1

Sachin
Sachin

Reputation: 48

You can go follwoing way too. It work for me 

$total = 5;
$CI = get_instance();
$CI->load->database();
$CI->db->order_by('id','asc');
$topusers = $CI->db->get_where('users',array('user_type != 1  && `status` =' => 1),$total,0);
echo $CI ->db ->last_query();
die;

and if still not work for you can go with @rohit suggest: $this->db->query('your query');

Upvotes: 1

rohitarora
rohitarora

Reputation: 1365

Why dont you use simple $this->db->query('your query');

Upvotes: 3

Related Questions