Black Friday Black Friday
Black Friday ultimate sale! Get up to 80 % extra free points! More information here

Lesson 1 - Introduction to using databases with PHP for total beginners

PHP Databases for beginners 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/itemĀ­s/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:

phpMyAdmin

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.

Creating MySQL database

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.


 

 

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

 

 

Comments

Avatar
Tabish Ali
Member
Avatar
Tabish Ali:12/8/2016 12:35

thank u . i find your web very informative (v)

Reply 12/8/2016 12:35
there is no limit to learn.
Avatar
David Capka
ICT.social team
Avatar
Replies to Tabish Ali
David Capka:12/9/2016 6:12

We're glad you like it! :) If you need our help with anything, just post to forums at any time ^^

Reply 12/9/2016 6:12
You can walk through a storm and feel the wind but you know you are not the wind.
Avatar
Falakniaz
Member
Avatar
Falakniaz:1/17/2017 23:45

i need a help to make ERD and DFD diagrams for this (Teacher profile,student profile,course contents,time tables,assignment submission,internal results,attendence)

 
Reply 1/17/2017 23:45
Avatar
David Capka
ICT.social team
Avatar
Replies to Falakniaz
David Capka:1/18/2017 12:56

You can use https://www.draw.io/, it's a free tool for making UML diagrams.

Reply 1/18/2017 12:56
You can walk through a storm and feel the wind but you know you are not the wind.
Avatar
paulitoyato007:2/25/2017 4:26

Dave starting my phpmyadmin how do i go about it

 
Reply 2/25/2017 4:26
Avatar
David Capka
ICT.social team
Avatar
Replies to paulitoyato007
David Capka:2/25/2017 7:24

You just type http://localhost/phpmyadmin to your web browser

Reply 2/25/2017 7:24
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.

6 messages from 6 displayed.