PDO in PHP, how to improve this PDO mysql code

Question

Thanks for checking. All helpful answers/comments are up voted. I have the following code, which does the job, but imo is not efficient. The reason I think it's not efficient is because I'm using fetchAll + loop even though I know that the query will return either 1 or no records.

//assume the usual new PDO, binding, and execute are up here

$myval = "somevalue";

$res = $stmt->fetchAll(PDO::FETCH_ASSOC);

if (!$res) {
    //no record matches
    //BLOCK A CODE HERE
} else { 
    //found matching record (but always going to be 1 record, no more) 
    foreach($res as $row) {
     if ($myval == $row['val']){
      //myval is the same as db
      //BLOCK B CODE HERE
     } else {
      //myval is different from db
      //BLOCK C CODE HERE
     }
    }//foreach
}

How can I improve it to remove the bulky look of the foreach and fetchAll (considering I know it's always going to be 1 or 0 record only)? But I still need similar checkpoints so I can execute the same BLOCK A BLOCK B BLOCK C as my current logic needs it.

Answer 1

If you are expecting to deal with no more than one row, you could use fetch instead of fetchAll.

Answer 2

You just have to use native SQL for your statement and prepare it:

SELECT * FROM someTable WHERE specificVal = ?

If you did this, you can use ->fetch instead of ->fetchAll and also use ->bindParam. And ->prepare makes it easy to deal with ANY $myVal, because you can run the statement as often as you want. You just have to chance the ? by using another params.

Example:

$stmt->prepare($yourQuery);
$stmt->bindParam($one,$two);

if($stmt->fetch(PDO::FETCH_ASSOC))
{
// here you can access $two (the result)
}
elseif(empty($two) || !checkForOtherComparisons($two))
{
// here you go if $two is not available or does not match to any other logic
}

Answer 3

Try:

$stmt->fetch( PDO::FETCH_ASSOC );

This will fetch just the first row.

Since you know for sure it will return only 1 or 0 rows it is likely safe to use this.

Answer 4

$myval = "somevalue";

$row = $stmt->fetch(PDO::FETCH_ASSOC);

if (!$row) {
    //no record matches
    //BLOCK A CODE HERE
} else if ($myval == $row['val']) { 
    //myval is the same as db
    //BLOCK B CODE HERE
} else {
    //myval is different from db
    //BLOCK C CODE HERE
}

Answer 5

I will rewrite it in following way:

$res = $stmt->fetchAll(PDO::FETCH_ASSOC);
$first_row = ( count($res) ? $res[0] : null );
if ( is_null($first_row) ) {
    // nothing found code
}
else {
    // we found something
    if ($myval == $first_row['val']) {
         // result is good
    }
    else {
         // result is bad
    }
}

Also I'll enable PDO to throw exceptions for all errors:

$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

So I'll not need check errors for each PDO result. Just try/catch block in main function. Somewhere at top level of the code:

try {
    // main script logic
}
catch (PDOException $e) {
    // sql error appeared somewhere, we should save it for futher investigation
}