what is wrong in my code that the row count method is not returning the affected row?

Question

I am working on login form using PDO. I have used fetch method to return the user data from database. And then use rowCount method to count the affected row and store it in count variable. But the count variable still remains zero and jump to else part of my code.

This is the login function



public function login($email, $password){
    $password1 = md5($password);
    $stmt = $this->pdo->prepare("SELECT 'user_id' FROM 'users' WHERE 'email' = 
    :email AND 'password' = :password");

    $stmt->bindparam(":email", $email, PDO::PARAM_STR);
    $stmt->bindparam(":password", $password1, PDO::PARAM_STR);
    $stmt->execute();

    $user = $stmt->fetch(PDO::FETCH_OBJ);
    $count = $stmt->rowCount();

    if ($count > 0) {
    $_SESSION['user_id'] = $user->user_id;
    header('Location: home.php');
    }else{
    return false;
    }
}

I expect to go to the home.php which I have defined in if statement.

I don't think `rowCount()` works with MYSQL. Try `fetchColumn()` instead. — rpm192, Apr 21 '19 at 09:43
Possible duplicate of [When to use single quotes, double quotes, and back ticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-back-ticks-in-mysql) — sticky bit, Apr 21 '19 at 09:59

Zain Farooq · Answer 1 · 2019-04-21T11:05:02.587

For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement. Instead, use PDO::query() to issue a SELECT COUNT(*) statement with the same predicates as your intended SELECT statement, then use PDOStatement::fetchColumn() to retrieve the number of rows that will be returned. Your application can then perform the correct action

public function login($email, $password){
$password1 = md5($password);
$stmt = $this->pdo->prepare("SELECT 'user_id' FROM 'users' WHERE 'email' = 
:email AND 'password' = :password");

$stmt->bindparam(":email", $email, PDO::PARAM_STR);
$stmt->bindparam(":password", $password1, PDO::PARAM_STR);
$stmt->execute();

$user = $stmt->fetch(PDO::FETCH_OBJ);
$count = $stmt->fetchColumn();//change here

if ($count > 0) {
$_SESSION['user_id'] = $user->user_id;
header('Location: home.php');
}else{
return false;
 }
}

would you please show me how to write that. I am new in php. — R.hood, Apr 21 '19 at 11:02

what is wrong in my code that the row count method is not returning the affected row?

1 Answers1