PDO empy result from SELECT when using AND

Question

Hello,

I've come upon a rather interesting thing, which I can't seem to figure out myself. Everytime when executing a SQL statement which contains a '... AND ...' the result is empty.

Example:

echo('I have a user: ' . $email . $wachtwoord . '<br>');

$dbh = new PDO($dsn, $user, $password);

$sql = 'SELECT * FROM user WHERE email = :email AND wachtwoord= :wachtwoord';
$stmt = $dbh->prepare($sql);
$stmt->bindParam(:email,$email,PDO::PARAM_STR);
$stmt->bindParam(:wachtwoord,$wachtwoord,PDO::PARAM_STR);

$stmt->execute();

while($row = $stmt->fetchObject())
{   
  echo($row->email . ',' . $row->wachtwoord);
  $user[] = array(
    'email' => $row->email,
    'wachtwoord' => $row->wachtwoord
  );
}

The first echo displays the correct values, however the line with

echo($row->email . ',' . $row->wachtwoord);

is never reached. A few things I want to add: 1) I am connected to the database since other queries work, only the ones where I add an 'AND' after my 'WHERE's fail. 2) Working with the while works perfectly with queries that do not contain '... AND ...' 3) Error reporting is on, PDO gives no exceptions on my query (or anything else) 4) Executing the query directly on the database does give what I want:

SELECT * FROM user WHERE email = '[email protected]' AND wachtwoord = 'jurgen'

I can stare at it all day long again (which I already did once, but I managed to work around the 'AND'), but maybe one of you can give me a helping hand.

Thank you in advance.

Jurgen

Answer 1

Let's see if one of those debug queries

....
$dbh = new PDO($dsn, $user, $password);

function debugQuery($dbh, $querystring, $params) {
  $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  
  $stmt = $dbh->prepare($querystring);
  $stmt->execute($params);
  echo $querystring, ":<br />\n";
  while ( false!== ($row=$stmt->fetch(PDO::FETCH_ASSOC)) ) {
    echo '  ', join(', ', $row), "<br />\n";
  }
}

debugQuery($dbh, 'SELECT Count(*) as c FROM user', array());
debugQuery($dbh, 'SELECT Count(*) as c FROM user WHERE email=?', array($email));
debugQuery($dbh, 'SELECT Count(*) as c FROM user WHERE wachtwoord=?', array($wachtwoord));
debugQuery($dbh, 'SELECT Count(*) as c FROM user WHERE email=? AND wachtwoord=?', array($email, $wachtwoord));
debugQuery($dbh, 'SELECT Count(*) as c FROM user WHERE email LIKE ? AND wachtwoord LIKE ?', array( '%'.trim($email).'%', '%'.trim($wachtwoord).'%'));

$sql = 'SELECT * FROM user WHERE email = :email AND wachtwoord= :wachtwoord';
$stmt = $dbh->prepare($sql);
...

returns something interesting.

edit: Doesn't seem so... then let's attack this from another angle

$email = 'emailA';
$wachtwoord = 'wachtwoordA';
echo('I have a user: ' . $email . $wachtwoord . '<br>');

//$dbh = new PDO($dsn, $user, $password);
$dbh = new PDO("mysql:host=localhost;dbname=test", 'localonly', 'localonly'); 
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// create a temporary table
$dbh->exec('CREATE TEMPORARY TABLE tmp_user ( id int auto_increment, email varchar(32), wachtwoord varchar(32), primary key(id))');
// and fill in exactly the record we're looking for
$dbh->exec("INSERT INTO  tmp_user (email, wachtwoord) VALUES ('$email', '$wachtwoord')");


$sql = 'SELECT * FROM tmp_user WHERE email = :email AND wachtwoord= :wachtwoord';
$stmt = $dbh->prepare($sql);
$stmt->bindParam(':email', $email,PDO::PARAM_STR);
$stmt->bindParam(':wachtwoord', $wachtwoord,PDO::PARAM_STR);

$stmt->execute();

while($row = $stmt->fetchObject())
{   
  echo($row->email . ',' . $row->wachtwoord);
  $user[] = array(
    'email' => $row->email,
    'wachtwoord' => $row->wachtwoord
  );
}

prints I have a user: emailAwachtwoordA<br>emailA,wachtwoordA on my version of php/mysql.

Answer 2

while($row = $stmt->fetchObject()){}

Shouldn't that line be (depending on your setup)?

while( $row = $stmt->fetchAll( PDO::FETCH_OBJ) ){

var_dump( $row ) ;

}