PHP Lessons - Lesson 3 - Working with MySQL DB.

You might think it’s too early to begin the third lesson with working in MySQL. But trust me, it’s not. Learning PHP as a web programming language without learning how it interacts with databases is like having a computer without internet. Yes, you can work on such a computer, but you won’t be able to get information. So let’s grit our teeth and start writing SQL queries, even if we know nothing about the SQL language yet.

SQL (Structured Query Language) — a universal computer language used to create, modify, and manage data in relational databases. SQL is based on record-based operations in tables.

PHP can work with many databases: MySQL, PostgreSQL, Oracle, and others. We will learn PHP with MySQL, since this combination is very popular for building websites. Moreover, learning MySQL will help you work with other databases in the future, since SQL is a universal language and its syntax is nearly identical across relational databases.

MySQL is a free relational database management system (RDBMS). MySQL is owned by Oracle Corporation, which acquired it along with Sun Microsystems. It is distributed under the GNU General Public License or a commercial license.

We will have many lessons on SQL itself. But for now, let’s start with the basics of connecting to a database. To do that, we’ll create another method in our simpleCMS class:

public function connectDB() {

}

This will allow us to connect to the database from anywhere in our code where we have an instance of this class. Very convenient.

We’ll also need variables for the MySQL server, the DB user, and the DB name:

class simpleCMS {

  public $host = 'localhost';
  public $username = 'root';
  public $password = '';

We’ll define default values for these variables in case we forget to explicitly define them later. I’m using Denwer, so those are the default MySQL credentials for me. If you’re using another environment like a hosting provider or dedicated server, you should use your own MySQL user credentials.

Now let’s use the mysql_connect() function to create the connection to MySQL:

public function connectDB() {
  $link = mysql_connect($this->host, $this->username, $this->password);
  if (!$link) {
    die('Connection error: ' . mysql_error());
  }
  return $link;
}

Let’s walk through how the mysql_connect() function works. It requires three parameters: DB host, DB username, and DB password. Then we use the die() function to show an error message if the connection fails. The variables $this->host, $this->username, and $this->password are class properties, which we’ve defined with default values — no need to redefine them inside the method.

The mysql_connect() function returns a connection resource, similar to file or folder resource types. So, we should close this connection after finishing to free up memory. For now, we can remove the success message — the main thing is to make sure there are no errors.

Now let’s call our method at the beginning of index.php, right after including the class file:

$obj = new simpleCMS(); // create an instance of the management class
$db_connection = $obj->connectDB(); // create a connection

Wherever we need to connect to the database, we’ll include the class file, instantiate the class, and call connectDB(). As you can see, there’s nothing complicated about it.

Just like we create a connection, we should also close it after finishing with mysql_close(). I won’t create a separate method for closing (although it’s a good idea if needed later). For now, just add this code:

include_once('class/simpleCMS.php'); // include the class file
$obj = new simpleCMS(); // create an instance of the class
$db_connection = $obj->connectDB(); // connect to the DB

// do something with the DB

mysql_close($db_connection); // close the connection

I hope I’ve explained everything clearly. In the next lesson, we’ll start writing MySQL queries. If anything is unclear, feel free to ask in the comments.