Search code examples
phpmysqlsqlmysqliparameterized-query

Best practice for executing parameterised queries in PHP?

Ok, so I have easily 100-200 queries on my website now, all parameterised.

Here's an example of one of them:

$mysqli = new mysqli('localhost', 'user', 'password', 'db_name');
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

$stmt = $mysqli->prepare("SELECT `x` FROM `y` WHERE `z` = ?");
$stmt->bind_param("s", $test);
$stmt->execute();
$stmt->store_result();
$stmt->close();

$mysqli->close();

So my question is, what is the best practice for opening/closing the connection to the database?

Is it best to leave the connection open, run multiple queries and then close the connection at the end of the page. Like so:

$mysqli = new mysqli('localhost', 'user', 'password', 'db_name');
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

$stmt = $mysqli->prepare("SELECT `a` FROM `b` WHERE `c` = ?");
$stmt->bind_param("s", $test1);
$stmt->execute();
$stmt->store_result();
$stmt->close();

$stmt = $mysqli->prepare("SELECT `x` FROM `y` WHERE `z` = ?");
$stmt->bind_param("s", $test2);
$stmt->execute();
$stmt->store_result();
$stmt->close();

$mysqli->close();

Or is it best to open the connection before each query, and then close immediately afterwards? Like so:

$mysqli = new mysqli('localhost', 'user', 'password', 'db_name');
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

$stmt = $mysqli->prepare("SELECT `a` FROM `b` WHERE `c` = ?");
$stmt->bind_param("s", $test1);
$stmt->execute();
$stmt->store_result();
$stmt->close();

$mysqli->close();


$mysqli = new mysqli('localhost', 'user', 'password', 'db_name');
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

$stmt = $mysqli->prepare("SELECT `x` FROM `y` WHERE `z` = ?");
$stmt->bind_param("s", $test2);
$stmt->execute();
$stmt->store_result();
$stmt->close();

$mysqli->close();

Currently I open the connection before the page opening <html> tag and then close it just after the closing </html> tag. Is this safe/good practice?

Solution

  • Opening and closing the connection takes resources, so the goal would be to open/close the connection as few times as possible, thereby executing as many queries as possible while a single connection is open.

    Whether this means opening the connection when the script starts and closing it when it finishes, or opening/closing for each query, is going to depend on the nature of your application.

    For instance, if most of your scripts have one or more queries, then opening/closing the connection and the beginning and end of the script is going to be best. If, on the other hand, most of your scripts are query-less, then establishing the connection when you want to execute a query is going to be your best bet.