All that typing that we did in the previous sections seems like a lot, just to make one single query to the database. That's why a lot of people tend to default to the old school systems of querying databases. It's just easier to use than PDO is. But, PDO has better security properties, so we're going to keep forging ahead and we'll make it easier on the way!
What we're going to do is create a database class that handles all this overhead for us, so we can use a simple php class to make queries.
In the course of creating this database class, you'll also learn some stuff about object oriented programming, classes, and objects.
First, let's define a class. A class is a container for an abstract thing. For example, if we're designing a car on paper, the car doesn't exist. In a sense, it's an abstract concept or thing. We'll do the same thing with our database class.
This code should be put into db.php in your webroot directory.
<?php
// create a namespace for the class below
namespace database;
// create an empty class named "db"
class db {
}This is an empty class definition. It includes no properties or methods.
What are properties? Properties are basically just variables that are contained within the class. You can make them public for all to use outside of the class, or private so that only the class can use them.
What are methods? Methods are just a fancy way of saying "class functions." They're basically a "method" of doing something. They're attached to the class and can also be public or private. If you define private methods, you can only use those methods within the class in other methods. If they're public, anything inside or outside the class can use them.
Let's define a method:
<?php
// create a namespace for our class below
namespace database;
// create a class named "db"
class db {
// do a query
public function query($query) {
return "here's your query results";
}
}So now we have a method that returns "here's your query results" when we call it.
How do we use it? Let's go back to your index.php file and replace everything in it with just the following:
<?php
// include our db class and use it
include "db.php";
use \database\db;
// create a new db object from class db
$db = new db;
// do a test query
echo $db->query("");
In your browser, you should see:
here's your query resultsThe next step is to allow the config object into our db object.
This code should be put into db.php in your webroot directory.
<?php
// create a namespace
namespace database;
include "config.php";
use \config\config;
// create a class named "db"
class db {
// set a private variable that only the class can access
// and store the created PDO object in it.
private $db;
// do a query
public function query($query) {
// to test, let's echo some config options to the screen
echo "host: " . config::$host . "<br>";
echo "username: " . config::$username . "<br>";
return "here's your query results";
}
}
Now your browser should show:
host: localhost
username: [username]
here's your query resultsLet's add a class constructor to automatically connect and configure PHP PDO. What is a class constructor? This is a special method that gets executed automatically when you create an object from a class. For example, when you $db = new db;, the class constructor get's executed. This is a great feature for getting everything set up properly.
This code should be put into db.php in your webroot directory.
<?php
// create a namespace
namespace database;
include "config.php";
use \config\config;
// create a class named "db"
class db {
// automatically set up mysql connection
function __construct() {
try {
// make a connection to the MySQL server using db configuration
$this->db = new \PDO("mysql:host=".config::$host.";dbname=".config::$dbName.";", config::$username, config::$password);
// turn on PDO options
$this->db->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
$this->db->setAttribute(\PDO::ATTR_DEFAULT_FETCH_MODE, \PDO::FETCH_ASSOC);
} catch(PDOException $e) {
// check to see if mode is "dev" or "staging"/"prod"
if (config::$mode == "dev") {
// if dev, then we want to show errors in our browser
echo "Couldn't connect to MySQL server:<br>";
echo $e->getMessage() . "<br>";
exit;
} else {
// if staging or prod, we only want errors to show up in our system log
syslog(LOG_ERR, "Couldn't connect to MySQL server: " . $e->getMessage());
exit;
}
}
}
// do a query
public function query($query) {
echo "host: " . config::$host . "<br>";
echo "username: " . config::$username . "<br>";
return "here's your query results";
}
}
We basically just copied our PHP PDO connection code into our constructor, with two differences. We've added backslashes \ before any PDO keywords and we've added $this.
In the previous class definition, we created a private property in the class named $db. This is where we will store our db object that is created with the connection string, aka
new PDO("mysql:host=".config::$host.";dbname=".config::$dbName.";", config::$username, config::$password);This is what we had before:
$db = new PDO("mysql:host=".config::$host.";dbname=".config::$dbName.";", config::$username, config::$password);and this is what we have now:
$this->db = new \PDO("mysql:host=".config::$host.";dbname=".config::$dbName.";", config::$username, config::$password);What changed? We've added a $this-> between the first dollar sign $ and db.
What does $this mean? This is class scoping. If you recall from previous sections about the concept of scopes, $this allows us to access class properties, aka private $db;
You can imagine in your mind that we can use $this to access what's within "this class".
If we had not added the $this->, $db would only be accessible within the method and not the entire class. Why do we want to have a private $db; property? We want to be able to access our database connection object anywhere within the class. This allows us to logically break up our code into more managable chunks. This makes it easier to read and reason about.
Let's change our query method to actually do a query. Again, we're going to be copying the query code into our method and changing $db into $this->db.
This code should be put into db.php in your webroot directory.
<?php
// create a namespace
namespace database;
include "config.php";
use \config\config;
// create a class named "db"
class db {
// set a private variable that only the class can access
// and store the created PDO object in it.
private $db;
// automatically set up mysql connection
function __construct() {
try {
// make a connection to the MySQL server using db configuration
$this->db = new \PDO("mysql:host=".config::$host.";dbname=".config::$dbName.";", config::$username, config::$password);
// turn on PDO options
$this->db->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
$this->db->setAttribute(\PDO::ATTR_DEFAULT_FETCH_MODE, \PDO::FETCH_ASSOC);
} catch(\PDOException $e) {
// check to see if mode is "dev" or "staging"/"prod"
if (config::$mode == "dev") {
// if dev, then we want to show errors in our browser
echo "Couldn't connect to MySQL server:
";
echo $e->getMessage() . "
";
exit;
} else {
// if staging or prod, we only want errors to show up in our system log
syslog(LOG_ERR, "Couldn't connect to MySQL server: " . $e->getMessage());
exit;
}
}
}
// do a query
public function query($query) {
try {
// prepare a query
$prep = $this->db->prepare($query);
// execute the query
$prep->execute();
return $prep;
} catch(\PDOException $e) {
if (config::$mode == "dev") {
// if dev, then we want to show errors in our browser
echo "There was a database error:
";
echo $e->getMessage() . "
";
exit;
} else {
// if staging or prod, we only want errors to show up in our system log
syslog(LOG_ERR, "There was a database error: " . $e->getMessage());
exit;
}
}
// return default empty array
return [];
}
}
In our index.php file, we can then do:
<?php
// include and use database class
include "db.php";
use \database\db;
// do query
$db = new db();
$results = $db->query("select lastName, firstName from names")->fetchAll();
// echo the results to the browser
foreach ($results as $res) {
echo "{$res["lastName"]}, {$res["firstName"]}";
}You should get something like this:
Smith, Jim
Rocker, Bob
Kella, Shella
Toga, Sara
Flauntana, Hannah
Found, Lost
Donkey, ManWhat we've done in this section is create a class that we can then push off all the extra boilerplate typing into one file and we can significantly cut down the size of our files that actually need database access.
This will make code much easier for programmers to read and understand now and into the future...