Using php functions with PDO

Question

What I want to do: Use functions to connect to my database with PDO so i don't have to type the connection out every time.

What I tried:

library.php:

function dbconpdo() {
    $db = new PDO('mysql:host=localhost;dbname=dbname', 'username', 'password');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
}

test.php:

include_once "templates/library.php";

    try {
        dbconpdo();
        $stmt = $db->query('SELECT * FROM users');
        while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            echo $row['user'];
            echo $row['password'];
            echo "test";
        }
    } catch(PDOException $ex) {
   echo $ex;
}

That didn't work, It didn't give me any errors but didn't output anything at all.

What worked:

test.php:

try {
    $db = new PDO('mysql:host=localhost;dbname=dbname', 'username', 'password');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $stmt = $db->query('SELECT * FROM users');
    while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo $row['user'];
        echo $row['password'];
        echo "test";
        }
    } catch(PDOException $ex) {
   echo $ex;
}

It worked, but the problem with that is that i had to remove the function altogether and type it(the connection) out manually.

My question: How do I use functions(or anything similar) to make a PDO connection to my database so that I don't have to manually type it out each time I want to connect?

Before working with classes and objects - learn variables scope — zerkms
– zerkms, Commented Nov 8, 2013 at 2:43

Darragh Enright · Accepted Answer · 2013-11-08 02:56:50Z

1

If you don't want to work with objects and just wanted a simple function that you can call anywhere, which instantiated a PDO object on the first call, and caches/returns the object for subsequent calls you could do the following:

<?php

function dbConnection()
{
    static $db;

    if (!$db instanceof PDO) {
        $db = new PDO('mysql:localhost;dbname=dbname', 'username', 'password');
    }

    return $db;
}

It's a function in the global scope so you can call it anywhere, and assign the return value to a local variable like so:

$db = dbConnect();
$db->prepare('SELECT name FROM user where id = :id');
// etc

It's not really elegant but it's handy for small scripts. Hope this helps :)

Edit:

A small bit of info on the static keyword in this context: http://www.php.net/manual/en/language.variables.scope.php#language.variables.scope.static

edited Nov 8, 2013 at 2:56

answered Nov 8, 2013 at 2:51

Darragh Enright

14.2k8 gold badges45 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BL1nK Over a year ago

I used my own function(I tried to modify your function for my use but didn't work), my own calling method, but added your "return $db" and "$db = dbConnect()" and it worked like a charm!

Darragh Enright Over a year ago

great! it's not the 'purest' approach by a long stretch but it works well enough, especially for small scripts and so on. it's sort of simplistic singleton as a regular function, which isn't great for encapsulation and testability, which is crucial for big apps. but, you're using PDO which is good. you should have a look at PDO's prepared statements as well. best/safest way to write queries! Anyway, glad to help :)

Fluffeh · Accepted Answer · 2013-11-08 02:51:11Z

0

I am a fan of using objects, so normally create an instance of this to use as needed:

class mySQL
{
    public $con;

    private $userName = "userName";
    private $passWord = "password";
    private $hostName = "mysql:host=localhost;dbname=yourDBName";
    private $isDebug=false;

    function __construct()
    {
        $this->con = new PDO ($this->hostName, $this->userName , $this->passWord);
        $this->con->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    }

    function __destruct()
    {
        $this->con = null;
    }
}

$con=new mySQL();
$sql="select * from someTable";
$con->con->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));

$con->execute()

This is mainly cut and paste from inside the objects I happen to have on this laptop, but you get the idea of where it is going.

answered Nov 8, 2013 at 2:51

Fluffeh

33.6k16 gold badges69 silver badges80 bronze badges

1 Comment

BL1nK Over a year ago

Please forgive my inexperience, but how would i echo out the result?

Collectives™ on Stack Overflow

Using php functions with PDO

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related