Can I include one pdo connection

Question

Im a just moving to using PDO for my development and I see in most tutorials that the connection is opend for each db query like in Jeffery Ways example below

$id = 5;
try {
    $conn = new PDO('mysql:host=localhost;dbname=myDatabase', $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);   

    $stmt = $conn->prepare('SELECT * FROM myTable WHERE id = :id');
    $stmt->execute(array('id' => $id));

    while($row = $stmt->fetch()) {
        print_r($row);
    }
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}

Can I still do a connection in an external file and include it at the top of my page like with previous procedural coding and then do my queries below in the page?

<?php include 'includes/db.php';?>

Opening multiple connections to the same database is stupid ... always use a single connection per request. — Orangepill, Aug 21 '13 at 13:34

Your Common Sense · Answer 1 · 2022-11-08T20:57:17.733

You probably misunderstood what he says. To open one connection and use it throughout the whole application is not that something you "can" but actually you should.

So - yes, you are doing it right.

Also note that this thing with

try {
    ...
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}

that Jeffery taught you is wrong. Never use a try catch to echo an error message. PHP will handle it better

So, your code should be like this

include 'includes/db.php';

$stmt = $pdo->prepare('SELECT * FROM myTable WHERE id = :id');
$stmt->execute(array('id' => $id));

while($row = $stmt->fetch()) {
    print_r($row);
}

while db.php has to contain something like this

<?php
$dsn = "mysql:host=localhost;dbname=test;charset=utf8mb4";
$opt = array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);
$pdo = new PDO($dsn, $username, $password, $opt);

Also note that when using this PDO object, you have to be aware of the variable scope.

Further reading: https://phpdelusions.net/pdo

What way should I deal with the catch then? I seen it that way in another tutorial a while back. — Pierce McGeough, Aug 21 '13 at 13:59
All these tutorials are the same useless crap. You have to deal with catch **only** if you are going to handle the error itself, but never to just report a message. PHP can do it already, and way better way. — Your Common Sense, Aug 21 '13 at 14:02

Matt Indeedhat Holmes · Accepted Answer · 2013-08-21T14:17:13.630

The short answer is yes,

if you are farmilier with OOPHP it might be worth creating a wrapper class to help with running queries but just creating the connection in a file and including it will get the job done

in the above example you can put

try {
    $conn = new PDO('mysql:host=localhost;dbname=myDatabase', $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);   

    }
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}

into your db.php and the run the queries

 $stmt = $conn->prepare('SELECT * FROM myTable WHERE id = :id');
 $stmt->execute(array('id' => $id));

wherever you need.

it may also be worth mentioning that you dont have to use prepared statements with PDO which can speed things up in coding however if you wish to do that i would highly recomend a database wrapper class

non prepared statement

<?php
try {
    $conn = new PDO('mysql:host=localhost;dbname=myDatabase', $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);   

    }
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}

$query = "
SELECT 
  col_1, 
  col_2
FROM
  table_1
WHERE
  col_3 = " . $conn->quote($_POST['input']); //the quotr is important, it escapes dangerous characters to prevent SQL injection


//this will run the query for an insert this is all thats needed
$statement = $conn->query($query);

//fetch single col
$col = $statement->fetch(PDO::FETCH_ASSOC);

//fetch all collums
$cols = $statement->fetchAll(PDO::FETCH_ASSOC);

the advantage of this way is that you can build up the query SQL in a more simple to follow manner, i should not that i havent tested this code but in theory it should be fine as this is how i do database handling

Edit: Your Common Sense brings up a good point about the echo 'ERROR: ' . $e->getMessage(); being a bad idea and this is a prime example of why you should NEVER blindly copy and paste code

What do you mean that I dont have to use prepared statements with PDO? I can do statements the same as procedural? — Pierce McGeough, Aug 21 '13 at 13:38
kind of, you would still be using the PDO object but you dont have to do the whole ->prepare ->execute steps, ill write up a simple example and add it to this answer, just give me 10mins — Matt Indeedhat Holmes, Aug 21 '13 at 13:40
There are two flaws with your answer. Relatively minor one, `echo 'ERROR: ' ...` as PHP errors should **never** be echoed, and indeed a fatal one: a statement on not using prepared statements, while those have to be the **only** way of adding dynamical statements to the query. — Your Common Sense, Aug 21 '13 at 14:00
Thanks, I think I can grasp the PDO statements ok with needing to use that method. — Pierce McGeough, Aug 21 '13 at 14:06

score 0 · Answer 3 · answered Aug 21 '13 at 13:35

Yes, example:

db.php

<?php
$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';

try {
    $dbh = new PDO($dsn, $user, $password);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

?>

FROM:

http://www.php.net/manual/en/pdo.error-handling.php

Then just include db.php. I name my connection $PDO, seems more implicit, especially when you are building a prepared statement on that.

Missed this, didnt expect to find it in the error handling section — Pierce McGeough, Aug 21 '13 at 13:39

Can I include one pdo connection

3 Answers3

Linked

Related