Creating MYSQLI only once

Question

I'm trying to create my db connection this way. In Functions_BD I would like to create connection to BD and define all my functions (Login, Insert, Selects...). My class looks like this:

 class functions_BD {

 private $mysqli;

 function __construct() {
    require_once 'config.php';
    $mysqli = new mysqli(DB_HOST,DB_USER,DB_PASSWORD,DB_DATABASE);   

}

In 'config.php' I've my DB configuration. Then I have some public functions inside "Functions_BD" like this one

public function login($user,$passw){        

        $mysqli = new mysqli(DB_HOST,DB_USER,DB_PASSWORD,DB_DATABASE);   

        if (mysqli_connect_errno()) {
            return 2;
        }
        $stmt=$mysqli->prepare("SELECT COUNT(*) FROM users WHERE user= ? AND passw= ? ");
        $stmt->bind_param('ss', $user,$passw);
        $stmt->execute();
        $result = $stmt->fetch();

        if($result>0){          
            return 1;
        }else{              
            return 0;
        }           
    }

If I define $mysqli in each function the code works fine, my question is: is there any way of defining $mysqli only once and using it in all my functions? (Like I tried in the constructor) Or I may define it again every time I do a query?

Uhm, you're not saving passwords in plain text, are you? – Marcel Korpel Dec 23 '13 at 12:27 — Marcel Korpel, Dec 23 '13 at 12:27

score 1 · Accepted Answer · answered Dec 23 '13 at 12:17

Just set it in an property ... use in the methods later, if you're using the same connection in others class, try learn and use dependency injection, and about the factory pattern.

In you ctor, just put $this->mysqli = new mysqli( ... ), and then, in others methods, just use it: $this->mysqli->prepare ...

You code will look something like that:

<?php

class functions_BD 
{
  private $mysqli;

  function __construct() 
  {
    require_once 'config.php';
    $this->mysqli = new mysqli(DB_HOST,DB_USER,DB_PASSWORD,DB_DATABASE);
  }

  function login($user, $psw)
  {
    $stmt = $this->mysqli->prepare("SELECT COUNT(*) FROM users WHERE user= ? AND passw= ? ");
    $stmt->bind_param('ss', $user,$passw);
    $stmt->execute();
    $result = $stmt->fetch();

    return $result > 0;
  }
}

score 0 · Answer 2 · answered Dec 23 '13 at 12:19

0

You can use the following singleton pattern:

class functions_BD {
  private $mysqli;
  private static $instance;

  public static function get_instance() {
    if (!isset(self::$instance)) {
      self::$instance = new self();
    }
    return self::$instance;
  }

  private function __construct() {
    $this->mysqli = new mysqli(DB_HOST,DB_USER,DB_PASSWORD,DB_DATABASE);
  }
}

To use the DB, you can use the following:

$db = functions_BD::get_instance();

The same instance will be called in all your codes.

answered Dec 23 '13 at 12:19

Raptor

53,206
45
230
366

Singleton is very bad, very, very ... in PHP, singleton doesn't exists and should not be used. – Andrey Dec 23 '13 at 12:20
Really? I doubt. Can you please explain? I treat this as a subjective comment. Many PHP frameworks use Singleton pattern nowadays. – Raptor Dec 23 '13 at 12:21
I can't explain with my words right now, but take a look there: http://pt.slideshare.net/go_oh/singletons-in-php-why-they-are-bad-and-how-you-can-eliminate-them-from-your-applications – Andrey Dec 23 '13 at 12:24
Yes, some experienced PHP developers support your stance too. Read this: http://stackoverflow.com/questions/4595964/who-needs-singletons. However, things change rapidly in PHP since PHP introduces full OO. Singleton pattern is now useful, especially on handling DB connections (the current case). – Raptor Dec 23 '13 at 12:27
Nopz, why you does not use an factory ? put at the top, and then create others objects using the same connection. – Andrey Dec 23 '13 at 12:31
Factory pattern does not solve OP's question: single connection to DB. – Raptor Dec 23 '13 at 12:34
What if you call the class in 2 different PHP files? – Raptor Dec 23 '13 at 12:39
Easy, i told you to put at the top, in this case, something like an 'db.php', and then create factory, and use require_once ... so, the file will be included only once, the factory will be created only once, or, you can setup an Registry. – Andrey Dec 23 '13 at 12:41
Wait. I assumed you have the Factory call in the `db.php`, and `require_once('db.php')` in every PHP file in need, right? When you access from A.php to B.php, `db.php` is called **twice** (`require_once` only calls once in same file). It can be proven by adding a debug message near MySQLi connection. Isn't it the case? – Raptor Dec 23 '13 at 12:44
In `A.php`, you'll require the `db.php`, then `$factory` will propagate to `B.php` thats is included from `A.php`. – Andrey Dec 23 '13 at 12:47

Creating MYSQLI only once

2 Answers2