SQL pt.1 - PHP 0 to hero pt.9

What’s up - welcome in another part of this course

Today we have a very important topic - using databases in PHP

This part is an introduction - there will be 2 other parts, strictly about coding So, what do we do today?

We will

1. Learn something about PHPMyAdmin and MySQL
2. Create our first database
3. Familiarize ourselves with terms like primary key, row, column, SQL, DBMS
4. Data types in SQL-based databases

So, let’s get started

What will we use today

This time, we will utilize another software - DBMS

What is it? It stands for Database Management System Here, my choice is MySQL - we have it installed with XAMPP (To be fair, it’s MariaDB)

But remember - MariaDB is not the only one, we have plenty of those. There are differences in writing SQL code then - also designated PHP syntax might be different

Some of the most popular are

• MySQL
• MariaDB
• PostgreSQL
• Microsoft SQL Server
• Oracle Database

Another thing is software - We have utility built in XAMPP (and installed in linux) called phpmyadmin

This allows us to work with database graphically, rather than via text queries only

And, what is a query? That’s a structure, that tells database to perform some action - like select some data or update them

We write them in language called SQL - structured query language

Let’s see what it really looks like

Launching PHPMyAdmin

First thing - enable MySQL service in XAMPP (Or start mysql-server and mysql-client in linux)

Then, when it + Apache are working - we can access phpmyadmin

Go to http://localhost/phpmyadmin

At first - it looks like this

(Here I’ve got some databases from my previous projects - you should have it empty)

We’ll familiarize ourselves with some of these - right now click new inside sidenav - you should be redirected to page like this

Creating database

Let’s create a database called firstProject - the most popular one, as it contains most of diffrent letters from multiple languages, is UTF-8

And that’s what we’ll be using. After inputting everything - it looks like this

Now, next page

Database designing

We will create simple to-do app - so we need 2 tables

1. users - It will have 3 rows (right now)
   • user_id - Numerical user id (It will be our primary key)
   • username - self-explanatory
   • password - user’s password - I think also self explanatory
2. tasks - All tasks will be stored here. It will consist of 5 rows
   • task_id - Primary key for this table
   • task_title - self-explanatory
   • description - description for user about specific task
   • creator - Task owner. It will be user_id of whoever created this task
   • timestamp - when was task created

What’s a primary key?

Primary key - It’s a column in the table (most of the time numerical), that’s unique to all records (rows) - like here id. It is used to distinguish rows in table

Creating tables

First, let’s create users

Add name and number of columns - here 3

Then we end up here

Let’s start with Name

After inputting names that we agreed to, we should have something like this

Next part - type

This indicates what type of data will be stored there

We have a bunch of them, like DECIMAL, BOOLEAN

So, what types will we have? user_id - will be INT username - will be TEXT password - as well TEXT

Now, we have this - one last thing

We were talking about primary key - but did we specify it? No

So, let’s do it

There is a column called index - in row with user_id click on it, and choose PRIMARY

On pop-up - just click go and don’t bother

Right beside it - we have suspicious checkbox labeled as A_I

Is it to fill it with data generated by AI? Not really

A_I stands for AUTO INCREMENT - we know the verb “to increment”

So, this option means that when we pass an empty value here (NULL) it will automatically replace it with incremented value of previous row

So, if we added a new row here it would automatically set user_id value to 1

If we added next row, it would fill it with 2. And so on

Let’s check it

After all - it looks like this

Click Save button below and boom - we should be redirected to page called structure

Looks like it’s fine - let’s create the second one

Click New in database drop-down menu on navbar

Up here

Add table name. We have only 4 colums, but we need 5

Click Go button beside table name - it will add a new column

Now - fill it

One more thing I want to tell you about is DATETIME type

It allows us to store dates in standardized MySQL format - YYYY-MM-DD hh:mm:ss

Here, those letters stand for one digit of Y - year M - month D - day h - hour m - minute s - second

Click Save and job is done

We have successfully created a database in PHPMyAdmin

Conclusion

As you see, today we have written no code - but don’t worry. Next parts will have more of that

Check out other parts of this series - see you in next articles