Opening and closing MySQL connections

Hi,

I recently learned that in some conditions closing the database connection is recommended but I still don’t have a clear idea about it so I am asking if my below code is fine for what it is doing and if I am missing anything that would improve the performance or prevent issues that I am not aware of.

It’s just a basic database connection class, and I have functions with simple queries to get content (website pages) from the database:

<?php
// Database info
define('DB_HOST', '...');
define('DB_NAME', '...');
define('DB_USER', '...');
define('DB_PASS', '...');

// Database connection
class DB {
	public static $db;
}
DB::$db = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
DB::$db->set_charset('utf8');

// Get page
function get_page($id) {
	$query = "SELECT * FROM pages WHERE page_id = $id";
	$page = mysqli_fetch_assoc(DB::$db->query($query));
	return $page;
}
?>

Thanks for any suggestions.

There’s not really any point in closing a database connection as PHP normally closes any non-persistant database connections when all scripts have finished running. Persistant database connections generally aren’t used.

btw, what’s the origin of the value for $id that is being passed to the get_page function?

1 Like

@SpacePhoenix, thank you for the clarification.

Actually, I use page_name in my function, like:

function get_page($page_name) {
	$query = "SELECT * FROM pages WHERE page_name = '$page_name'";
	$page = mysqli_fetch_assoc(DB::$db->query($query));
	return $page;
} 

I get $page_name from the page URL, after a .htaccess rewrite like:

RewriteRule ^([a-z0-9-]+)/$ page.php?p=$1

In my page.php file:

$page = get_page($_GET['p']);

Although isn’t it generally considered general good practice to close connections when you’re done with them? (This is more of an academic question, I suppose, than a functional one)

FYI @nayen that would be using http://www.w3schools.com/php/func_mysqli_close.asp

But @SpacePhoenix is definitely right, when the PHP script is done it’ll terminate MySQL connections on its own.

That’s how I feel about it and why I close connections when I’m done with them.

A few extra characters that aren’t usually needed, unless maybe

  • the script has a ways to go after you’re done with the connection
  • you want to connect to a different database and don’t want to risk using the first by mistake
  • your host is scolding you for wasting resource use

So yes, in like 99.999999% percent of the time the close() isn’t needed and it’s absence wont make any difference at all.
On the other hand, is one more line of code that big of a deal?

Hi @jeffreylees and @Mittineague, and thanks for your contribution.

My real confusion was when it would be necessary to close the connection and if it was necessary in my case of a website that displays content from the database, with no extra functionalities like complex applications.

In general it is not, but I need to know if and when I should use it like anything new I learn, instead of having that line “just in case”, without being able to tell why I have it there or how it really helps my website performance.

Closing the connection is not a bad idea if you know the last query that will run. Though in todays world with OOP depending on your code, it could be difficult to locate a spot to do this.

It is important to remember that you do not want to close and open connections to the same database if you can avoid it, due to the connection part (while fast on MySQL compared to other dbs, it is still an expensive process).

2 Likes

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.