Iulian Barbu Iulian Barbu - 3 months ago 15
MySQL Question

PHP closing mysqli connection to database error

The case in which I'm getting this error is diffrent from what I have seen on SO, in trying to solve my problem. I have a class, Database, which creates only one instance of itself, trying to limit the number of connection to mysql server to one. Here is my class [code 1]:

class Database {

private $_connection;

// Store the single instance.
private static $_instance;

/**
* Get self instance of database to private static variable $_instance.
* @param string $host
* @param string $username
* @param string $password
* @param string $database
* @return Database
*/
public static function getInstance($host,$username,$password,$database) {
if (!self::$_instance) {
self::$_instance = new self($host,$username,$password,$database);
}
return self::$_instance;
}

/**
* Constructor.
* @param string $host
* @param string $username
* @param string $password
* @param string $database
*/
public function __construct($host,$username,$password,$database) {
$this->_connection = new mysqli($host, $username, $password, $database);

// Error handling.
if (mysqli_connect_error()) {
trigger_error('Failed to connect to MySQL: ' . mysqli_connect_error(), E_USER_ERROR);
}
}

/**
* Empty clone magic method to prevent duplication.
*/
private function __clone() {}

/**
* Get the mysqli connection;
*/
public function getConnection(){
return $this->_connection;
}
}


After this class, I created a connection for getting some info from a table. The code is the following [code 2]:

// Establish a connection with MySQL Server database.
$host = 'localhost';
$username = 'barbu';
$password = 'watcrn0y';
$database = 'siteinfo';

$db = Database::getInstance($host, $username, $password, $database);
$mysqli = $db->getConnection();

// Get the firstname of the author of the site from database.
$sql_query = 'SELECT author.firstname, author.lastname ';
$sql_query .= 'FROM author;';

$result = $mysqli->query($sql_query);


if($result && $row = $result->fetch_assoc()){
$author_firstname = $row['firstname'];
$author_lastname = $row['lastname'];
}


Now, in another file, I do this [code 3]:

require '../includes/siteinfo.php'; // this file contains the connection
// with first database ['siteinfo'].
//I include this file for accessing some other variables from it, which aren't
//in the code posted above.

// Establish the connection to server.
$host = 'localhost';
$username = 'barbu';
$password = 'watcrn0y';
$database = 'articles';

// Here I need to close my previous connection and begin another.
// It is important to remember that my class is designed for creating
// just one connection at a time.
// So if I want to change the connection to another database,
// I have to close the previous one and create the one which
// suits my needs.
$mysqli->close();
unset($db);

$db = Database::getInstance($host, $username, $password, $database);
$mysqli = $db->getConnection();



// Send the post info to the server.
$title = (isset($_POST['title'])) ? $_POST['title'] : '';
$text = (isset($_POST['text'])) ? $_POST['text'] : '';
$sql = "INSERT INTO postInfo ";
$sql .= "VALUES ('" . $author_firstname . " " . $author_lastname ."', '" .
date('d D M Y') . "', '" . $title . "', '" . $text . "');";
$result = $mysqli->query($sql);


While doing this, I get the error:


Warning: mysqli::query(): Couldn't fetch mysqli in /home/barbu/blog/admin/index.php on line 24


If I don't close the first connection (and let say I let just ['unset($db)']), my query will be executed on the first database ['siteinfo'], and I'm getting another error message, namely one telling me that the 'postInfo' table doesn't exist in 'siteinfo' database, which is true. If I let that connection to persist, and declare another instance of Database class, $db1, and another mysqli object, $mysqli1, which hold my connection, and execute my query through it, I get the same mysqli error message as in second case: 'siteinfo.postInfo' doesn't exist. What do you recommand me? How can I solve this problem?

Answer

First of all if you want to have only one connection per session and not allow to create a seccond instance you should define Database::__construct as private. Then add a new method Database::close. The idea of this method is to close the connection and set the link to instance of class Database to null. The code will look like this:

public function close() 
{
   if (self::$_instance) {
       self::$_instance->getConnection()->close();
       self::$_instance = null;
   }
}

Final point, instead of $mysqli->close(); you should call $db->close();