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';
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);
}
}