Proper use of mysqli->close();

Question

database.config.php

<?php
class Database
{

    private $host = "localhost";
    private $db_name = "trial";
    private $username = "root";
    private $password = "";
    public $conn;

    public function dbConnection()
    {

        $this->conn = null;    
        try
        {
            $this->conn = new PDO("mysql:host=" . $this->host . ";dbname=" . $this->db_name, $this->username, $this->password);
            $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);   
        }
        catch(PDOException $exception)
        {
            echo "Connection error: " . $exception->getMessage();
        }

        return $this->conn;
    }
}
?>

class.php

<?php
include_once 'database.config.php';
class USER
{   
    private $conn;

    public function __construct()
    {
        $database = new Database();
        $db = $database->dbConnection();
        $this->conn = $db;
    }

    public function runQuery($sql)
    {
        $stmt = $this->conn->prepare($sql);
        return $stmt;
    }
}
?>

index.php

<?php
require_once 'class.php'; 
$user = new USER();

$teacher_fetch = $user->runQuery("SELECT * FROM teacher WHERE Id=:_id");
$teacher_fetch->execute(array(":_id"=>$_SESSION['teacher_id']));
$fetch_teacher = $teacher_fetch->fetch(PDO::FETCH_ASSOC);

echo $fetch_teacher['Name'];
?>

As it's a best practice to use close() in last where should I use it in the index.php file?

Option 1

$teacher_fetch->execute(array(":_id"=>$_SESSION['teacher_id']));

//close connection here

$fetch_teacher = $teacher_fetch->fetch(PDO::FETCH_ASSOC);
echo $fetch_teacher['Name'];

Option 2

$teacher_fetch->execute(array(":_id"=>$_SESSION['teacher_id']));
$fetch_teacher = $teacher_fetch->fetch(PDO::FETCH_ASSOC);

//close connection here

echo $fetch_teacher['Name'];

Option 3

$teacher_fetch->execute(array(":_id"=>$_SESSION['teacher_id']));
$fetch_teacher = $teacher_fetch->fetch(PDO::FETCH_ASSOC);
echo $fetch_teacher['Name'];

//close connection here

Or Somewhere else?

Also, how should I close it?

$user->close(); OR $teacher_fetch->close(); OR $fetch_teacher->close(); OR Something else?

Answer 1

The fact that mysqli has nothing to do with PDO aside, there are several things about your code at whole:

• this "class Database" from some article is a dummy. It is absolutely useless and there is not a single reason to prefer it over original PDO. Let alone some flaws in the code inside.
• under no circumstances you should create a new database connection in the constructors of your classes. A single existing instance should be passed as a constructor parameter instead.
• runQuery() function is misplaced, misnamed and useless. It just performs PDO::prepare() which you can always call directly. It makes whole User class a dummy as well, just a useless chunk of code.
• some User-related method should be written in this class instead

Given all the above let's rewrite your code

database.config.php will be a code from my article How to connect to MySQL using PDO:

<?php
$host = '127.0.0.1';
$db   = 'test';
$user = 'root';
$pass = '';
$charset = 'utf8mb4';

$options = [
    \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
    \PDO::ATTR_EMULATE_PREPARES   => false,
];
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
     throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

here it will give you an instance of PDO class.

user.class.php

<?php
class User
{   
    private $conn;

    public function __construct(PDO $pdo)
    {
        $this->conn = $pdo;
    }
    public function findTeacher($id)
    {
        $stmt = $this->conn->prepare("SELECT * FROM teacher WHERE id=?");
        $stmt->execute([$id]);
        return $stmt->fetch();
    }
}

here you have a findTeacher() method logically placed in the user class.

index.php

<?php
require_once 'database.config.php';
require_once 'user.class.php'; 

$user = new User($pdo);
$teacher = $user->findTeacher($_SESSION['teacher_id']);
echo $teacher['Name'];

As you can see, this code is much more concise and logical, actually utilizing some basic OOP, as opposed to your current code which could be written (again more concisely) without any classes.

As for your question, in the average PHP script you don't have to close neither the statement nor the connection. PHP will close it for you automatically.