Back

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):

public_html/

* index.php  

* initialize/  

  -- load.initialize.php  

  -- configure.php  

  -- sessions.php   

index.php

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

load.initialize.php

#   site configurations

    require('configure.php');

#   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($class_filename);

    }

#   include functions

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

        include($func_filename);

    }

#   handle sessions

    require('sessions.php');

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...

configure.php

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

connect.php

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...

sessions.php

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

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

    session_start();

    $_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)
Welcome to Intellipaat Community. Get your technical queries answered by top developers!

28.4k questions

29.7k answers

500 comments

94.6k users

Browse Categories

...