Lesson 1 - Introduction to using databases with PHP for total beginners
Welcome to the first lesson of our course on working with MySQL databases in PHP for absolute beginners and without object-oriented programming. MySQL is the most widely used database, it is free and is found on almost every web-hosting service. In a few lessons, we are going to create working examples and finish the course up with a simple blog that will feature an article editor.
Introduction
I'll keep things as simple and straightforward as possible. However, I will assume that you are familiar with the basic PHP syntax at this point. We are going to go over the absolute basics of MySQL databases. Meaning that you will not need any prior knowledge of databases or OOP for that matter.
This course intends to break the ice and teach just about anyone to use databases, even if they're PHP novices.
Why we use databases
As website users, we expect the content to change constantly based on what we click on, e.g. writing an article, writing a comment, adding items to and e-shop, workers to a company, etc. It would be rather annoying if we had to make a new HTML page for each and every article and upload to the site. There wouldn't be a way to search through said articles/items/workers and things would only get more confusing as a number of entries increased. A much better way to do things like this is, you guessed it, databases! We'll program an article editor in PHP, stores articles into a database, and display them for visitors to indulge in.
You may be asking yourself, why databases? You could just store the data in text files using an editor or come up with some other alternative. You'd get it to work somehow...right? Or would you?
Databases don't just store data. They are a very sophisticated and optimized tool, that take care of a lot of issues for us and are very simple to use. We use the SQL language to work with databases, whose syntax is more or less human-readable English sentences. Essentially, and almost literally, we communicate with databases like this: "select the users whose e-mail address is andrew@yahoo.com" or "insert new user, with name John and e-mail address john@yahoo.com". Eventually, will also be able to write queries like the following: "select a user with e-mail andrew@yahoo.com and his articles, ordered by publish date descending". You will be surprised how easy it is to communicate with databases. As I have already said, other than storing data, databases take care of other issues for us. Issues, that would be very hard to deal with otherwise. We'll go into said issues in further detail further on in the course.
A database is technically a black box, that deals with data somehow, so we don't have to deal with it and only focus on using it. Nowadays, people usually don't work with data any other way.
Relational database
MySQL is what is known as a relational database. This term is used for databases based on tables. Each table contains items of a single type. So we can have a table named user, another named article, comment, so on and so forth. Tables are usually named in singular form, but naming them in plural form is not to be discouraged.
One could magine a database table like a table in Excel. The users table could look something like this:
First name | Last name | Date of birth | Articles count |
---|---|---|---|
John | Smith | 03/11/1984 | 17 |
Thomas | Moore | 01/01/1989 | 6 |
Jack | Newman | 12/20/1972 | 9 |
Jane | Wilson | 08/14/1990 | 1 |
We'd save items (in this case users) as separate rows. columns would then specify the attributes (properties, if you will) that belong to each user. MySQL databases are strongly typed, meaning that each column has a specific data type (number, character, short text, long text...) and can contain only values of this type. To keep things nice and neat, relative databases allow use to add unique identifier to our table rows. This way we could sort users any which way we wanted to using artificial identifiers, which we will get to later on.
As you may have already guessed, tables in relational databases have certain internal relationship. However, this too, is a topic for another lesson.
Preparing tools
Let's go ahead and get started. We will need a configured Apache webserver and MySQL database. I assume that you have alreadt tested out PHP and have your environment set up. If not, see the installing PHP and MySQL tutorial
phpMyAdmin
phpMyAdmin is the most widely used MySQL database administration tool. MySQL can be found along with phpMyAdmin on every web hosting service. Even if you use other databases or database administration tools, it is very likely that it is based on this technology. You can get to phpMyAdmin through the administration interface on your web hosting service. On localhost, all you'll have to do is type in the following address:
localhost/phpmyadmin/
Then, you'll be able to see the phpMyAdmin web interface, which looks like this:
Creating the database
Let's create the new database, one that we are going to use in our course. Usually, a single database is enough for an entire project (site).
This step is almost for sure already done for you by your web hosting service. In other words, you will have instant access to a database, which has the same name as your account, e.g. mywebsitecom1. On localhost, we'll have to create one ourselves.
Click the Databases tab on the top of phpMyAdmin. Fill in the database name, e.g. web_database. I highly recommend naming databases using only lowercase letters and underscores. I suppose you understand why using accent characters isn't a good idea. Linux is to blame for the lowercase/uppercase part since its file system is case-sensitive and most servers run it. We will set the collation to utf8_general_ci, so that accent characters and languages like Chinese and Russian are displayed properly. Once you've done all of that, submit it.
phpMyAdmin will then generate and send an SQL query based on what we have entered. The resulting query looks something like this.
CREATE DATABASE web_database CHARACTER SET utf8 COLLATE utf8_general_ci;
Don't worry, you won't have to remember any of that. We are going to be using databases and tables exclusively through phpMyAdmin. We now have our database ready for use. We will leave creating tables for next lesson, First database table and MySQL PHP drivers.