Explore Courses Blog Tutorials Interview Questions
0 votes
1 view
in SQL by (20.3k points)

From time to time I see questions regarding connecting to database.

Most answers is not the way I do it, or I might just not get the answers correctly. Anyway; I've never thought about it because the way I do it works for me.

But here's a crazy thought; Maybe I'm doing this all wrong, and if that's the case; I would really like to know how to properly connect to a MySQL database using PHP and PDO and make it easy accessible.

Here's how I'm doing it:

First off, here's my file structure (stripped down):


* index.php  

* initialize/  

  -- load.initialize.php  

  -- configure.php  

  -- sessions.php   


At the very top, I have require('initialize/load.initialize.php');.


#   site configurations


#   connect to database

    require('root/somewhere/connect.php');  //  this file is placed outside of public_html for better security.

#   include classes

    foreach (glob('assets/classes/*.class.php') as $class_filename){



#   include functions

    foreach (glob('assets/functions/*.func.php') as $func_filename){



#   handle sessions


I know there's a better, or more correct, way to include classes, but can't remember what it was. Haven't gotten the time to look into it yet, but I think it was something with autoload. something like that...


Here I basically just override some php.ini-properties and do some other global configuration for the site


I've put the connection onto a class so other classes can extends this one...

class connect_pdo


    protected $dbh;

    public function __construct()


        try {

            $db_host = '  ';  //  hostname

            $db_name = '  ';  //  databasename

            $db_user = '  ';  //  username

            $user_pw = '  ';  //  password

            $con = new PDO('mysql:host='.$db_host.'; dbname='.$db_name, $db_user, $user_pw);  

            $con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

            $con->exec("SET CHARACTER SET utf8");  //  return all sql requests as UTF-8  


        catch (PDOException $err) {  

            echo "harmless error message if the connection fails";

            $err->getMessage() . "<br/>";

            file_put_contents('PDOErrors.txt',$err, FILE_APPEND);  // write some details to an error-log outside public_html  

            die();  //  terminate connection



    public function dbh()


        return $this->dbh;



#   put database handler into a var for easier access

    $con = new connect_pdo();

    $con = $con->dbh();


Here I do believe there's room for massive improvement since I recently started learning OOP, and using PDO instead of mysql.

So I've just followed a couple of beginners tutorials and tried out different stuff...


Beside handling regular sessions, I also initialize some classes into a session like this:

if (!isset($_SESSION['sqlQuery'])){


    $_SESSION['sqlQuery'] = new sqlQuery();


This way this class is available all over the place. This might not be good practice(?)...

Anyway, this is what this approach allows me to do from everywhere:

echo $_SESSION['sqlQuery']->getAreaName('county',9);  // outputs: Aust-Agder (the county name with that id in the database)

Inside my sqlQuery-class, which extends my connect_pdo-class, I have a public function called getAreaName which handles the request to my database.

Pretty neat I think.

Works like a charm

So that's basically how I'm doing it.

Also, whenever I need to fetch something from my DB from not within a class, I just do something similar to this:

$id = 123;

$sql = 'SELECT whatever FROM MyTable WHERE id = :id';

$qry = $con->prepare($sql);

$qry -> bindParam(':id', $id, PDO::PARAM_INT);

$qry -> execute();

$get = $qry->fetch(PDO::FETCH_ASSOC);

Since I put the connection into a variable inside connect_pdo.php, I just have referring to it and I'm good to go. It works. I get my expected results...

But regardless of that; I would really appreciate it if you guys could tell me if I'm way off here. What I should do instead, areas I could or should change for improvement, etc...

I'm eager to learn...

1 Answer

0 votes
by (40.4k points)

You can create and maintain a single/reusable connection per-database and make sure that the connection has been set up properly. It's recommended to use both anonymous function and factory pattern for dealing with PDO connection this way :

$provider = function()


    $instance = new PDO('mysql:......;charset=utf8', 'username', 'password');

    $instance->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $instance->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

    return $instance;


$factory = new StructureFactory( $provider );

Use this for a different file or lower in the same file:

$something = $factory->create('Something');

$foobar = $factory->create('Foobar');

The factory can be used like this:

class StructureFactory


    protected $provider = null;

    protected $connection = null;

   public function __construct( callable $provider )


        $this->provider = $provider;


  public function create( $name)


        if ( $this->connection === null )


            $this->connection = call_user_func( $this->provider );


        return new $name( $this->connection );



This will let you have a centralized structure, which will make sure that the connection is created only when required. It will also make the process of unit-testing and maintenance much easier. The provider in the above case can be found somewhere at the bootstrap stage. This approach will also give a clear location where to define the configuration, that you use for connecting to the DB.

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
asked Nov 29, 2020 in SQL by Appu (6.1k points)

Browse Categories