function for PDO queries and return result - tested - any tips?

Question

I write a function using PHP and MySQL, to get PDO connection, query and array of placeholders to execute query and return result.

I test this function and it has good response when result for SELECT has empty result or has error; do you see any problems, or have tips to say about this?

Do you have any tips about this to test or check?

Function and test code

function db_query_pdo( PDO $pd, $q,$ar )
    {
            
//      $pd Pdo connection object, $q query string ,  $ar array of placeholders
      try
       {
            $stmt = $pd->prepare($q);
            $x=$stmt->execute($ar);
            $rs=$stmt->fetchAll();
            
            if(!empty($rslt))
                return $rslt;
            else
                return $x;
        }
        catch (Exception $ex)
        {
            echo 'Query failed';
//            exit(); uncomment this lines if needed in future
 
//    echo $ex->getCode();
//    echo $ex->getMessage();
//    throw $ex;
        }
 
    }
// TEST CODE
$pdo_cnctn=db_con_pdo(); // is a pdo connection function that worked correctly and return connection object
    $id=133;
    $id2=77;
    
$query='SELECT * FROM `users` WHERE u_id=:uid OR u_id=:uid2;
';
$vals=[
    'uid' => $id,
    'uid2' => $id2
];
 
    $res= db_query_pdo($pdo_cnctn,$query,$vals);
//$res[] = $stmt->fetch();
    if(!is_bool($res))
    {
        var_dump($res);
    }
    else
    {
       // $$pdo_cnctn->rollBack();
        echo 'Not Found';
        goto  end_point;
    }
    
    //some codes
    end_point:

Answer 1

First of all, it's a very good thing that you came to the idea of such a function. Sadly, but out of many people asking questions on Stack Overflow, only a handful ever come to such an idea. It means you're a natural born programmer, with an eye for the sub-optimal code and the desire to make it better.

The regular routine with prepared statements is not that optimal indeed, you need two function calls instead of one. Some drivers already have such a function to execute the prepared query in one go, pg_query_params() for example. But for PDO we have to create one of our own.

The function

However, the code for such a function should be much, much simper. Basically it's just three lines

function db_query_pdo( PDO $pd, $q, $ar )
{
    $stmt = $pd->prepare($q);
    $stmt->execute($ar);
    return $stmt;
}

surprisingly, that's all you really need. The rest is either harmful, superfluous or can be achieved by other means.

$query='SELECT * FROM `users` WHERE u_id=:uid OR u_id=:uid2';
$vals=[
    'uid' => $id,
    'uid2' => $id2
];

$res = db_query_pdo($pdo_cnctn,$query,$vals)->fetchAll();
if($res)
{
    var_dump($res);
}
else
{
    echo 'Not Found';
}

See, the calling code is almost the same, but the function became

a) much cleaner
b) much more versatile, as you can chain any PDOStatement method to it, not limited to the return value of fetchAll()

Say, you've got a delete query and want to know whether it deleted any rows? No problemo!

if (db_query_pdo($pdo_cnctn,"DELETE FROM t WHERE id=?",[1])->rowCount()) {
    echo "deleted";
}

or you want a fetchAll() but with different modifier:

$ids = db_query_pdo($pdo_cnctn,"SELECT id FROM t WHERE id<?",[10] )->fetchAll(PDO::FETCH_COLUMN);

or you want to get a single value

$exists = db_query_pdo($pdo_cnctn,"SELECT 1 FROM t WHERE email=?",[$email] )->fetchColumn();

etc., etc., etc.

The error reporting

To be honest, all that try catch business in your function is completely off the track. Especially that commented out section, "uncomment this lines if needed in future". Seriously? Are you really going to comment these lines back and forth? That's not how a good program should be written. It shouldn't require anything like this.

Besides, this approach is based on the two wrong notions which, sadly, are extremely popular among PHP folks:

• "an error must be thrown only in some circumstances". That's but a grave delusion. A programmer desperately needs the error message no matter that. An error must be thrown every time, no exceptions. As a matter of fact, in the production environment the error message is much more important than in the dev. Without the error message a programmer will never have an idea what's going wrong
• "a database function should decide what to do with its errors". This is a funny one. There is nothing special in the database interactions. But for some reason PHP users diligently wrap their database functions in a distinct handling code. At the same time completely neglecting every single other line in the code. It just makes no sense. A database error is no different from a filesystem error. And as you don't wrap every include in a distinct try catch, you should never do it for a db function either.

All the error handling business should occur elsewhere. You can read more about error reporting basics from my article.