Can't Pass MySQLi Connection in Session in PHP

Can't pass mysqli connection in session in php

Yes, it is explicitly impossible.

See PHP Documentation here mentioning in a highlighted Warning: "Some types of data can not be serialized thus stored in sessions. It includes resource variables or objects with circular references (i.e. objects which passes a reference to itself to another object)."

MySQL connections are one such kind of resource.

You have to reconnect on each page run.

This is not as bad as it sounds if you can rely on connection pooling via mysql_pconnect(), but first see more background info on mysql_pconnect() in this article.

Cannot store database connection inside the $_SESSION

Anything that is stored inside a session needs to be serializable. Why do you want to store the connection inside a session at all? This does not make sense to me. Just reconnect with every request.

store mysqli_query result in session

Try this:

  if(!isset($_SESSION['query_result'])){
     $anything=mysqli_query($connection,"select something from table name where filed1='$variable' order by id DESC limit 70");
     while($res = mysqli_fetch_row($anything)){
        array_push($_SESSION['query_result'], $res);
     }
  } else {
     $anything = $_SESSION['query_result'];
  }
while($data=mysqli_fetch_array($anything)){
   print_r($data);
}

Storing database connection in a session variable

Even if you could do this (resource vs. data), this is a bad idea. You'll wind up with lots of concurrent open connections, which will blow your max connections very quickly... especially if its lifecycle is expanded beyond sub 100ms (depending on your queries) to 20 minutes or more. With open connections, something like MySQL also won't be able to reset its memory allocations properly, and the whole system sort of goes to hell. In short, this is not what DBs are for unless the only consumer of your code will be a single user.

As an alternative, I'd highly recommend caching technologies which are designed specifically to reduce database load and obviate connection times. Using something like, at its simplest, memcached will dramatically improve performance all the way around, and you'll be able to specify exactly how many system resources go into the cache -- while letting the database do its job of getting data when it needs to.

How to keep connection between SQL Server and PHP?

In your config file you may define some constants

<?php
define('DBHOST', 'your-dbhost');
define('DBNAME', 'your-dbname');
define('DBUSER', 'your-dbuser');
define('DBPASS', 'your-dbpass');
?>

If you like to use PDO...
Create a DB class that uses the singleton pattern to create a connection and store it in a variable.

<?php

class DB extends PDO {
    /**
     * Singleton instance of this class.
     * @var PDO
     */
    private static $instance;

    /**
     * @return PDO
     */
    public static function getInstance(): PDO {
        if (self::$instance !== null) {
            return self::$instance;
        }

        $dsn = 'mysql:host='.DBHOST.';dbname='.DBNAME.';charset=utf8';

        $opt = [
            self::ATTR_ERRMODE => self::ERRMODE_EXCEPTION,
            self::ATTR_DEFAULT_FETCH_MODE => self::FETCH_ASSOC,
            self::ATTR_EMULATE_PREPARES => false,
        ];

        self::$instance = new self($dsn, DBUSER, DBPASS, $opt);

        return self::$instance;
    }
}
?>

Now you can use your connection in every file

<?php
$db = DB::getInstance();

$db->select(...);

?>

---

Related Topics