Mysql Connection Class

Question

Hi -

I'm writing some php code, and I'm frequently accessing my MySQL database to read and update fields.

My current code includes a class called dbconnnect, and I use it as follows:

class edit_data extends dbconnect {
//<some php code>
parent::connect();
//<get info from database>
parent::disconnect();
//<evaluate data>

My question - this is the most efficient way to connect and disconnect from a MySQL database? (Keep in mind, I almost always connect to the same database, so no need to redefine the connection parameters every time).

I was considering running the connect in the constructor, so then I could just write

$connector = new dbconnect();

but I realized I'm not actually saving much by doing this - right?

Thanks.

Answer 1

Just make sure that the code for db connection/disconnection is included/run automatically at the start and end of each file, without needing to do it separately for every file. Make sure this code is stored in 1 location and included in all other files, so you can change it easily when needed. As long as you do these things, the rest is just personal preferences for how you want to connect/disconnect from the db.

I would also recommend a framework such as CodeIgniter for taking care of common tasks such as this behind the scenes for each file.

Answer 2

The "right" way would probably be to do it like the MySQLi and PDO extensions - open the connection in the constructor and close it in the destructor. The "efficient" way is to check the connection in your query() method and (r)open it if necessary.

In both approaches you avoid creating a separate connect() method, and thus remove the risk of forgetting to call it in your script.

Answer 3

Unless you know you are always going to connect to the database every time, I wouldn't put the connect piece in the constructor. If you don't need to connect, you shouldn't, it's expensive. Your constructor should only accept the connection parameters. Although it looks like you may be using static classes, so the constructor would get executed.

As Krzysztof mentioned, you should connect on demand. In my database class, I have all queries eventually go through an "execQuery" function. That function checks if there is a connection to the database or not. Having a single central query function also allows you to do things like record all queries that were run in a session, add timing, logging or anything else you need to do.

Having the disconnect in the destructor is the proper place.

Answer 4

Our codebase has many paths and makes a lot of use of caching.

We only connect with the database when the first query is executed. (so actually on the ->query() method).

We let PHP disconnect once the script is over, and don't explicitly call ->disconnect

Answer 5

By using

$connector = new dbconnect();

and not

parent::connect();

you are essentially decoupling your edit_data class with the dbconnect class. What the means for you is that:

1. your edit_data class can now have more than one connection by using multiple dbconnect objects (connection pooling)
2. your edit_data class can (in the future) use something other than dbconnect and won't have to change any other code. With parent::connect(), if you ever change to extending some another class, you'll have to make sure the new class will support the existing semantics

Answer 6

Juts be sure of reusing you connection:

Review new_link parameter:

If a second call is made to mysql_connect() with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned. The new_link parameter modifies this behavior and makes mysql_connect() always open a new link, even if mysql_connect() was called before with the same parameters. In SQL safe mode, this parameter is ignored.

http://php.net/function.mysql-connect

Answer 7

I think deriving your "edit_data" class from your "dbconnect" class demonstrates confusion of logic. Is your data editing object a special type of database connection? Or maybe it would make more sense to say the data editing object needs to use a database connection? (This is the difference between IS A and HAS A.)

Although it seems to be falling out of fashion now, you can use a singleton factory call to get the database handler. Then any function which needs it can simply call it. If you make the handler self-initializing the first time it has to do any work, then nothing needs to even worry about initializing it. (To do this, check if the instance variable containing the handle is a resource handle - if not, call the initializer. Remember to decide what to do if the connection fails.) Then you just need a way for the handler to find it's configuration. I would have the factory call do that, not the initializor.

A variant way of doing that is for the constructor to get the current database handler and put a reference to it in an instance variable. This can be done several ways. Requiring it in the constructor might work, or you can fall back on the singleton factory call again. This technique gives the object constructor a chance to deny instantiation if the database handler won't initialize (the factory call can check that).

Answer 8

• Connecting is expencive so, don't connect and disconnect for each query.
• If possible use mysqli instead of mysql, it is generally faster
• If you are using mysql not mysqli, use mysql_pconnect instead of just mysql_connect
• php closes all the open db connections, so there is no need to explicitly do so
• share connections between queries whether you connect at the start of the script or on the first query is up to.