Lesson 2 - First database table and MySQL PHP drivers

In the previous lesson, Introduction to using databases with PHP for total beginners, we made a quick introduction to databases and created a MySQL database for our website. In today's lesson, we're going to create a user table and insert data into it.

Table

Open the database in the left panel. phpMyAdmin will then ask you if you would like to create a table. Before we do so, let's think back to the example with user table from last time. It had the following columns: first name, last name, date of birth and articles count. As we also said last time, each table should have a column whose value is unique for every and every row. With all of this in mind, we'll set it up to have 5 columns total. Let's enter the values we have settled on into phpMyAdmin (the table name will be user):

Note: In most cases, we name databases in singular form, which would mean that the correct name for our table would be "user". Both ways of naming databases are used by large companies and both have pros and cons. Plural form may suit beginners a bit more, however, singular form is usually preferred.

Lots of fields will now have appeared, but don't worry, we'll go over each and every one of them. The first column is for table column names. Let's go ahead and fill them in: user_id, first_name, last_name, date_of_birth, and articles_count. I usually use the table name as a prefix to the id column to avoid name collisions, but doing so is not necessary.

Let's move on to the second column, where the data types of each table column are chosen. INT is the default value, i.e. integers. There are a lot of different data types available, however, we will only be using a few for the time being (probably until the end of the course). Keep user_id as an INT, and then set first_name and last_name as VARCHAR, i.e. short text. Then, change the date of birth to DATE, and last of all, keep the number_of_articles column as an INT. Later, we will introduce a couple of more data types, but these will do for now

The third column, Length/Values, only applies to VARCHAR columns and is where you set the maximum number of characters a field can take. There, set the first name and last name's cap to 60 characters.

These first 3 columns are followed by a couple of other ones, which are not as important to us, so we'll leave them as is. The last thing we're going to fill in is the Index for the user_id column. Set the index to PRIMARY and click the A_I (AutoIncrement) check box next to it. Once yu have done that, you will have set the user_id column as the table's primary key. Keys (sometimes called indexes) allow us to identify items in a table. Every table should have a primary key even though they theoretically can be created without one. For example, if we wanted to delete a user, we would delete it based on the key (user_id). If we deleted it based on its name, we might end up deleting multiple items, since there could be more than one John Smith. Using user_id, we would only delete the one we wanted to. By having checked the Auto Increment forced user_id value to be automatically incremented for every next row, we are telling the database to give each user a numerical id.

By the way, notice the option to Add column(s), it may come in handy if you remember that you did not create enough columns after submitting the table. Let's go ahead and save our table.

The table will appear in the left panel. You could open it if you want, but it will be all empty. Here's what the SQL query that phpMyAdmin generated looks like:

CREATE TABLE user (
  user_id int AUTO_INCREMENT,
  first_name varchar(60),
  last_name varchar(60),
  date_of_birth date,
  articles_count int,
  PRIMARY KEY (user_id)
);

As usual, you won't have to remember any of that (I added it in so you know what is going on behind the scenes).

MySQL driver in PHP

Next, we're going to connect to the database from a script and add users to it. To do that, we'll need a database driver. Surprisingly, this part is a major problem in PHP and is a common pitfall for most beginners which makes them avoid databases overall. Here are the 3 database drivers available in PHP:

mysql

MySQL driver used to communicate with a database using the mysql_query() function. Since this driver uses outdated approaches and is pretty dangerous, you had to manually sanitize query parameters, it was marked as deprecated since PHP 5.5 and using it causes an error message. It doesn't even come with the newer PHP versions. Unfortunately, this driver is still being taught in old books or at some schools, be sure not to use it.

mysqli

mysqli stands for MySQL Improved. It's a hybrid driver, which can be used in both object-oriented or procedural ways. Procedural approaches have kind of disappeared from the PHP manual, which is blatantly inclined towards the object-oriented way of doing things. mysqli makes passing query parameters simple and sweet.

PDO

PDO stands for PHP Database Objects. It is the newest and high-quality object-oriented driver. One that is easy to use and supports other databases along with MySQL. Nowadays, it's better to avoid PHP functions that start with mysql overall.

Wrapper

As you can see there is almost no way to work with databases in PHP without objects. However, what you can do is use a wrapper. Basically, someone wrapped the object-oriented functions in the PDO driver for you in a way that you can understand them. In other words, you will be able to use them even if you have no clue what an object actually is. This is the approach we are going to use in this course.

First example - inserting user into the database

Let's start by creating a PHP project, and naming it DatabaseTest. Download today's lesson attachment below, extract it, and copy the wrapper (Db.php) into the folder you have saved your project in. Then, edit your index.php to make it look like this:

// Loads wrapper
require_once('Db.php');
Db::connect('127.0.0.1', 'web_database', 'root', '');
Db::query('
    INSERT INTO user (first_name, last_name, date_of_birth, articles_count)
    VALUES ("John",  "Smith",  "1984-11-03", 17)
');
echo('OK');

The example will load the database wrapper from Db.php file. All wrapper functions should now be accessible through the Db class and are called in the following format, Db::functionName().

First of all, we are connecting to the database via the Db::connect() function. There, we insert the hostname, database name, username, and password. On localhost, the credentials are usually as I entered them above. The credentials for your production database will be provided by your web hosting service provider.

Another wrapper function is the Db::query(), which executes a query in the SQL language in a database. SQL queries are entered as a string. I've split it into two lines to make it more readable, which is a very common thing to do. Notice that I used quotation marks for entering strings (just like in PHP) and used the year-month-day format for entering dates. We write numbers as they are. Beware! Never insert PHP variables directly into a query string! That would create a huge security vulnerability. (more on this next time).

Run the script several times with different data. Then, go back to PHPMyAdmin, open the user table, and click Browse. As you can see, the users have been added to the database:

Pretty easy, right? In the next lesson, Form handling and printing database data into table in PHP, we are going to elaborate on this.

Did you have a problem with anything? Download the sample application below and compare it with your project, you will find the error easily.