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
- Learn something about PHPMyAdmin and MySQL
- Create our first database
- Familiarize ourselves with terms like primary key, row, column, SQL, DBMS
- 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
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
(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
- 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
Now, next page
Database designing
We will create simple to-do app - so we need 2 tables
-
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
-
-
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 beuser_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 - 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
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
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
Top comments (0)