Error establishing a database connection PDO

Question

I am using PDO to connect to MySQL

include_once('connection_insert.php');
global $host, $dbname, $user, $pass;
$DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
$DBH->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION); ///*** for error handling

function insert_terms(){
  global $DBH;
  $STH = $DBH->prepare("INSERT IGNORE INTO table
  (name,state) 
  value (?,?)");
  return $STH;
  $DBH = Null;// Closing Connection
}

But i am facing error:

Error establishing a database connection

My question is, is this the right way use global $DBH; and closing connection $DBH = Null;// Closing Connection

EDIT

connection_insert.php

$host = 'localhost';
$user = 'username';
$pass = 'password';
$dbname = 'db';

score 2 · Accepted Answer · edited May 23 '17 at 10:31

No, using global variables is bad practice ( dont look on Wordpress & and others uses global variables). The more complex the structure (architecture) application - the more terrible the consequences might be of using globals. You can find a lot of information why they are evil.Instead its you can use pattern Registry (for example) for accessing "global" variables in application. In short, lots of choices.

Сoncerning your code. Params of connecting ($host,$dbname,$user,$pass) are defined?

See also Global or Singleton for database connection? and php.net.

EDIT Example :

Index.php

require_once 'Database.php';

$DB = new Dababase();
$DB->prepare("INSERT IGNORE INTO table (name,state) value (:name,:state)");
$database->bind(':name', 'NAME');
$database->bind(':state', 'STATE');
$DB->execute();

Config.php

define("DB_HOST", "localhost");
define("DB_USER", "username");
define("DB_PASS", "password");
define("DB_NAME", "database");

Database.php

require_once 'config.php'; 

class Database{
  private $host      = DB_HOST;
  private $user      = DB_USER;
  private $pass      = DB_PASS;
  private $dbname    = DB_NAME;

  private $dbh;
  private $error;
  private $stmt;

public function __construct(){
    // Set DSN
    $dsn = 'mysql:host=' . $this->host . ';dbname=' . $this->dbname;
    // Set options
    $options = array(
        PDO::ATTR_PERSISTENT    => true,
        PDO::ATTR_ERRMODE       => PDO::ERRMODE_EXCEPTION
    );
    // Create a new PDO instanace
    try{
        $this->dbh = new PDO($dsn, $this->user, $this->pass, $options);
    }
    // Catch any errors
    catch(PDOException $e){
        $this->error = $e->getMessage();
    }
  } 

  public function prepare($query){
    $this->stmt = $this->dbh->prepare($query);
  }  

  public function query($query){
     return $this->dbh->query($query);   
  }

  public function bind($param, $value, $type = null){
    if (is_null($type)) {
     switch (true) {
        case is_int($value):
            $type = PDO::PARAM_INT;
            break;
        case is_bool($value):
            $type = PDO::PARAM_BOOL;
            break;
        case is_null($value):
            $type = PDO::PARAM_NULL;
            break;
        default:
            $type = PDO::PARAM_STR;
      }
   }
   $this->stmt->bindValue($param, $value, $type);
   } 

   public function execute(){
     return $this->stmt->execute();
   } 

   public function execute(){
     return $this->stmt->execute();
   } 

   public function fetchRows(){
     $this->execute();
     return $this->stmt->fetchAll(PDO::FETCH_ASSOC);
   }

  public function fetchRow(){
    $this->execute();
    return $this->stmt->fetch(PDO::FETCH_ASSOC);
  }

 }

Edited with connection_insert.php ... can you please give a small example please — Harinder, Jan 18 '14 at 17:34
Actually Registry is just improved Singleton (which also another kind of global state). There's no huge difference between global variables and singletons - as both do introduce another form of it. Instead a `Service Locator` should be used instead — Yang, Jan 18 '14 at 17:49
@voodoo417 Thx looks good .. just 2 questions 1st- will it put less load on mysql .. as my main problem was error from sql. and 2nd can we make any function from it, i dont want to use whole statement of insert again and again ... ** INSERT IGNORE INTO..** ... thx for good reply ;) — Harinder, Jan 18 '14 at 18:09

Error establishing a database connection PDO

1 Answers1