Database connection query

Question

I'm a beginner PHP developer and it's my first time using MySQLi. I'm not sure if this is a correct implementation of the connection and query because some people defend this method and some others not. I need a solid implementation for heavy database usage.

Connection:

class bbddConnection {


private $_host = 'localhost';
private $_username = 'username';
private $_password = 'password';
private $_db_name = 'DBNAME';
private $_mysqli = NULL;
private static $_instance = NULL;

private function __construct() {
    $this->connection();
}


public static function get_instance() {
    if (!isset(self::$_instance)) {
        //echo "Creating new instance.\n";
        $class_name = __CLASS__;
        self::$_instance = new $class_name;
    }
    return self::$_instance;
}


private function connection() {
    //echo "Creating new connection.\n";
    try {
        //The mysqli object will be created once and only once
        if (!$this->_mysqli instanceof mysqli) {
            $this->_mysqli = new mysqli($this->_host, $this->_username, $this->_password);
        }
        if ($this->_mysqli->connect_errno) {
            throw new Exception('An error occured: ' . $this->_mysqli->connect_error);
        } else {
            $this->select_db();
        }
    } catch (Exception $e) {
        echo $e->getMessage() . "\n";
    }
}

private function select_db() {
    //echo "Selecting database.\n";
    try {
        $this->_mysqli->select_db($this->_db_name) or die('Could not find database');
    } catch (Exception $e) {
        echo $e->getMessage() . "\n";
    }
}

public function query($query) {
    if (!isset($query))
        return;
    //echo "Prevent SQL Injection. \n"
    $query = $this->_mysqli->real_escape_string($query);
    return $this->_mysqli->query($query);
}

public function stmt_init() {
    return $this->_mysqli->stmt_init();
}


public function __destruct() {
    if ($this->_mysqli instanceof mysqli) {
        $this->_mysqli->close();
    }
}

 private function __clone() {
    trigger_error('Clone is not allowed.', E_USER_ERROR);
}

  private function __wakeup() {
    trigger_error('Unserializing is not allowed.', E_USER_ERROR);
}
}

Query:

class gestorUsuarios {

private $db;

public function __construct() {
    $this->db = bbddConnection::get_instance();
}
/**
 * Returns the Name of the user according his ID
 * @param int id
 * @return string Nombre, if an error occurred.
 * returns <b>FALSE</b>
 */
function getUserName($id) 
{

    $stmt = $this->db->stmt_init();
    if ($stmt->prepare("Select Nombre from usuarios where id = ?")) {
        $name = '';
        $stmt->bind_param('i', $id);
        $stmt->execute();
        $stmt->bind_result($name);

        while ($stmt->fetch())
            $nombre = $name;
        $stmt->close();

        return $nombre;
    } else {
          return false;
    }
}
/**
 * Edit the Name of the user according his ID
 * @param String Nombre, int id
 * @return Boolean if an error occurred.
 * returns <b>FALSE</b>, else returns <b>TRUE</b>
 */
function setUserName($nombre, $id) 
{

    $stmt = $this->db->stmt_init();
    if ($stmt->prepare("UPDATE usuarios SET Nombre=? WHERE id =?")) {

        $stmt->bind_param('si', $nombre, $id);
        $stmt->execute();
        $stmt->close();

        return true;
    } else {
        return false;
    }
}
}

Answer 1

Similar to this one: Minimalistic mysql db class

To answer your question: NO!

The short answer: Throw away your code, burn it, do whatever you want with it but never use it in a production enviroment. Just don't.

The long answer: Again, don't use this code, ever. You are even violating nearly every SOLID rule

As far as I can see you are trying to write some wrapper around the mysqli object. But the question remains: why? and what problem are you trying to solve?

As far as I can see, you are trying to solve 2 problems with one solution. Oh oh, this is doomed to fail. You want to solve the problem of always having to check wether an instance of mysqli exists or not and you want to not have to worry about connecting and creating a mysqli instance and query-ing/sanitizing the database with a given query. 3 different things.

Then you start designing the solution to that problem. When that is done, you should take a step back and ask the question 'Is my code easy to use?' and 'Is it easy configurable and extendable?' and most important 'is it easy to debug?'.

The answer to those 3 questions is simple: NO!

• If you want to change a connection parameter you have to change the code of the class itself, bad bad bad.
• If something goes wrong (an exception) you echo an error message. So what if I first get an instance of your database and it echo's some error message, and then my code sends a header(); . Ooeehh, a nice header allready sent error. Hf debugging that error...
• ...

But in fact, that isn't your real problem. The real problem you have is that you are writing code without designing it.

If your problem is that you want only one instance to exist you should only create one instance instead of relying on a get_instance() function or Singleton pattern. If a class needs a database connection, pass it in in the __construct(). More on this topic:

• http://www.slideshare.net/go_oh/singletons-in-php-why-they-are-bad-and-how-you-can-eliminate-them-from-your-applications
• https://stackoverflow.com/questions/1812472/in-a-php-project-what-patterns-exist-to-store-access-and-organize-helper-objec

To sum up, sometimes the problems we are solving tend to be meta-problems. The solution we make is actually a fix. A good article on this: http://thedailywtf.com/Articles/The_Complicator_0x27_s_Gloves.aspx

On a complete diffrent note, instead of using mysqli. use PDO. This is a realy nice OO way of using a database. more on pdo:

• http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/
• http://www.php.net/manual/en/class.pdo.php