5
\$\begingroup\$

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:
\$\endgroup\$
8
  • 1
    \$\begingroup\$ Please do not edit the question after an answer has been provided. Everyone needs to see what the person that wrote the answer saw. \$\endgroup\$
    – pacmaninbw
    Nov 25, 2020 at 15:44
  • \$\begingroup\$ OK, Thanks. How can I increase the number of views of the post over a long time? \$\endgroup\$
    – sidoco
    Nov 25, 2020 at 15:52
  • \$\begingroup\$ You can ask follow up questions, which is a new question based on the old question with a link to the old question. You can also answer questions and ask new questions. \$\endgroup\$
    – pacmaninbw
    Nov 25, 2020 at 16:18
  • \$\begingroup\$ @pacmaninbw OK, thanks \$\endgroup\$
    – sidoco
    Nov 26, 2020 at 5:07
  • \$\begingroup\$ In this forum, editing a question does not change its order in any list. Having optimal tags can help (up to the limit of 5). And a good title helps catch attention. \$\endgroup\$
    – Rick James
    Mar 29, 2021 at 5:56

1 Answer 1

5
\$\begingroup\$

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.

\$\endgroup\$
9
  • \$\begingroup\$ thanks a lot, I'll wait for other answers and will choose the best.You're good man,I hope you're always healthy \$\endgroup\$
    – sidoco
    Nov 25, 2020 at 12:56
  • \$\begingroup\$ Please, no. Don't recommend anyone to extend PDO class. This separate function makes much more sense because it works for all PDO instances, not just instances of a custom subclass. \$\endgroup\$
    – slepic
    Nov 26, 2020 at 5:48
  • \$\begingroup\$ @sidoco cc read my above comment please \$\endgroup\$
    – slepic
    Nov 26, 2020 at 5:50
  • \$\begingroup\$ @slepic which function, his or mine? Can you explain more in new answer? \$\endgroup\$
    – sidoco
    Nov 26, 2020 at 6:53
  • \$\begingroup\$ @slepic all right all right, deleted. no more fuss \$\endgroup\$ Nov 26, 2020 at 6:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.