CODEWITHSUNDEEP
phpmysqlmysqli
Dre_Dre
Dre_Dre

Reputation: 755

When does PHP connect to the database?

When does PHP connect to a database? For example:

$db = new mysqli('host', 'user', 'password', 'database');

When the mysqli class is initiated, is that when php connects, or is it only when this is called:

$stmt->execute();

I want to know if I write a line like this, if php will attempt to connect or will it wait until $stmt->execute(); is called:

$stmt = $db->prepare("SELECT fname, lname WHERE id =?");

Upvotes: 4

Views: 125

Answers (1)

apokryfos
apokryfos

Reputation: 40681

Here's a breakdown of your script:

$db = new mysqli('host', 'user', 'password', 'database');

This will create a new connection to the database at host using credentials user and password and database schema database. By contrast 

$db = new mysqli('p:host', 'user', 'password', 'database');

will attempt to reuse an existing connection from a pool of available connections (source)

The physical TCP socket remains active until mysqli->close is called or the script terminates. You can confirm this is the actual behaviour by doing:

$db = new mysqli('host', 'user', 'password', 'database');
sleep(1000);

and then running show processlist on your MySQL database to confirm that there's a physical connection to the database that connection may be active or sleeping but the physical link will be there and the port in use.

$stmt = $db->prepare("SELECT fname, lname WHERE id =?");

Will send a request to the MySQL server to prepare that statement and you will get back a reference to the prepared statement. Note that preparing the statement is something that happens on the database server unless you're using PDO and emulated prepares. 

$stmt->execute();

Will execute the previously prepared statement. This again happens on the database server and a result resource is opened which can be used to retrieve the result. Note that unlike PDO, in MySQLi the statement resource acts as both the statement as well as the result and is what you will be using to retrieve the result set (either as a stream or all at once). The statement needs to be closed or otherwise disposed before you can execute another statement on the same link. This is why you can call $stmt->close() when you need to do this.

In order to close a connection you need to run $db->close() or wait for the script to terminate. Note that if you are using a persistent connection then the physical link is not closed but rather it is returned to the connection pool and is made available to re-use. The pools is necessary because concurrent users should not share the same physical link.

Upvotes: 3

Related Questions