Future-proofing functions for php/sql queries

Question

I am trying to write a series of utility functions for the common queries and other operations I run using php.

Reason I want to do this is so that my applications can be easily upgraded between PDO, mysqli and whatever they come up with next.

Before I jump head first into this approach I wonder if some PHP experts could review this approach and tell me what you think - is this a good idea for future proofing and upgradability? How will this affect performance? Will I be limiting my applications in any way in the future?

//BASIC QUERY EXECUTION (ESCAPED)
function qry($sql)
{
    $sql = $db->real_escape_string($sql);
    if(!$result = $db->query($sql))
    {
        die('There was an error running the escaped query [' . $db->error . ']');
    }

    return $result;
}

//PREPARE AN SQL STATEMENT
function prep($sql)
{
    $statment = $db->prepare($sql);
    return $statement;
}

//BIND A PARAMETERS AND VARIABLE TYPE
function bnd($statement,$parameter,$type)
{
    $statement->bind_param($type, $parameter);
    return $statement;
}

//EXECUTE PREPARED QUERY (UNESCAPED)
function ex($statement)
{
    if(!$result = $statement->execute())
    {
        die('There was an error running the prepared query [' . $db->error . ']');
    }
    return $result;
}

//FETCH ARRAY
function ftch($result)
{
    $row = $result->fetch_assoc();
    return $row;
}

//GET NUMBER OF ROWS
function num($result)
{
    $num = $result->num_rows;
    return $num;
}

//FREE THE RESULT
function fre($result)
{
    $result->free();
}

Please do not vote for code review - this is NOT working code. — Your Common Sense, Jun 07 '13 at 13:39
I'm not asking for that - I am asking about the approach because I wonder if other people do a similar thing and if it is a good practice or not... — Amy Neville, Jun 07 '13 at 13:40
You're not doing anything except shortening names into something.. weird. What's the point? Even function names have no meaning.. `fre` against `free`, `ex` instead of `execute`. You just made everything ambiguous. — N.B., Jun 07 '13 at 13:42
Admittedly not answering your question directly, but are you not re-creating the wheel with the many PHP frameworks out there that handle differing storage engines? — dKen, Jun 07 '13 at 13:43
Reason is because I may want to change from mysqli to PDO, or add improved error handling accross the entire system easily, etc — Amy Neville, Jun 07 '13 at 13:44
Then use an ORM. Also, you have virtually no reason to switch from PDO to mysqli. — N.B., Jun 07 '13 at 13:46
@AmyNeville Cool, I still may not understand the full requirements, but if it's a fresh project, it could be worth research existing frameworks. These already support transparent switching between database systems, and you can let the communities maintain and future-proof it for you :) — dKen, Jun 07 '13 at 13:57
Yes, I think maybe I will research some existing frameworks. Makes sense. I've had some success with css frameworks that sped up development on my latest project, so there must be something worth looking into about them for php :)) — Amy Neville, Jun 07 '13 at 13:59
@AmyNeville Absolutely. There are a huge number of very stable and active frameworks, and that have such extensive communities and documentation, that it would be worth taking a close look. I won't suggest any, because each have their own USPs, but you'll find many comparisons online. Good luck :) — dKen, Jun 07 '13 at 14:02

score 1 · Accepted Answer · edited May 23 '17 at 12:18

Just a few guidelines

Create a class, not set of independent functions
Whatever real_escape_string function does not protect anything and should not used for this purpose. PDO doesn't have a function like this at all. Applying this function to a whole query will make absolutely no sense.
Therefore, qry() function simply won't work.
It won't work also because $db is out of scope.
Please don't die()! Throw an exception instead. PDO does it already, no code required

The rest is quite okay, though I would get rid of bnd() and prep() functions to make it all in one ex() call. And also I would make separate functions to get separate result types, like scalar, row, array of rows and resource. Here is an example of what I am talking about: a safeMysql class based on the pretty similar concept

Thanks, I don't pretend to be a php expert. I am just learning and trying to find efficient ways of doing things. I run successful businesses that I have developed and I need to think about future proofing and things. — Amy Neville, Jun 07 '13 at 13:51

Future-proofing functions for php/sql queries

1 Answers1