So far, we have worked with MySQL, and we already have a good idea of what you can do with it. However, connecting to the client and performing queries manually is not our goal. What we want to achieve is that our application can take advantage of the database in an automatic way.
In order to do this, we will use a set of classes that comes with PHP and allows you to connect to the database and perform queries from the code.
PHP Data Objects (PDO) is the class that connects to the database and allows you to interact with it. This is the popular way to work with databases for PHP developers. PDO allows you to work with different database systems, so you are not tied to MySQL only.
In the following section, we will consider how to connect to a database.
Connecting to the database
In order to connect to the database, it is good practice to keep the credentials—that is, the user and password—separated from the code in a configuration file.
Let’s add the correct credentials for our database to the config/app.json file i.e the configuration file. If we have the configuration by default, the configuration file should look similar to this:
{
"db": {
"user": "root",
"password": ""
}
}
Developers usually specify other information related to the connection, such as the host, port, or name of the database. This will depend on how our application is installed, whether MySQL is running on a different server, and so on, and it is up to us how much information we want to keep on our code and in our configuration files.
In order to connect to the database, we need to instantiate an object from the PDO class. The constructor of this class expects three arguments: Data Source Name (DSN), which is a string that represents the type of database to use; the name of the user; and the password.
We already have the username and password from the Config class, but we still need to build DSN.
One of the formats for MySQL databases is <database type>:host=;dbname =<schema name>.
As our database system is MySQL, it runs on the same server, and the schema name is bookstore, DSN will be mysql:host=127.0.0.1;dbname=book store.
Let’s take a look at how we will put everything together:
$dbConfig = Config::getInstance()->get('db');
$db = new PDO( 'mysql:host=127.0.0.1;dbname=bookstore', $dbConfig['user'], $dbConfig['password'] );
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
Note also that we will invoke the setAttribute method from the PDO instance. This method allows us to set some options to the connection; in this case, it sets the format of the results coming from MySQL.
This option forces MySQL to return the arrays whose keys are the names of the fields, which is way more useful than the default one, returning numeric keys based on the order of the fields. Setting this option now will affect all the queries performed with the $db instance, rather than setting the option each time we perform a query.