Query differences

Question

I had problem running a query and counting the results with this code:

$this->db->get_where('user_tb', array('username' => $username, 'password' => $password) );
$count = $this->db->count_all_results();

And it always return 1 even if the username and password are wrong.

Then I changed my code to:

$sql = "SELECT * FROM user_tb WHERE username = ? AND password = ?";
$this->db->query($sql, array($username, $password));
$count = $this->db->count_all_results();

But the result is still the same.

Then my third and last try, I changed the code to:

$this->db->where('username', $username);
$this->db->where('password', $password);
$this->db->from('user_tb');
$count = $this->db->count_all_results();

Then it works. What are the differences between this three? Why is the last set of code works and the other two did not?

score 1 · Answer 1 · edited May 23 '17 at 12:32

1

In the 2nd line, you are using array , but in the 3rd line of your code you used single variable to compare column value thats why its working. To use array in query use IN() operator.

Passing an array to a query using a WHERE clause

edited May 23 '17 at 12:32

Community

1
1

answered Mar 15 '16 at 06:41

Sanket

99
1
1
8

Vinie · Answer 2 · 2016-03-15T07:17:27.217

Its because $this->db->get_where(); and $this->db->query(); executed the query and returned the sql result and ended sql execution. After above two call when you are calling $this->db->count_all_results(); it is independent from above two calls. So it returns 1. and in

$this->db->where('username', $username);
$this->db->where('password', $password);
$this->db->from('user_tb');
$count = $this->db->count_all_results();

Execution is done with above three line query builder. So it is working fine.

Try this to see diffrence

$result = $this->db->get_where('user_tb', array('username' => $username, 
          'password' => $password) );
$count = $this->db->count_all_results();

print_r($result); // you will see it contains all data related to your query.

Without any query when you use $this->db->count_all_results(); which is equivalent to SELECT COUNT(*) AS numrows and will return 1. In your above two cases happening same.

score 0 · Answer 3 · answered Mar 15 '16 at 07:10

Try following code in your model


    class Auth_login extends CI_Model
    {
        public function user_auth($username, $password)
        {
            $q = $this->db->where(['uname'=>$username, 'password'=>$password])
                          ->get('table'); //your table name

            if($q->num_rows())
            {
                return $q->row()->id; //primary key of returned row
            }
            else
            {
                return FALSE;
            }

        }
    }

Add this code to the controller to authenticate user


    $username = $this->input->post('user');
    $password = $this->input->post('pwd');
    $this->load->model('auth_login');
    $login_id = $this->auth_login->user_auth($username, $password);
     if($login_id)
        {
          $this->load->library('session');
          $this->session->set_userdata('id',$login_id);
          return redirect('user/dashboard');
        }                       
     else
        {
            $this->session->set_flashdata('feedback','password not match!');
        }
        return $this->load->view('index_login');

score 0 · Answer 4 · answered Mar 15 '16 at 07:35

0

Hello try this code and it return's number of affected rows

$this->db->get_where('user_tb', array('username' => $username, 'password' => $password) );
$query = $this->db->get();
$rowcount = $query->num_rows();

and let me know it's working or not.

answered Mar 15 '16 at 07:35

Divyesh

389
2
12

Query differences

4 Answers4