PHP and MySQLi Reference database connection within user class

Langdale · Apr 10, 2013

I am trying to learn OOP in php, and so I decided to have a go at creating a simple login script. The following code works fine; it logs me in as required. However, the database connection is held within the function, which means that every time I want to access the database I have to log in again. Not great!

I have tried to set up a connection class but I cannot seem to find a way to reference the connection within the function. Can anyone help please. P.s. I have had a look at other answers but cannot seem to find anything that helps.

public function test_login($username,$password){

	[B]$mysqli = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_DATABASE);	
	if (mysqli_connect_errno()) {
		printf("Failed to connect to MySQLi: %s\n", mysqli_connect_errno());	
	}[/B]else{	
		/* Set up and execute prepared statment to retrieve User id */				

		$query ="SELECT uid from users WHERE username='$username' and password = '$password'";

		/* create a prepared statement */
		if ($stmt = $mysqli->prepare($query)) {
			$stmt->execute();						/* execute query */
			$stmt->bind_result($uid);				/* bind result variables */
			/* fetch value */
			if($stmt->fetch()){
				$_SESSION['login'] = true;
				$_SESSION['uid'] = $uid;
				return TRUE;
			}else{
				return FALSE;
			}
			$stmt->close();							/* close statement */
		}					
		/* close connection */
		$mysqli->close();
	}
}

Thanks in advance!

traq · Apr 10, 2013

don't put the DB connection inside the function - create it outside (so all methods can use it), and use dependency injection to give it to those methods that need it (very simple example):

<?

$DB = new myDatabaseConnection();

$logged_in = User::test_login( $username,$password,$DB );

Roger_Ramjet · Apr 12, 2013

Langdale, what you want is a Singleton Database class that returns the connection. Properly constructed it will have methods to prepare statements and bind parameters and execute queries, saving you a lot of repetition in code elsewhere. Here is my own such class that has stood the test of time, but it uses PDO not mysqli so you will have to convert it, or change to PDO. The database connection is passed by reference and can be reused throughout your code without having to re-connect every time, so you can pass it into other classes as Traq has suggested, or just call for it within class methods as required. Since it is a Singleton you do not spawn multiple connections no matter how many times you call for it.

/*
singleton pdo database class
use as follows:

get PDO object to work with
$pdo = PdoSingle::getInstance($host, $user, $pass, $db);

run a select query and get the results as associative array
$result = $pdo->getData($sql, $params);

run an insert/update/delete query and get the rows affected
$rowcount = $pdo->editData(sql, $params);

*/

class PdoSingle {
  static protected $instance = null;
  protected $conn;
  protected $host;
  protected $user;
  protected $pass;
  protected $database;
  protected $stmt;

  private function __construct($host, $user, $pass, $db) {
  		$this->host = $host;
  		$this->user = $user;
  		$this->pass = $pass;
  		$this->database = $db;
    $this->connectDB();
  }

  private function connectDB() {
    // Connect to the database
    try {
        $this->conn = new PDO('mysql:host=' . $this->host . ';dbname=' . $this->database, $this->user, $this->pass);
        $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
    catch(PDOException $e) {
        throw new Exception('DATABASE ERROR: ' . $e->getMessage());
    }
  }

  public static function getInstance($host, $user, $pass, $db) {
    // method for returning db
    if(self::$instance == null)
     {
      self::$instance = new PDODB($host, $user, $pass, $db);
     }
     return self::$instance;
  }

  public function getData($sql,$params) {
    try {
      $this->stmt = $this->conn->prepare($sql);
      $this->stmt->execute($params);
      return $this->stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    catch(PDOException $e) {
      throw new Exception('DATABASE ERROR: ' . $e->getMessage());
    }
  }

  public function editData($sql,$params) {
    try {
      $this->stmt = $this->conn->prepare($sql);
      $this->stmt->execute($params);
      return $this->stmt->rowCount();
    }
    catch(PDOException $e) {
      throw new Exception('DATABASE ERROR: ' . $e->getMessage());
    }
  }

  public function lastID() {
    try {
      return $this->conn->lastInsertId();
    }
    catch(PDOException $e) {
      throw new Exception('DATABASE ERROR: ' . $e->getMessage());
    }
  }

  public function __destruct() {
        $this->conn = null;
  }
}

traq · Apr 12, 2013

Of course, this introduces state into your program (which is something one generally seeks to avoid). From a practical standpoint, if you ever need two database connections (two databases, or a privileged and an unprivileged user account (good for security), or re-using the class for another project), you won't be able to.

An alternative, if you don't like dependency injection, is to make your DB class act like a connection "library."

<?php

class mysqlilib extends mysqli{

# add static methods that manage your mysqli instance(s)

protected static $_connlib = [];

# use mysqlilib::get_conn() to (create, if necessary, and) get a mysqli instance from the library
public static function get_conn( $name,array $credentials=null ){
    # "name" is an index so you can look up the connection you need
    # check if there is already a matching mysqli instance
    # something like 
    if( ! empty( self::$_connlib[$name] ) && (self::$_connlib[$name] instanceof mysqli) ){
        return self::$_connlib[$name];
    }

    # "credentials" are the host, user, pass, etc. so you can create any connection you need.
    # if $credentials is provided, use them to create a new mysqli
    if( $credentials && self::set_conn( $name,$credentials ) ){
        return self::$_connlib[$name];
    }

    return false;
}

# use mysqlilib::set_conn() to create a new mysqli instance in the library, without returning it
# (e.g., to set up your application's "default" connection,
#  just do:  mysqlilib::set_conn( 'default',['host','user','pass','dbname'] );  at the top of your script,
#  then, any function can do:  mysqlilib::get_conn( 'default' );  to use it.)
public static function set_conn( $name,array $credentials ){
    # extract credentials from array (you'll probably want validation/error checking too)
    list( $host,$user,$pass,$DBname ) = $credentials;

    # try to create a new connection
    $conn = new self( $host,$user,$pass,$DBname );

    # if it worked, add it to the library
    if( ! $conn->connect_errno ){
        # success!
        self::_connlib[$name] = $conn;
        return true;
    }else{
        # there was a connection error
    }
    return false;
}

# you can override mysqli's __construct() method if you want to make sure you *never* end up with a connection that is not added to the library
private function __construct( $host,$user,$pass,$DBname,$port=null,$socket=null ){
    return parent::__construct( $host,$user,$pass,$DBname,$port=null,$socket=null );
}

# add other methods as desired

}

Roger_Ramjet · Apr 12, 2013

Reusing the class for another project is no problem: just pass in different connection parameters. I have been using this class for simple projects since 2005 with no problems at all. It was just a simple class posted as an example and starting point for Langdale who is just learning OOP in PHP. I have boiled this down over the years to the minimum code required and it works just fine for 80% of the projects I have worked on. These are typically on shared hosting with 1 database so multiple concurrent connections to different servers are not required.

traq · Apr 12, 2013

Roger Ramjet;11026451 wrote:
Reusing the class for another project is no problem: just pass in different connection parameters.

whoops, misread that! sorry!

In general, I didn't mean to imply there was anything wrong with your example, or that it wouldn't work in many cases - just discussing the limitations of one approach over another.

PHP and MySQLi Reference database connection within user class

LLangdale

Ttraq

RRoger_Ramjet

Ttraq

RRoger_Ramjet

Ttraq