Yet another workaround to return the row count inside only ONE select (see limitations below!):
$sth = $dbh->prepare("SELECT *,count(*) AS howmany FROM users WHERE email=:email and password=:pass"); #var placeholders
$sth->execute(array(':email'=>$email, ':pass'=>$pass)); #var binding
$row = $sth->fetch(); #get one row (it'll always be one and only one!!!)
if ($row['howmany'] == 1){ #we have a match and only one! cool!
echo $row['email'], $row['name'], $row['phone'], ... ;
} elseif ($row['howmany']>1) { #more than one row returned
#one programmer should be fired 'cause he's not checking for
#for existing emails, before creating a new user
...
# treat this exception somehow or simply skip this branch,
# if you're sure it won't happen in your table
} else { #no match in the table ($row['howmany'] == 0)
echo "Email/pass didn't match the ones in the database!";
}
Advantages:
- only one select statement is executed, no two steps needed!
- it checks if one row exists in the table or not, according to the WHERE clause.
- it returns all (or only a selection of) fields for that one row, if exists.
Disadvantages:
- it doesn't return row fields reliable if more than one row found. If more than one row responds to the SELECT query, the query returns still only one row and you don't know which one exactly .
Maybe using a SORT BY, would make it a bit more predictible (as in: "if more than one users found, return the last user added in the table") but it's more a matter of good design of the program that fills in the table initially.
Uses:
- It is perfect for checking if a user/pass pair is present in a users table and to return the other fields of the user (like name, phone, whatever) if user was found.