php pdo save query data

: Call to a member function rowCount() on a non-object in C:\wamp\www\prjy\classes\user.php on line 34

Line 29-32

$this->st开发者_JAVA技巧h = $this->dbh->prepare("SELECT id, userlevel FROM users WHERE username = ? AND password = ?");
$this->sth->bindParam(1, $username);
$this->sth->bindParam(2, $password);
$this->data = $this->sth->execute();

Line 34

if ($this->data->rowCount() > 0)

I want to save the result object in another variable, so I can execute another query and depending on the result, I return what is correct... Can't I save the result, (execute()) in a variable like above? How do I solve it else way?

PDOStatement::rowCount() returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.

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.

Reference

What about using:

count($this->data)

for the row count?

Matt is correct; PDOStatement::execute() returns a boolean value.

And diEcho is partially correct about PDOStatement::rowCount(); the docs also say:

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

Seeing you are using MySQL, this will work, but keep in mind that this may therefor not be portable to other RDBMSs.

If portability is of no concern, PDOStatement::rowCount() is what you need and therefor you need to use this:

if ($this->sth->rowCount() > 0)

However, you'll need to save multiple PDOStatement objects. So I suggest doing something like the following:

$stmt1 = $this->dbh->prepare( /* first select statement */ );
$stmt1->bindParam(1, $username);
$stmt1->bindParam(2, $password);

$stmt2 = $this->dbh->prepare( /* second select statement */ );
$stmt2->bindParam(1, $username);
$stmt2->bindParam(2, $password);

if( $stmt1->execute() && $stmt2->execute() )
{
    if( $stmt1->rowCount() > 0 )
    {
        $result = $stmt1->fetchAll();
    }
    else if( $stmt2->rowCount() > 0 )
    {
        $result = $stmt2->fetchAll();
    }
}

Or something of similar nature, depending on what criteria you will want the results of either one of the statements.

PS.: be aware though, that in order for this to work, you would have to activate buffered queries for MySQL in PDO (if this wasn't activated already):

$db = new PDO(
          'dsn connection string',
          'username',
          'password',
          array( PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true )
      );

You have to perform another test before calling rowCount():

$this->sth = $this->dbh->prepare("SELECT id, userlevel FROM users WHERE username = ? AND password = ?");
$this->sth->bindParam(1, $username);
$this->sth->bindParam(2, $password);
if($this->data = $this->sth->execute()) {
   if ($this->data->rowCount() > 0) {
      // do something here ..
   }
}

You are not testing for errors. PDOStatement::execute() returns false in case of an error. You should test for that!

if (!$this->data = $this->sth->execute()) { echo "ERROR!"; print_r($this->sth->errorInfo()); die(); }

Or something similar. That's probably the reason.