PDO connection / prep and execute in there own functions best practice

Question

Hi all i have been playing with PDO's and am slowly converting some old code over to this.

Getting a little stuck with a few thing and struggling to find what i need.

What i am getting stuck on is:

1. Having the $db in a function or the sorts so its only open when i call it, i only want to manage one instance of this
2. checking if execute was successful and if not return a value etc.

Also any advice on the below code would be great full as i have gathered this from sources around the web.

Current Code:

//Database Array
$config['db'] = array(
    'host' => 'localhost',
    'username' => 'root',
    'password' => 'root',
    'dbname' => 'root');

//New PDO
$db = new PDO('mysql:host=' . $config['db']['host'] . ';dbname=' . $config['db']['dbname'], $config['db']['username'], $config['db']['password']);

//Check connection is ok
try {
    $db->exec("SET CHARACTER SET utf8");
}
catch (PDOException $ex) {
    print "Error!: " . $ex->getMessage() . "<br/>";
    die();
}


//Update users function
function update($db, $fn, $ln, $email, $offers, $vlue, $responce) {
    $stmt = $db->prepare("insert into kkt (fName_765, lName_765, email_765, signup_765, stamp_765) values (:fname, :lname, :email, :signup,  NOW())");
    $stmt->bindParam(':fname', $fn, PDO::PARAM_STR);
    $stmt->bindParam(':lname', $ln, PDO::PARAM_STR);
    $stmt->bindParam(':email', $email, PDO::PARAM_STR);
    $stmt->bindParam(':signup', $offers, PDO::PARAM_STR);
    $stmt->execute();
    print $db->lastInsertId(); 
    $stmt = null;
}


//Test Attributes
$fn = 'test';
$ln = 'test';
$email = 'tesst@test,com';
$offers = '1';

update($db, $fn, $ln, $email, $offers, $vlue, $responce);

thanks in advance for any help / tips

Edited Code:

//Database Array
$config['db'] = array(
    'host' => 'localhost',
    'username' => 'root',
    'password' => 'root',
    'dbname' => 'local');

//New PDO
$db = new PDO('mysql:host=' . $config['db']['host'] . ';dbname=' . $config['db']['dbname'], $config['db']['username'], $config['db']['password']);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//Check connection is ok
try {
    $db->exec("SET CHARACTER SET utf8");
}
catch (PDOException $ex) {
    print "Error!: " . $ex->getMessage() . "<br/>";
    die();
}


//Update users function
function update($db, $fn, $ln, $email, $offers, $vlue, $responce)
{
    $stmt = $db->prepare("insert into local (fName_765, lName_765, email_765, signup_765) values (:fname, :lname, :email, :signup, NOW())");
    $stmt->bindParam(':fname', $fn, PDO::PARAM_STR);
    $stmt->bindParam(':lname', $ln, PDO::PARAM_STR);
    $stmt->bindParam(':email', $email, PDO::PARAM_STR);
    $stmt->bindParam(':signup', $offers, PDO::PARAM_STR);
    try {
        $stmt->execute();
        print $db->lastInsertId(); //show ID
        return true;
    }
    catch (PDOException $e) {
        print "Error!: " . $e->getMessage() . "<br/>"; // show error
        return false;
    }
}


//Test Attributes
$fn = 'test';
$ln = 'test';
$email = 'tesst@test,com';
$offers = '1';


if (!update($db, $fn, $ln, $email, $offers, $vlue, $responce)) {
    echo "no update there is a slight problem";
} else {
    echo "it seemed to work";
}

Seem to be getting there, the above works hows it looking

Answer 1

checking if execute was successful and if not return a value etc.

Personally I prefer Exceptions, and PDO can be configured to raise Exceptions on errors. Exceptions are nice because the code that comes after the failed statement is not executed. This comes handy if you've a parent row, and then write some child rows which depend on the inserted parent. You don't want to write the child rows when the parent could not be created.

One can turn this on by doing this:

<?php

$pdo = new PDO(/* DSN */);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ATTR_ERRMODE_EXCEPTION);

Then you would do:

<?php

try {
    $stmt->execute();
    return true;
} catch (\PDOException $e) {
    return false;
}

You can find more about this here: http://www.php.net/manual/en/pdo.error-handling.php

Having the $db in a function or the sorts so its only open when i call it, i only want to manage one instance of this

I'm usually managing a database connection by using a service container. The simplest existing option is Pimple. You then pass this service container around, and the service container is responsible for only creating one database connection.

<?php

$config = new \Pimple;

$config['db.options'] = array(
    'host' => 'localhost',
    'username' => 'root',
    'password' => 'root',
    'dbname' => 'root');

# Calling the "share" method makes sure that the function is only called when
# 'db' is retrieved the first time.
$config['db'] = $config->share(function() use ($config) {
    return new PDO('mysql:host=' . $config['db.options']['host'] . ';dbname=' . $config['db.options'']['dbname'], $config['db.options'']['username'], $config['db.options'']['password']);
});

function update() {
     global $config;

     # Connection is only made the first time the 'db' key is accessed.
     $db = $config['db'];
     /* Do queries */
}

Answer 2

Having the $db in a function or the sorts so its only open when i call it, i only want to manage one instance of this.

Open it at the start of your script and then pass it into the functions that need it. Opening a new database connection inside a function can lead to problems further down the line. For example, what if your function is used multiple times throughout the same script? You don't really want to open a new database connection every time that same function gets called.

checking if execute was successful and if not return a value etc.

As for checking if PDOStatement::execute was successful:

$result = $stmt->execute();

If you look at the manual, the return types are listed as:

Returns TRUE on success or FALSE on failure.

$result = $stmt->execute();
return $result;

or

return $stmt->execute();

Personally, I'd go with:

function update($db, $fn, $ln, $email, $offers, $vlue, $responce) {
    $stmt = $db->prepare("insert into kkt (fName_765, lName_765, email_765, signup_765, stamp_765) values (:fname, :lname, :email, :signup,  NOW())");
    $stmt->bindParam(':fname', $fn, PDO::PARAM_STR);
    $stmt->bindParam(':lname', $ln, PDO::PARAM_STR);
    $stmt->bindParam(':email', $email, PDO::PARAM_STR);
    $stmt->bindParam(':signup', $offers, PDO::PARAM_STR);
    return $stmt->execute();
}

By the way, when you pass an object into a function, it is automatically passed by reference, which means that you can do something like:

$result = update($db, $fn, $ln, $email, $offers, $vlue, $responce);
if($result){
    echo $db->lastInsertId();
}