I have a webpage with some mysql querys, I must start new db connection and db close after every query or its more efficiently start an only one db connection at the top of the code and close at the bottom of the script? Thanks!
Add a comment
|
4 Answers
Never create a new connection for every query; it's very wasteful and will overburden your MySQL server.
Creating one connection at the top of your script is enough - all subsequent queries will use that connection until the end of the page. You can even use that connection in other scripts that are include()ed into the script with your mysql_connect() call in it.
3 Comments
webbiedave
Hopefully he's connecting with PDO, not
mysql_*: $dbh = new PDO($dsn, $user, $password);
moto
@Bojangles still new to php and this definitely interests me. So i have a database class with function of __CONSTRUCT making the database connection, assigning it to a public $connection. I subsequently always call by doing $database->connection->query(""); but I'm guessing that I should (after instantiating the class just once, and there by connecting to the database), simply do $database->query(""); yes?
moto
I must be doing something wrong, because after instantiating $database = class database(); I tried to not make a connection and go straight to $database->query(""), but it didn't work despite my __CONSTRUCT being set to making a connection
I prefer to create some sort of class called Database of which upon creation will create a database connection using mysqli (OOP version of mysql).
This way i do not have to worry about that and i have a static class that has access to the database.
class MyAPI {
private static $database = false;
public static function GetDatabase() {
if (MyAPI::$database === false) {
MyAPI::$database = new Database(credentials can go here)
}
return MyAPI::$database;
}
}
Now your system with the includsion of your API and your database can access the database and its initialization code does not have to be peppered around your files depending on what part of the program is running and your database/connection will not be created if it is not needed (only until it is called).
Just for fun i also like to have a function in my Database class that will perform a query and return its results, this way i do not have to have the SAME while loop over and over again throughout my code depending on where i make these calls.
I also forgot to answer your question. No multiple connections! its baaaad.
Comments
Only create one connection. You don't even have to close it manually.
1 Comment
Your Common Sense
Does it mean that if you are using mysql_pconnect(), you have to close connection manually?
multiple connexion is better for security and tracking. if you're going though a firewall between front and back there will be a timeout defined so a single connection will be a problem. but if your web server is on the same host as mysql, a single connection will be more efficient.
