How to keep connection between SQL Server and PHP?

Question

I have 2 files login.php and employee.php.

When I log in to the website, I create a connection to my database. How can keep that connection so that I can call some query statement in employee.php file?

This is login.php:

<?php
if (isset($_POST["login"])) {
    $server_name = "my-server.database.windows.net";
    $user_name = $_POST['user_email'];
    $password =  $_POST['user_password'];
    $connection = array("Database"=>"BankingDB", "UID"=>$user_name, "PWD"=>$password);

    $conn = sqlsrv_connect($server_name, $connection);

    if ($conn) {
        session_start();
        $_SESSION['user_name'] = $user_name;
        $_SESSION['password'] = $password;
        setcookie("type", $user_name, time() + 3600);
        header("location:index.php?page=employee");
    }
    else {
        echo "<div class='alert alert-danger'>Wrong Email Address or Password!</div>";
        die(print_r(sqlsrv_errors(), true));
    }
}
?>

This is employee.php:

<?php
session_start();
$server_name = "my-server.database.windows.net";
$connection = array("Database"=>"BankingDB", "UID"=>$_SESSION['user_name'], "PWD"=>$_SESSION['password']);
$conn = sqlsrv_connect($server_name, $connection);

if ($conn) {
    echo "Connection established: " . $server_name;
}
else {
    echo "Connection could not be established";
    die(print_r(sqlsrv_errors(), true));
}
$id = $_GET['employee_id'];
$sql = "SELECT EmployeeName, EmployeeCode FROM Employee WHERE EmployeeCode = $id;";
    $query = sqlsrv_query($conn, $sql);
    $row = sqlsrv_fetch_array($query);
?>

I can save the user_name and password with $_SESSION variable. I also have many files that also use this connection.

I did try to store the $conn in $_SESSION but it doesn't work.

<?php
    // login.php
    $conn = sqlsrv_connect($server_name, $connection);
    $_SESSION['connection'] = $conn;
?>

How can I keep that connection so that I don't have to reconnect to the database every time I need to query?

https://stackoverflow.com/questions/2125403/cant-pass-mysqli-connection-in-session-in-php — steven, Dec 11 '20 at 14:38
It's been a *long* time (probably a decade) since I worked with PHP, however, i recall the "norm" would be to store the connection details in a separate file and `include` that in the pages that need it. You really shouldn't be redeclaring the connection details in every page; it'll be a nightmare if those connection details ever change. — Thom A, Dec 11 '20 at 14:39
you may use a public static property from a class to store your connection. So you may be able to access it from each file. — steven, Dec 11 '20 at 14:44
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Dec 11 '20 at 14:48
You can't do that. You have to open a new connection on every request. — Dharman, Dec 11 '20 at 14:49
You can, however, share the same connection between different parts of your application, _within the same request_. as mentioned earlier, this is generally done by making the connection object available - either globally or by getting a property from a class or something like that. — ADyson, Dec 11 '20 at 17:35

score 2 · Accepted Answer · answered Dec 11 '20 at 14:53

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(...);

?>

Thank you, I will try it with my code. – huy Dec 11 '20 at 14:55 — huy, Dec 11 '20 at 14:55

How to keep connection between SQL Server and PHP?

1 Answers1