I currently do two queries when using prepared statements. One to fetch the results and another to fetch the number of found rows. I could previously do this with one query with mysql_num_rows. With larger queries i don't want to have to copy and paste the query and use COUNT every query. Is there a way to do this in one query like with mysql_num_rows?
$connectdb->prepare("SELECT * FROM users WHERE username=:username");
$connectdb->prepare("SELECT COUNT(*) FROM users WHERE username=:username");
$query = $connectdb->execute(array(':username'=>$username));
$numrows = $query->fetchColumn();
if($numrows!=0) {
while(false !==($row = $query->fetch()))
{
The statement already has the row count: PDOStatement::rowCount()
So use $query->rowCount(); and your first query only
PS: your code doesn't look like it even would work. The correct one should be:
$stmt = $connectdb->prepare("SELECT * FROM users WHERE username=:username");
$stmt->execute(array(':username'=>$username));
...
$query=$connectdb->prepare("SELECT * FROM users WHERE username=:username");
$query->execute(array(':username'=>$username));
$numrows = $query->rowCount();
You have 3 options available in PDO:
1) what you currently have, by using COUNT():
$numrows = $query->fetchColumn();
2) fetchColumn() without executing the query itself:
$pdo->query("SELECT COUNT(*) from users")->fetchColumn();
3) rowCount() NOTE - apparently doesn't work in MySQL, and I don't recommend this:
PDOStatement::rowCount();
EDIT - To clarify - Number 3, this is a reference from the PDO Doc:
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.