I would suggest using
using(MySqlConnection conn = new MySqlConnetion(cs)
{
conn.open();
//setup and execute query
} //conn gets closed here
Here, once you exit the using
block, the connection is closed.
Should I separate and create a PHP file for insert and a PHP file to get results from the database?
First, you may separate your data access from the business logic by moving data access code to a data access layer. For instance, if the select boxes are populated with the list of countries, then you'll have to call:
$this->db->listCountries();
from your business logic instead of doing directly the PDO stuff and the actual select ... from
.
The next step is to consider what should be done with the business logic which is processing the submission of the form. You might decide that this is totally independent from the code which populates the form, in which case a separate file makes sense; however, in most cases, you'll consider that both actions are related, and keep them in the same file.
From your question, I'm not sure how advanced you are in application architecture, so if you don't know the following concepts, make sure you learn them:
You may also be interested in using a framework (especially in PHP). Laravel looks promising and encourages you to use MVC.
I use require_once 'SELECT query file'
to populate the select boxes
While including files with require_once
is a viable approach, it may be difficult to maintain the list of files to include. What if you rename a file which is included in a few dozen of files? What if you forget to remove an include to a file you don't use any longer?
This is why often autoloading is used instead: with autoloading, you don't have to import anything manually, and it just works, magically. You may also be interested by PSR-4 (or PSR-0 if you don't use namespaces in your project).
Best Answer
The best practice for database connections is the same regardless of the language used.
You have a pool of open connections at the start of your application. You take a connection from the pool as late as possible. Return it to the pool as soon as possible.
Never hold a connection out of the pool unless it is being used. Don't have Connections as member variables in a class. I've seen lots of horrible data layer wrappers that do nothing more than duplicate the API interface while introducing unnecessary state with the Connection.
The API you program against may hide the pooling implementation. But you will still be responsible for upholding the take late, return soon rule.
You should code in a style that guarantees the connection is returned to the pool in the event of an exception. This could be achieved with try/finally constructs in Java. "using" blocks in C#. RAII in C++.