What is the equivalent of bind_result on PDO

Question

I'm converting to PDO and Im using prepared statements, I want to bind my result as so $stmt->bind_result($email_count); so i am able to put this into an if statement to see if the e-mail exists however I am getting the error Fatal error: Call to undefined method PDOStatement::bind_result() in /Applications/XAMPP/xamppfiles/htdocs/imanage/insert.php on line 51 which relates to the previous example.

I'm guessing bind_result is not a PDO defined method, so is there an equivalent I could use?

My code is below in case it helps:

insert.php

<?php

 include("connect/class.Database.php");

 class Users extends Database {

     public function insert() {

            $stmt = $this->pdo->prepare("SELECT COUNT(*) FROM users WHERE email=:email");
            $stmt->bindParam(":email", $_POST['email']);
            $stmt->bind_result($email_count);
            $stmt->execute();
            $stmt->fetch(PDO::FETCH_ASSOC);

                    if ($email_count > 0) {
                        echo "email exisits! click here to try <a href='register'>again</a>";
                        } else {
                            //escape the POST data for added protection
                            $username = isset($_POST['username']) ? $_POST['username'] : null;
                            $cryptedPassword = crypt($_POST['password']);
                            $password = $cryptedPassword;
                            $name = isset($_POST['name']) ? $_POST['name'] : null;
                            $email = isset($_POST['email']) ? $_POST['email'] : null;

                            $data = array($username, $password, $name, $email); 
                            $stmta = $this->pdo->prepare("INSERT INTO users (username, password, name, email) VALUES (?, ?, ?, ?)");
                            $stmta->execute($data);

                                printf("%d Row inserted.\n", $stmta->row_count);
                                /* close statement and connection */
                                $stmta->close();
                } // end email_count and insert to table
            } // end function

      }
?>

connect/class.Database.php

<?php

// Database connection PDO

class Database {

    public function __construct() {
        // Connection information
        $host   = 'localhost';
        $dbname = 'imanage';
        $user   = 'root';
        $pass   = '';

        // Attempt DB connection
        try
        {
            $this->pdo = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            echo 'Successfully connected to the database!';
        }
        catch(PDOException $e)
        {
            echo $e->getMessage();
        }

    }

     public function __destruct()
    {
        // Disconnect from DB
        $this->pdo = null;
        echo 'Successfully disconnected from the database!';
    }


}

?>

Answer 1

You do not need an ugly bind_result with PDO at all.

Yet you don't need to count either. Please, avoid unnecessary actions - they only bloat and obfuscate your code for no reason.

Think first, what you need from the query? Do you really need to count? No. What you actually need is just a flag - if user exists or no. So, make a query to return such a flag.

$stmt = $this->pdo->prepare("SELECT 1 FROM users WHERE email=?");
$stmt->execute(array($_POST['email']));
$exists = $stmt->fetchColumn();

Same goes for all the other parts of code

//escape the POST data for added protection

You don't actually "escape" any data in this code block and add no protection. Yet I see absolutely no point in inserting NULL as email. Are you sure you really want it?

Answer 2

For quickly retrieving a value from something like a SELECT COUNT() query, have a look at PDOStatement::fetchColumn, eg

$stmt = $pdo->prepare('SELECT COUNT(1) FROM users WHERE email = :email');
$stmt->bindParam(':email', $email);
$stmt->execute();
$email_count = $stmt->fetchColumn();

I'd also like to offer some further advice. You shouldn't be creating a PDO connection in your class constructor. This means that every time you instantiate a class extending Database, you create a new connection. Instead, pass the PDO instance as a dependency, eg

abstract class Database {
    /**
     * @var PDO
     */
    protected $pdo;

    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }
}

The same goes for your User::insert method. Try to pass any required parameters via method arguments. If you ever want to start writing unit tests for your classes, this will be invaluable

public function insert($email) {
    $stmt = $this->pdo->prepare('SELECT COUNT(1) FROM users WHERE email = :email');
    $stmt->bindParam(':email', $email);
    $stmt->execute();
    $email_count = $stmt->fetchColumn();

    // and so on

And finally, for PHP only files, omit the closing PHP tag ?>. This will save you from accidentally including whitespace at the end of your files that may be sent to the browser.

Answer 3

For people who come here seeking a literal answer.

Use bindColumn

PDOStatement::bindColumn — Bind a column to a PHP variable

using the example from the same source :

function readData($dbh) {
  $sql = 'SELECT name, colour, calories FROM fruit';
  try {
    $stmt = $dbh->prepare($sql);
    $stmt->execute();

    /* Bind by column number */
    $stmt->bindColumn(1, $name);
    $stmt->bindColumn(2, $colour);

    /* Bind by column name */
    $stmt->bindColumn('calories', $cals);

    while ($row = $stmt->fetch(PDO::FETCH_BOUND)) {
      $data = $name . "\t" . $colour . "\t" . $cals . "\n";
      print $data;
    }
  }
  catch (PDOException $e) {
    print $e->getMessage();
  }
}
readData($dbh);

Answer 4

Here's my custom function:

function bindColumns($stmt, $columns){
    if(!is_array($columns)) $columns=array($columns);
    $count=count($columns);
    for($i=0;$i<$count;$i++) $stmt->bindColumn($i+1, $columns[$i]);
}

And usage (variables by reference):

bindColumns($stmt, array(&$worker_id, &$password, &$salt));