mysqli execution failing when using bind parameters

Question

I have a mysql query that I am using to log into the database but for some reason it is returning -1 even though i put in the right values and the same query executes without the bind_param

$username = $_POST['username'];
$password = md5($_POST['password']);
$user_array = array('username' => $username,'password' => $password );  
$queryname = "SELECT username, password FROM user WHERE username = ? AND password = ?";

function auth($queryname,$user_array) {
        $stmt = $this->connection->prepare($queryname);
        $stmt->bind_param('ss', $user, $pass);  
        $user=$user_array['username'];
        $pass=$user_array['password'];
        $stmt->execute();       
        return $stmt->affected_rows;        
    }

Any suggestions as to how i can debug this ?

From the manual ~ "-1 indicates that the query has returned an error". Also, a SELECT query is never going to result in affected_rows — Phil
– Phil, Commented Sep 5, 2014 at 6:11
@Phil could there be anything wrong in my bind_param i am using an md5 hashed text, which is stored as varchar — Bazinga777
– Bazinga777, Commented Sep 5, 2014 at 6:12
Run this before you create your connection mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); and watch the errors come rolling in ;) — Phil
– Phil, Commented Sep 5, 2014 at 6:12
Thanks, though my solution was answered my someone else, i now know of a better way of debugging mysqli — Bazinga777
– Bazinga777, Commented Sep 5, 2014 at 6:20

Rimas · Accepted Answer · 2014-09-05 06:23:28Z

1

$stmt->affected_rows returns the total number of rows changed, deleted, or inserted:

This function only works with queries which update a table. In order to get the number of rows from a SELECT query, use mysqli_stmt_num_rows() instead.

Change return $stmt->affected_rows; to:

$stmt->store_result();
$count = $stmt->num_rows;
$stmt->close();
return $count;

edited Sep 5, 2014 at 6:23

answered Sep 5, 2014 at 6:16

Rimas

6,0332 gold badges28 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rimas Over a year ago

@Bazinga777 also you must close $stmt: see modified code

Collectives™ on Stack Overflow

mysqli execution failing when using bind parameters

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related