Using same MySQL Connection in different PHP pages

Question

I am creating a simple Web Application in PHP for my college project. I am using the MySQL database.

I connect to the database in login.php. After connection I assign the connection to $_SESSION["conn"] and then redirect to main.php.

In main.php I write $conn = $_SESSION["conn"]. But the connection in $conn does not work.

I thought that as the login.php script ends, the connection gets closed. So I tried using mysql_pconnect instead of mysql_connect but that too does not work.

I know I can reconnect to the database in every PHP file. But I don't want to do this. I want to use the same connection in all PHP files.

Answer 1

The second request may not be served by the same web server process as the first, which means that you will have a completely separate set of database resources. You'll need to connect again in this new process in order to run queries.

Answer 2

Instead of saving the DB connection in a session you should make the connection calls in a separate file such as db.php and then require it from each of your scripts. For example, place your connection in db.php:

mysql_connect('...', '...', '...');
mysql_select_db('...');

and then bring it in in login.php:

require('db.php');
$res = mysql_query('...');

You can then do the same for each PHP file that needs access to the DB and you'll only ever have to change your DB access credentials in one file.

Answer 3

After connection I assign the connection to $_SESSION["conn"] and then redirect to main.php.

You'll probably want to read up on PHP sessions. You can't store resources (database connections, file handles, etc) in a session, because they can not be serialized and stored.

Keep in mind that each and every visit to a PHP script invokes a new instance of the PHP interpreter (via CGI, via FastCGI, or via a built-in module), and invokes a new instance of the script. Nothing is shared between script calls, because the entire environment goes away when the script exits.

The other answers are correct -- you'll need to connect to the database on every script call. Place the connection in a common include file for convenience.

Answer 4

What I normally have is a Connection class that pages will require in order to establish a connection. Something along the lines of:

class Connection    {
    public $dbConnection = null;
    public $isConnectionActive = false;
    private $dbServer = null;
    private $dbCatalog = null;
    private $dbUser = null;
    private $dbPassword = null;
}

This class handles opening and closing of the connection on any pages.