Lesson 6 - Creating the database and PHP SQL drivers

PHP MVC Creating the database and PHP SQL drivers

In the previous lesson, Contact form, we created a contact form. We're now familiar with the main idea behind MVC, so we'll move on to some more interesting stuff. In today's tutorial, we'll create a database for users and their articles.

Database

The first thing we'll do today is create the database using the phpMyAdmin tool. If you'd rather use plain SQL or some other tool, there'll be complete creation scripts at the end of this lesson to accommodate your preferred method.

Let's move to PHPMyAdmin and create a new database (I named it mvc_db). Don't forget to set the collation to utf8_general_ci.

Creating a new database in phpMyAdmin

Open the database. Next, we'll create two tables.

Users

Create a user table, and add the following columns (fields) to it:

  • user_id - int type - primary key, autoincrement
  • name - varchar type - length 255
  • password - varchar type - length 255
  • admin - int type

The admin column sets whether a user is an administrator (0/1).

The overall table setup should look like this:

Creating a table for users in phpMyAdmin

(Don't forget to set the user_id column as the primary key and making it AI as well (this note was added because it can't be seen on the screenshot).)

Now that that's all taken care of, hit the Save button.

Add the UNIQUE key to the name column of the created table. The main reason behind this is to prevent users from choosing the same names:

A unique key in phpMyAdmin

Articles

Let's add one more table - article, which will consist of the following columns (fields).

  • article_id - int type - primary key, autoincreament
  • title - varchar type - length 255
  • content - text type
  • url - varchar - length 255
  • description - varchar - length 255

Here's the overall table setup:

Creating a table for articles in phpMyAdmin

(Again, don't forget to set the article_id column as the primary key and making it AI)

The url columns represent the URL address through which we'll access an article. For example, the "Home article"'s URL could be something along the lines of "home-article". Some systems display articles just based on a numeric ID, which is very uncomfortable for both users and search engines. Now, save the table.

Next, we'll add the home article so we could test the system out on something:

The home article of an MVC CMS in PHP

In case you prefer plain SQL, use the script below to create the database and tables:

CREATE DATABASE IF NOT EXISTS mvc_db
DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

USE mvc_db;

CREATE TABLE IF NOT EXISTS article (
  article_id int(11) AUTO_INCREMENT,
  title varchar(255),
  content text,
  url varchar(255),
  description varchar(255),
  keywords varchar(255),
  PRIMARY KEY (article_id)
);

CREATE TABLE IF NOT EXISTS user (
  user_id int(11) AUTO_INCREMENT,
  name varchar(255),
  password varchar(255),
  admin int(11),
  PRIMARY KEY (user_id)
);

ALTER TABLE user ADD UNIQUE (name);

INSERT INTO  article (
        article_id,
        title,
        content,
        url,
        description,
        keywords
)
VALUES (
NULL ,  'Home',  '<p>Welcome to our website!</p>

<p>This website is powered by a <strong>simple MVC framework in PHP</strong>. This is the home article loaded from the database.</p>', 'home',  'The MVC website&#8217;s home article',  'home, mvc, website'
);

Before we start working with the database, we'll go over theory a bit of theory.

Approaches for working with a database in PHP

The object-oriented world and the world of relational databases are very different. They represent two different philosophies, which I dare to say are incompatible.

Relational databases are a proven way of working with data. There are even fully-object-oriented databases available for use out there. However, they're usually not worth investing in, so they're not as popular. After the object-oriented programming revolution, a problem with storing data arose. Since relational databases don't work with objects, they can't store them. There are several ways to work around this problem.

1. Non-object-oriented programming

The first option is obviously to program everything entirely without objects. However, we would go against the mainstream, and wouldn't be able to use any third-party components and would end up making very low-quality code. Creating an architecture may not even be plausible this way. It makes no sense to explain advantages the of object-oriented programming here, you can read all about that in the introductory article of object-oriented programming in PHP.

2. CRUD Wrapper

The so-called wrapper approach allows us to work with a database as if it were an object. However, we would still have to communicate with it in the SQL language. In other words, we'll mix object-oriented and relational code. This approach is a sort of middle ground and requires us to bend the OOP philosophy a bit. The advantage to it is that it keeps the database's performance and features for the price of degrading OOP's principles slightly.

We usually get data from a database as values in an array, so we lose the option to add functionality to said data. As a workaround, we'll gather logic into a so-called manager. Although we could potentially map data partially onto existing classes, we'd never achieve the fully-featured object model.

Database wrappers are often implemented as CRUDs. Meaning that they're pre-made to perform four basic data operations (Create, Read, Update, Delete).

3. Object-relational mapping

Object-relational mapping (ORM) strictly follows the OOP ideology. Meaning that instead of arrays, we get objects straight from the database whose objects provide other methods. We don't communicate in the SQL language at all. We treat database tables as object collections and use OOP language syntax to work with them. This way, we're completely blinded from the fact that we're working with a relational database. Sounds great, right?

The catch is that SQL queries are automatically generated in the background since they're not always effective (which does take a small toll on performance). Another problem with ORM is that it's quite complex. We have to use a pre-made library that PHP doesn't provide, so we have to resort to a third-part solution (which is hard to get working and is hard to use as well).

People's thoughts on ORM are very controversial. Some people say that it's a modern way of working with data and that writing SQL queries manually is obsolete. Other people say that the idea of ORM is completely wrong since automatically-generated SQL code simply cannot be effective, and we still have to consider the final code so the "blindness" to the relational database is not absolute. Personally, I have a neutral view on ORM, so if somebody gave me a standardized and functional ORM along with a language, I would use it. However, since PHP doesn't have anything like that, I avoid using it. I don't find the payoff to be as high as other say it is.

4. Object-oriented database

Aside from relational databases, as you already know, there are object-oriented databases. These solve the incompatibility issue with the object-oriented and relational approaches. They provide the same comfort as ORM but internally have no need to convert object data into tables since it's all stored as objects. Theoretically, there is no performance-related or any other reason why they shouldn't replace today's relational databases. However, they're not used very much at the moment, so we can only hope for that to change. If you're interested in the aforementioned technology, take a look at the MongoDB project.

We'll use the second approach for our application - the wrapper. This approach fits our course since we're writing minimalistic and clear code. We'll make the actual wrapper in the next lesson - Database wrapper.


 

 

Article has been written for you by David Capka
Avatar
Do you like this article?
1 votes
The author is a programmer, who likes web technologies and being the lead/chief article writer at ICT.social. He shares his knowledge with the community and is always looking to improve. He believes that anyone can do what they set their mind to.
Unicorn College The author learned IT at the Unicorn College - a prestigious college providing education on IT and economics.
Thumbnail
Previous article
Contact form
Thumbnail
All articles in this section
Simple object-oriented CMS in PHP (MVC)
Thumbnail
Next article
Database wrapper
Activities (7)

 

 

Comments

To maintain the quality of discussion, we only allow registered members to comment. Sign in. If you're new, Sign up, it's free.

No one has commented yet - be the first!