Which is equivalent to bind_result on PDO

Question

I convert to PDO and Im using prepared statements, I want to bind my result as $stmt->bind_result($email_count); so I can put this in an if statement to find out if the email exists, but I get the Fatal error: Call to undefined method PDOStatement::bind_result() in /Applications/XAMPP/xamppfiles/htdocs/imanage/insert.php on line 51 , related to the previous example.

I assume bind_result is not a specific PDO method, so is there an equivalent that I could use?

My code is below if 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 don't need the ugly bind_result with PDO at all.

But you also do not need to count. Please avoid unnecessary actions - they only inflate and obfuscate your code for no reason.

First think what you need from the request? Do you really need to count? Not. In fact, you only need a flag - if the user exists or not. So, make a request to return such a flag.

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

The same goes for all the rest of the code.

 //escape the POST data for added protection 

You actually do not "avoid" any data in this block of code and do not add any protection. However, I see absolutely no reason to embed NULL as email. Do you really want this?

Answer 2

To quickly get a value from something like a SELECT COUNT() query type, look at PDOStatement::fetchColumn , for example

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

I would also like to offer some advice. You should not create a PDO connection in your class constructor. This means that every time you create an instance of the Database class, you create a new connection. Instead, pass the PDO instance as a dependency, e.g.

 abstract class Database { /** * @var PDO */ protected $pdo; public function __construct(PDO $pdo) { $this->pdo = $pdo; } } 

The same applies to your User::insert method. Try passing any required parameters using the method arguments. If you ever want to start unit tests for your classes, it will be priceless.

 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 spaces at the end of your files, which can be sent to the browser.

Answer 3

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

Answer 4

For people who come here looking for a literal answer.

Use bindColumn

PDOStatement :: bindColumn - bind a column to a PHP variable

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