Best practice for using a mysqli object?

Question

I am finally getting around to converting from procedural PHP to OO PHP, and from mysql_XXX to mysqli. I have a quick question about mysqli and OOPHP. Say I create a new mysqli object, and want to use that object on different pages to access the database. Is the best practice to put the created object into a session variable

$_SESSION['dbSession'] = new mysqli('host','user','pass','database');

that I can pass around, or is there some other [real] best practice that I should follow?

Create a mysqli class and include it either a) In every single page b) Inside a header.php file that you would call to every page either way. Making a db class session dependent might not be the best idea — Sterling Archer, Sep 18 '13 at 19:42
(a) When you say "include it in every single page, do you mean to create a new mysqli object on every page I want to access the database with? (b) Just out of curiosity, why do you not believe I should stick it in a session variable? Thanks. — Brian, Sep 18 '13 at 19:45
Don't use mysqli, use PDO instead and always use prepared statements. — Gary Willoughby, Sep 18 '13 at 19:53
@webbiedave ... I did not know that. I have stored objects in sessions before, but hadn't tried a mysqli connection before. — Brian, Sep 18 '13 at 19:55
@ Gary Willoughby, I was just getting comfortable with mysqli, I'm not sure about jumping to PDO immediately. — Brian, Sep 18 '13 at 19:56
It won't take long to change your mind. Just start using instead of musing. — Your Common Sense, Sep 18 '13 at 20:07
@Brian you can adapt the solution described [here](http://stackoverflow.com/a/11369679/727208) for use with MySQLi. The only significant difference will be in the provider. — tereško, Sep 18 '13 at 21:58
@andrewsi, http://net.tutsplus.com/tutorials/php/pdo-vs-mysqli-which-should-you-use/ — Gary Willoughby, Sep 20 '13 at 08:44

score 0 · Answer 1 · answered Sep 18 '13 at 19:45

0

This will differ on a case by case basis, however I think it's safe to say that storing your db handler in the session is not good practice (extra memory usage, no need to save it really etc.).

You should look at dependency injection, read here. This will make unit testing a lot easier. But please, don't store it in the session. Please.

answered Sep 18 '13 at 19:45

Mark

1,376
9
16

So, would you recommend using RUJordan's method [from above]? – Brian Sep 18 '13 at 19:49
That depends on your application's structure. If you're working within some sort of framework, than a header file might not be the most ideal place to add your script (especially in MVC). What you need to find is the common denominator between all of your scripts (ie. a file that gets included everywhere). – Mark Sep 18 '13 at 19:53

Jeremy Harris · Answer 2 · 2013-09-18T19:52:56.867

I used to create a Database class as a Singleton like this. Not everyone likes this method, but its convenient for wrapping things up.

class Database
{
   private $_instance = null;

   public static function getInstance()
   {
      if( !(self::$_instance instanceof MySQLi) )
         self::createInstance();

      return self::_instance;
   }

   private static function createInstance()
   {
       // Create DB object here and store in self::$_instance
   }
}

There would obviously be passing of configuration data and what not, that's just a psudo-summary. You could use that class like:

$myDb = Database::getInstance();

This will recreate your database connection on each individual request, but will reuse the same connection if you make multiple queries in a single request.

As far as storing your MySQLi object in the SESSION, it just won't work. You are allowed to serialize objects and place them in the $_SESSION, but some objects are special and intentionally disallow it. For example, if you try to serialize a PDO object to store in the SESSION, it will throw this error:

Fatal error: Uncaught exception 'PDOException' with message 'You cannot serialize or unserialize PDO instances' in ...

This is really nothing more than a global, wouldn't you say? It makes unit testing a nightmare, and you will regret this choice if you ever need to connect to multiple databases. Dependency injection would be better suited, if we care about the semantics. — Mark, Sep 18 '13 at 19:51
@Marcel, I agree with what you are saying. DI is the better choice to go with, but requires some full on OOP with proper use of multiple design patterns. For someone who is "*converting from procedural PHP to OO PHP*", it may not be that simple without a major rewrite. — Jeremy Harris, Sep 18 '13 at 19:55
Well said, well said. However, it will be beneficial to the OP to know of something such as DI. — Mark, Sep 18 '13 at 19:59

Best practice for using a mysqli object?

2 Answers2