Lesson 2 - First database table and MySQL PHP drivers

PHP Databases for beginners 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):

Creating table in phpMyAdmin

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.

Creating table in phpMyAdmin

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:

Users in MySQL table

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


 

Download

Downloaded 100x (2.61 kB)
Application includes source codes in language PHP

 

 

Article has been written for you by David Capka
Avatar
Do you like this article?
2 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.
Activities (5)

 

 

Comments

Avatar
Shaleen Baral
Member
Avatar
Shaleen Baral:1/28/2017 1:32

Is the db.php your creation or?

 
Reply 1/28/2017 1:32
Avatar
David Capka
ICT.social team
Avatar
Replies to Shaleen Baral
David Capka:1/28/2017 9:47

Yes, it's our wrapper for working with the database.

Reply 1/28/2017 9:47
You can walk through a storm and feel the wind but you know you are not the wind.
Avatar
Shaleen Baral
Member
Avatar
Replies to David Capka
Shaleen Baral:1/29/2017 1:31

Exactly what license does this source code use?

 
Reply 1/29/2017 1:31
Avatar
David Capka
ICT.social team
Avatar
Replies to Shaleen Baral
David Capka:1/29/2017 4:08

It's under ICT.social free license. Basically, the only thing you need to do is to keep our link in the source code. More at https://www.ict.social/licenses

Reply 1/29/2017 4:08
You can walk through a storm and feel the wind but you know you are not the wind.
Avatar
Daouda Sanogo
Member
Avatar
Daouda Sanogo:2/4/2017 0:49

Hello,I am having this issue.. may someone help me please? Thanks!

Fatal error: Undefined class constant 'PDO::MYSQL_AT­TR_INIT_COMMAN­D' in C:\Program Files (x86)\EasyPHP-Devserver-16.1\eds-www\DatabaseTes­t\Db.php on line 52

 
Reply 2/4/2017 0:49
Avatar
IT Man
Member
Avatar
Reply 2/4/2017 2:19
Don't be silly and smile!
Avatar
Daouda Sanogo
Member
Avatar
Replies to IT Man
Daouda Sanogo:2/4/2017 11:59

Thanks it solved the prob! Now i am running on to another problem, i can not connect to my database.
Briefly, i am not using the default port of localhost which is supposed to be '80', i changed it for particular reason to '90' and phpmyadmin is working fine.
Now when i run this code:
<?php
require_once ('Db.php');
$host = "localhost:90";
$database = "web_database";
$user = "root";
$password = "admin";

$success = Db::connect($host, $database, $user, $password);
if($success){
echo 'Connected!';
}else
echo 'Error occurred';
?>

I got this error message:
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [2002] No connection could be made because the target machine actively refused it. ' in C:\xampp\htdoc­s\DatabaseTes­t\Db.php:54 Stack trace: #0 C:\xampp\htdoc­s\DatabaseTes­t\Db.php(54): PDO->__construct('mys­ql:host=loca.­..', 'root', '', Array) #1 C:\xampp\htdoc­s\DatabaseTes­t\index.php(8): Db::connect('lo­calhost:90', 'web_database', 'root', '') #2 {main} thrown in C:\xampp\htdoc­s\DatabaseTes­t\Db.php on line 54

Could anybody help?

 
Reply 2/4/2017 11:59
Avatar
IT Man
Member
Avatar
Replies to Daouda Sanogo
IT Man:2/5/2017 2:37

Try this:

$host = 'localhost;port=90';
Reply 2/5/2017 2:37
Don't be silly and smile!
Avatar
Tabish Ali
Member
Avatar
Tabish Ali:2/9/2017 12:45

the wrapper you made , is it necessary to understand it for beginners to database?

Reply 2/9/2017 12:45
there is no limit to learn.
Avatar
David Capka
ICT.social team
Avatar
Replies to Tabish Ali
David Capka:2/9/2017 13:20

Absolutely not, you should be only able to use it :)

Reply  +1 2/9/2017 13:20
You can walk through a storm and feel the wind but you know you are not the wind.
To maintain the quality of discussion, we only allow registered members to comment. Sign in. If you're new, Sign up, it's free.

10 messages from 10 displayed.