How to build a function to connect to PDO using PHP and run queries?

Question

I am trying to write couple of function so I can later user them to connect to the database using PDO object. I have done one to return data, one to get count and another one to do an insert.

My problem is that non-of them return an exception when there is an exception. and I am not sure where I need to add a line to free results form memory?

This is my current functions

function getDataSet($query, $connection){

    $cmd = $connection->query( $query );
    return $cmd->fetchAll(PDO::FETCH_ASSOC);

}


function getResultsCount($query, $connection){

    $cmd = $connection->query( $query );
    return $cmd->fetchColumn();

}

function insertRecord($query, $connection){

    $cmd = $connection->query( $query );
    $connection->free();
    return $cmd;

}

This is how I use those functions in my application

        try {
        $data = getDataSet(' SELECT name FROM TABLE LIMIT 1000', $db);

            foreach($data AS $row){
                echo $row[0]['name'] . '<br />';
            }

        }  catch(PDOException $e){
            echo 'an error encountered'. $e->getMessage();
        }
unset($data);

summery of my question How can I get those functions to free results after they return? How can I return an exception when once exists? Is there a better function that I can use? Is there a function to handle prepare statements and return the result?

Thanks :)

Answer 1

It is always a pleasure to see such a good programming question. A rare one though.

You have a good aim writing all these functions, though there is one major mistake. You are using them as separate functions which makes not too much sense. All these functions have to be methods of the database class, using internal class resources.

Though most of the answers you can get from the tag wiki, but it seems this site never lead it's visitors to this knowledge cache, encouraging them to ask a new question instead of reading already written answers. So, here you go:

How can I return an exception when once exists?

To make PDO trow exceptions you have to set a corresponding option:

$dsn = "mysql:host=localhost;dbname=test;charset=utf8";
$opt = array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);
$pdo = new PDO($dsn,'root','', $opt);

being instantiated this way PDO will start throwing exceptions on mysql errors.

Is there a function to handle prepare statements and return the result?

Just write it.
Why don't you want to use prepare/execute in your functions? Here is one (being a class method, not separate function):

function getDataSet($query,$data = NULL)
{
    $cmd = $this->conn->prepare( $query );
    $cmd-> execute($data);
    return $cmd->fetchAll();
}

and now you'll be able to use parameterized queries as well as non-parameterized ones:

$data = $db->getDataSet('SELECT name FROM TABLE LIMIT 1000');
$data = $db->getDataSet('SELECT name FROM table WHERE id < ? LIMIT 1000',array($id));
foreach($data AS $row){
    echo $row['name'] . '<br />';
}

(note that you shouldn't use try-catch just to echo an error message - PHP will do it already, and much better way)

where I need to add a line to free results form memory?

I wouldn't bother at all. They will be cleared automatically.

function getResultsCount($query, $connection){

As a matter of fact, this function can do a lot more than just returning a count. It can actually return whatever scalar value returned by the query. So, better call it getOneResult or something like that:

$name = $db->getDataSet('SELECT name FROM table WHERE id=?',array($id));

function insertRecord($query, $connection){

this function again is named wrong way. As a matter of fact it have to be just generic query() function, which can be used to run either INSERT, UPDATE, DELETE or whatever else queries.

Is there a better function that I can use?

Well, no doubt I think my own class written for this very purpose is better :)
You can freely use it as is or just get some ideas for your own class.