PHP 0 to hero pt.9 - setting up SQL (SQL pt. 1)
Tags: webdev, beginners, sql, tutorial
Published: 2024-01-18 15:00:00

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

Today, we start very important topic - using databases in PHP

This part is an introduction - there will be 2 other parts about strictly 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
There, my choice is MySQL - we have it installed with XAMPP 
(To be fair, it's MariaDB)

But remember - that's not the only one

We have plenty of those. There are differences in writing SQL code then - also PHP syntax might be different

Some of the most popular are 
- [MySQL](https://www.mysql.com/products/)
- [MariaDB](https://mariadb.org/)
- [PostgreSQL](https://www.postgresql.org/)
- [Microsoft SQL Server](https://www.microsoft.com/en-us/sql-server/sql-server-downloads)
- [Oracle Database](https://www.oracle.com/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 queries

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 this and Apache is working - we can access phpmyadmin

Go to `http://localhost/phpmyadmin`

At first - it looks like this


![First glance](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vnd9uht6trhvztpzab48.png)

(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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8uris67ktmzeat3hyih7.png)

## Creating database

Let's create a database called `firstProject` - choose whichever encoding suites your language from ones starting with `utf8` and ending with `ci`

As I am polish, I've chosen `utf8_polish_ci`

After inputting everything - it looks like this


![Ready to create database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ajfwbr5etr0n8w5s8zww.png)


Now, next page

![Table creation view](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/milpwrtq2es4420ouiv5.png)

## 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

![What should it look like](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4insqc1bn2u3sze6u6ik.png)

Then we end up here

![Table creation page](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/32bqjyfvti6wyn3ei7sf.png)

Let's start with `Name`

After inputting names that we agreed to, we should have something like this
![View right now](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ntc6uhjojrcp779ld2xs.png)

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`


![Named columns with types](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/iz4f4cdux0ks6h110pai.png)


Now, we have this - one last thing

We were talking about primary key - have we specified 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

If you hadn't realized it yet - we have to check it

After all - it looks like this

![Ready table structure](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/eenmgdgtsegyehu7oor1.png)

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


![Table structure](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jf0bycgkzy83vgjt5anb.png)

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

Click `New` in database drop-down menu on navbar

![Where to click](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e968ziuyl41nnbyflhvm.png)

Up here

![place for table name](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h2imbdkah5pdr1g7eehd.png)

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

![Filled table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y8emnma3rohxsfyn5izt.png)

As you see, I allowed myself to skip explaining everything - you should know, how it works and what some of those mean

One 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 coding

Check out other parts of this series - wait for next ones and see you in next articles