mysqli connection in classes

[pl_towers]

Hi,

I am sure this is a simple question but I want to be able to use a $mysqli connection within all my classes, do I have to use a DB class which all my other classes use?

 

Basically how do i use a external mysqli connection within my classes, got to be simple as it is necessary for every class lol

[Mchl]

A factory or a singleton pattern is useful for such situations

 

http://www.php.net/manual/en/language.oop5.patterns.php

[nankoweap]

i'm not so sure about a connection being required of every class. i reckon it depends upon your application's architecture/design. in my web applications a subset of business classes provide model-services (classes that interact directly with the database and both abstract from and provide the view with a clean, simple interface to interact with the database) all extend a base business class whose constructor obtains a connection and whose destructor closes the connection.

 

here's that base class:

 

<?php

include_once('classes/AppInfo.php');

class BaseBo {

  protected $appInfo;
  protected $dbc;
    
  function __construct() {
  
    $this->appInfo = AppInfo::getInstance();
    $this->dbc = mysqli_connect(
              $this->appInfo->getMysqlHost(),$this->appInfo->getMysqlUser(),
              $this->appInfo->getMysqlPassword(),$this->appInfo->getMysqlDatabase())
        or die('Error connecting to database.');
  }
  
  function __desctruct() {
    mysqli_close($this->dbc);
  }
}
?>

 

and here's a snippet of a business class that extends BaseBo...

 

<?php

include_once('classes/bo/BaseBo.php');
include_once('classes/bean/Bike.php');
include_once('classes/bean/User.php');

class BikeManager extends BaseBo {

  public function getBike(User $user, $bikeId) {
    if (is_null($user) || $bikeId <= 0) return;

    $userId = $user->getId();
    $b = new Bike();
    $b->setId($bikeId);

    $sql = "select a.b_id, a.m_id, a.model, a.b_year, b.description " .
             "from bike a, make b " .
            "where a.u_id = $userId " .
                  "and a.b_id = $bikeId";
    $result = mysqli_query($this->dbc, $sql);
    $row = mysqli_fetch_array($result);
    mysqli_free_result($result);

    if ( ! is_null($row)) {
      $b->getMake()->setId($row['m_id']);
      $b->getMake()->setDescription($row['description']);
      $b->setYear($row['b_year']);
      $b->setModel($row['model']);
    }

    return $b;

  } // getBike

} // BikeManager
?>

 

in this simple example, if a view component (i.e. a web page) requires bike information, the php snippet within it will create a new BikeManager and ask it for a particular bike's information.

 

there are some issues with this approach, but i'm a php noob and still learning about the internals of how the php engine, mysqli, etc actually work. i may change this approach to achieve better performance, resource management... having all this code localized to a specific application layer, though, lends itself well to change without polluting the other application layers.

 

jason

[ch1326]

class MySQL 
{
  var  $host ;
  var  $dbUser ;
  var  $dbPass ;
  var  $dbName ;
  var  $dbConn ;
  var  $dbconnectError ;

function MySQL (  $host , $dbUser , $dbPass , $dbName ) {
	$this->host   = $host ;
	$this->dbUser = $dbUser ;
	$this->dbPass = $dbPass ;
	$this->dbName = $dbName ;
	$this->connectToDb() ;
}


function connectToDB() {
	$this->dbConn = @mysql_connect($this->host , $this->dbUser , $this->dbPass )  ;
	if ( !$this->dbConn )
	{
	trigger_error ('could not connect to server' ) ;
	$this->dbconnectError = true ;
	}
	else if (!@mysql_select_db ( $this->dbName , $this->dbConn) )
	{
	trigger_error ('could not select database' ) ;  
	$this->dbconnectError = true ;                     
	}
}


function isError()
   {
      if  ( $this->dbconnectError )
       {
         return true ;
       }
       $error = mysql_error ( $this->dbConn ) ;
       if (empty ($error))
         {
           return false ;
         }
         else
         {
           return true ;   
         }

   }


 function query ( $sql )
{
if (!$queryResource = mysql_query ( $sql , $this->dbConn ))
{
        trigger_error ( 'Query Failed: ' . mysql_error ($this->dbConn ) . ' SQL: ' . $sql ) ;
} 
return new MySQLResult( $this, $queryResource ) ; 
}
}



class MySQLResult 
{
   var $mysql ;
   var $query ;

   function MYSQLResult ( &$mysql , $query )
   {
     $this->mysql = &$mysql ;
     $this->query = $query  ;
   }

    function size()
    {
      return mysql_num_rows($this->query) ;
    }

    function fetch()
    {
       if ( $row = mysql_fetch_array ( $this->query , MYSQL_ASSOC ))
       {

         return $row ;
       }

       else if ( $this->size() > 0 )
       {
         mysql_data_seek ( $this->query , 0 ) ;
         return false ;
       }
       else
       {
         return false ;
       }         

    }

   function isError()
    {
      return $this->mysql->isError() ;
    }


}
?>


Hope this will help.

[PHP Monkeh]

Look at what Mchl suggested, creating your database class to extend MySQLi is my preferred method.  Google 'PHP singleton' and you're bound to find loads of examples.

 

And to nanko: You seem to be creating a new connection to the database for every class that requires it that way, whereas if you used the Singleton method in your AppInfo class for the connection only 1 connection would be made.

[nankoweap]

And to nanko: You seem to be creating a new connection to the database for every class that requires it that way, whereas if you used the Singleton method in your AppInfo class for the connection only 1 connection would be made.

 

yep. that's the primary issue i have with this implementation and why it's likely to change. there are a number of use-cases where multiple biz objects will be utilized - each having its own connection. not too kind - resource wise.

 

preciate the feedback.

 

jason