Best practice for executing parameterised queries in PHP?

Question

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?

By the way, all this stuff has nothing to do with parameterized queries but rather with queries in general — Your Common Sense, Jul 28 '13 at 17:41
@YourCommonSense I realise that, however last time I posted a question on this site - yourself in fact had a go at me for not using parameterised queries. Posting my question in this way was to ensure that the question was productive and any comments weren't side-tracked by going into the pro's & con's of parameterised queries. — , Jul 29 '13 at 12:52

score 2 · Accepted Answer · answered Jul 28 '13 at 17:39

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.

Your Common Sense · Answer 2 · 2013-07-29T05:41:56.400

1

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

Yes.

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

No.

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

No.
Your application have to be structured such a way to let connection to be closed painlessly before the page opening <head> tag, because all the database interaction have to be finished before any output started.

Best practice for executing parameterized queries in PHP?

To create a function of course, to make execution in one line instead of ten (and to handle all this opening/closing stuff), like this

$data = $db->get("SELECT `a` FROM `b` WHERE `c` = ?", $test1);

edited Jul 29 '13 at 05:41

answered Jul 28 '13 at 17:35

Your Common Sense

156,878
40
214
345

Why does the connection have to be closed before ``? – icktoofay Jul 28 '13 at 21:59
It is not necessarily have to be closed, but every database interaction apparently have to be finished before the page opening tag. – Your Common Sense Jul 29 '13 at 04:58
I've never known of such a requirement — can you cite a source for it? – icktoofay Jul 29 '13 at 04:59
Oh, just read something on *business logic from display logic separation*. I have no link at hand as I find the principle quite logical by itself, but I am sure it is easy googleable too. In short, no output should be started before all data got ready. – Your Common Sense Jul 29 '13 at 05:02
I understand that it's good practice to separate such things, but it's wrong to say that connections “have to be closed before […] ``” when, in fact, they don't. Perhaps “have to” should be changed to “should”. – icktoofay Jul 29 '13 at 05:04
Well, I have to agree it is bad phrasing but I take it as a some sort of pun, using the OP's literal wording to emphasize the contradiction. The idea was to tell that there should be no relation between database connection and opening/closing tags, as implied by the OP. I'll try to rephrase to something less ambiguous. – Your Common Sense Jul 29 '13 at 05:13

Best practice for executing parameterised queries in PHP?

2 Answers2