CODEWITHSUNDEEP
phppdo
Preeti Bisht
Preeti Bisht

Reputation: 79

how to fetch data using pdo and php

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.

Upvotes: 2

Views: 12603

Answers (3)

user1685883
user1685883

Reputation: 74

    show all column name in bind result
$result = $conn->prepare('SELECT * FROM users WHERE user_email = ? and user_password = ? ');
$result->bind_param('ss', $email, $password);
$result->execute();
$result->bind_result($userid,$fullname,$useremail,$userpassword,$userimage);                
        $array = array();               
        while($result->fetch()) 
        {
            $array['id'] = $userid;
            $array['fullname'] = $fullname;
            $array['email'] = $useremail;
            $array['picture'] = $userimage;             
        }

        print_r($array);

Upvotes: 0

Your Common Sense
Your Common Sense

Reputation: 158007

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);

Upvotes: 1

Madara's Ghost
Madara's Ghost

Reputation: 175098

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;
        }
}

Upvotes: 1

Related Questions