Codeigniter where clause is not working

Question

I'm new to codeigniter I've a function in model with this code

    $this->db->select("id, username, password");
    $this->db->from(TABLE_USERS);
    $where = "username='".$username."' AND password='".$password."' AND status='1'";
    $this->db->where($where);
    $this->db->limit(1);
    $login = $this->db->get();
    echo '<pre>';print_r($login);echo '</pre>';die('Call');

And it is giving this error

A Database Error Occurred

Error Number: 1054

Unknown column 'username='admin'' in 'where clause'

SELECT `id`, `username`, `password` FROM (`users`) WHERE `username='admin'` AND password='7c4a8d09ca3762af61e59520943dc26494f8941b' AND status='1' LIMIT 1

Filename: C:\wamp\www\customadmin\system\database\DB_driver.php

Line Number: 330

after where clause this should be like this

WHERE `username`='admin' AND `password`='7c4a8d09ca3762af61e59520943dc26494f8941b'

instead of this

WHERE `username='admin'` AND password='7c4a8d09ca3762af61e59520943dc26494f8941b'

I've searched every where like Active Record Class and also at stackoverflow CodeIgniter WHERE clause and other forums but didn't find solution related with my problem. Thanks in advance.

Rather than post a blurry screenshot, couldn't you simply add the error message text to your question? — Phil, Jan 08 '14 at 05:17
You can use a third parameter to `where()` to prevent CI from protecting the table names with backtick, so `$this->db->where($where,NULL,FALSE);` should fix it — omma2289, Jan 08 '14 at 05:37

score 6 · Accepted Answer · answered Jan 08 '14 at 05:18

6

Try Like this:

$where = array('username' => $username, 'password' => $password, 'status' => '1');

$this->db->where($where);

answered Jan 08 '14 at 05:18

user2936213

1,021
1
8
19

1

Is that a question or a sentence? `$this->db->where()` accepts an array of `table=>value` pairs. There's no why, that's how it works – Keith Jan 08 '14 at 05:26
2

@Prodikl it accepts a custom string too, the issue in the OP is with the backticks, using `$this->db->where($where,NULL,FALSE);` should fix it – omma2289 Jan 08 '14 at 05:34
Ah, derp. In that case it's the backticks, yup. I posted an answer, too. – Keith Jan 08 '14 at 05:41
how to get proxy ip address using codeigniter??? I know with PHP I've to use $_SERVER['HTTP_X_FORWARDED_FOR'] but in codeigniter what I've to do to grab the proxy ip address??? – user2727841 Jan 08 '14 at 07:31

score 2 · Answer 2 · edited May 23 '17 at 12:02

2

Option 1: You can make a condition string and pass it to where function. but Make sure to add space before and after Equals to "="

same thing mentioned on similar thread here error in codeigniter custom where clause

 $where = "username = '".$username."' AND password = '".$password."' AND status = '1'";
 $this->db->where($where);

Option 2:

Write code like below.

$where = array('username' => $username, 
               'password' => $password, 
               'status'   => '1');
$this->db->where($where);

edited May 23 '17 at 12:02

Community

1
1

answered Jan 08 '14 at 05:45

Trimantra Software Solution

3,223
2
25
44

how to get proxy ip address using codeigniter??? I know with PHP I've to use $_SERVER['HTTP_X_FORWARDED_FOR'] but in codeigniter what I've to do to grab the proxy ip address??? – user2727841 Jan 08 '14 at 07:31
1

I would suggest you to create another question for this. Or look for similar threads on stackoverflow – Trimantra Software Solution Jan 08 '14 at 08:14

score 1 · Answer 3 · answered Jan 08 '14 at 05:38

try below code...

  function get_user(){
    $this->db->select('id, username, password');
            $where = "username=$username AND password=$password AND status='1'";
    $this->db->where($where);
            $this->db->limit(1);
    $query = $this->db->get('YOUR TABLE NAME');
    return $query->row_array();
}

score 1 · Answer 4 · answered Jan 09 '14 at 12:42

You may also try this code.

    $this->db->select("id, username, password");
    $this->db->from(TABLE_USERS);
    $this->db->where('username', $username);
    $this->db->where('password', $password);
    $this->db->where('status', 1);
    $this->db->limit(1);
    $login = $this->db->get();

score 0 · Answer 5 · answered Jan 08 '14 at 05:40

Your column names are wrong, look at this error:

SELECT `id`, `username`, `password` 
FROM (`users`) 
WHERE `username='admin'` 
AND password='7c4a8d09ca3762af61e59520943dc26494f8941b' 
AND status='1' 
LIMIT 1

Line 3. It's looking for a column named

`username='admin'`

Where it should be

`username`='admin' // the tick marks (`) should wrap the column name

Try this:

$where = "`username`='$username' AND `password`='$password' AND `status`=1";
$this->db->where($where);

But honestly the best way is to use an array, or else you risk confusion:

Like this:

$this->db->where(array(
    'username' => $username,
    'password' => $password,
    'status' => 1
));

That will escape your variables for you, too, which is safer.

how to get proxy ip address using codeigniter??? I know with PHP I've to use $_SERVER['HTTP_X_FORWARDED_FOR'] but in codeigniter what I've to do to grab the proxy ip address??? — user2727841, Jan 08 '14 at 07:49

David Yang · Answer 6 · 2018-07-19T04:28:08.520

-1

try like this

$where = "username='".$username."' AND password='".$password."' AND status='1'";

to

$where = "username ='"~~~~~

keep a blank between "first column name" with "operator"

in codeigniter v1.0

edited Jul 19 '18 at 04:28

answered Jul 19 '18 at 01:16

David Yang

1
1

score -2 · Answer 7 · answered Jan 08 '14 at 05:29

-2

Its better to use SHA($password) or MD5() value instead Checking with the Hash value directly This code might work..!! and for the query you can make this replacement username=admin AND password=7c4a8d09ca3762af61e59520943dc26494f8941b AND status='1' LIMIT 1

answered Jan 08 '14 at 05:29

user1830049

109
1
6

What is the relation of `md5` and `SHA` to do with this question? – user2936213 Jan 08 '14 at 05:36
1

The error mentions an incorrect column name; it's not a problem with hashes – Keith Jan 08 '14 at 05:42
how to get proxy ip address using codeigniter??? I know with PHP I've to use $_SERVER['HTTP_X_FORWARDED_FOR'] but in codeigniter what I've to do to grab the proxy ip address??? – user2727841 Jan 08 '14 at 07:48

Codeigniter where clause is not working

7 Answers7