how to fetch data using pdo and php

Question

I want to fetch data using this code:

if(isset($_POST['btnSubmit']))
{
        /* connect using pdo */
        try
        {
            $db_conn = new PDO('mysql:host=localhost; dbname =database','username','password');
        }
        catch (PDOException $e) {
            echo "could not connect to database.";
            exit;
        }
        $password=$_POST['password'];
        $selectSQL = "SELECT fac_id, username, password, type, user_prefix FROM faculty WHERE username = ? AND password = ?";
        $stmt = $db_conn->prepare($selectSQL);
        $stmt->execute(array($_POST['username'],$password));
        while($abc = $stmt->fetch())
        {
            print_r($abc);
        }
        echo $rowCount = count($stmt);
        die;
}

Expected result:

All rows + number of rows.

What I get:

Only number of rows.

Solution 1

Well, you aren't checking for errors (It's very possible that your query fails without you knowing).

To check for errors, set PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION. (That will cause PDO to throw PDOExceptions on errors):

if(isset($_POST['btnSubmit']))
{
        /* connect using pdo */
        try
        {
            $db_conn = new PDO('mysql:host=localhost; dbname =database','username','password');

            $db_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $db_conn->setAttribute(PDO::EMULATE_PREPARES, false); //This will prevent PDO from emulating prepared statements. Solving various edge cases.

        $password=$_POST['password'];
        $selectSQL = "SELECT fac_id, username, password, type, user_prefix FROM faculty WHERE username = ? AND password = ?";
        $stmt = $db_conn->prepare($selectSQL);
        $stmt->execute(array($_POST['username'],$password));
        while($abc = $stmt->fetch())
        {
            print_r($abc);
        }
        echo $rowCount = count($stmt);
        die;
        }
        catch (PDOException $e) {
            echo "There was an error! " . $e->getMessage();
            exit;
        }
}

Solution 2

Ironically, you are doing your fetch all right, but it's count what you're doing wrong.
So, if there is no fetch - there is no data to match.

if(isset($_POST['btnSubmit']))
{

    $dsn = "mysql:host=localhost;dbname=database;charset=utf8";
    $opt = array(
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    );
    $pdo = new PDO($dsn,'username','password', $opt);

    $sql = "SELECT fac_id, username, password, type, user_prefix 
            FROM faculty WHERE username = ? AND password = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array($_POST['username'],$_POST['password']));
    $user = $stmt->fetch();
    var_dump($user);
}

if it says FALSE, you need to debug some
try to check a password for example

    $sql = "SELECT password FROM faculty WHERE username = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array($_POST['username']));
    $pass = $stmt->fetchColumn();
    var_dump($pass, $_POST['password'], $_POST['password'] == $pass);