Learning PHP - Part 6 - MySQL? Or Your SQL?

Part 6

Laracasts main site
Laracasts - PHP for beginners

Chapters covered

Chapter 11 - Databases 101

Chapter 11 - MySQL 101

Now its time to switch gears. Lets learn about MySQL. Its a relational database, or SQL-based database, which essentially means everythings related, as implied by the name via various ‘id’ columns. SQL stands for ‘Structured Query Language’. It’s simply a standardized way of accessing data in a database.

This is in contrast to a NoSQL-based database. The most obvious NoSQL-based database is MongoDB. NoSQL essentially follows a way of storing data nonrelationally. I won’t get into the advantages and disadvantages of both, just know that both exist.

Installing MySQL - Ubuntu 18.04

Feel free to check out MySQL downloads page for your specific needs.

For me I used the following:

Bash 
sudo apt update
sudo apt install -y mysql-server # installs mysql
sudo mysql_secure_installation # will provide various prompts


Ensure its working:

Bash 
mysql # enters a prompt
  -> exit
sudo service mysql status
sudo service mysql stop
sudo service mysql start
sudo service mysql status


If you are having permission issues: Check this out

There are additional configs to add users, but thats a little too advanced for what were doing here.

Using MySQL

Creating your first database:

Bash 
mysql
 -> show databases;
# Lists databases
 -> create database todo;
# Query OK, 1 row affected
 -> show databases; # should show mytodo
 -> use mytodo; # This tells mysql what database to switch into and use
 -> show tables; # should be empty
 -> CREATE TABLE todos (description TEXT, completed BOOLEAN);
# Query OK, 0 rows affected
 -> show tables; # Should show your new todos table
 -> describe todos; # Shows your fields and types
 -> drop tables; # Removes your todos tables
 -> CREATE TABLE todos (id INTEGER PRIMARY KEY AUTO_INCREMENT, description TEXT NOT NULL, completed BOOLEAN NOT NULL);
 -> show tables;
 -> describe todos;


I’m not going to get too in depth with the above commands, but I will touch on the following query.

Here’s the command:

CREATE TABLE todos (id integer PRIMARY KEY AUTO_INCREMENT, description text NOT NULL, completed boolean NOT NULL);

Here’s what it’s doing:

CREATE TABLE todos(); Create the table called ‘todos’
id integer PRIMARY KEY AUTO_INCREMENT: The first field will be the id field, this field will accept an INTEGER. This field will also be the PRIMARY KEY. This means that the id will be the unique identifier for whatever data is stored. AUTO_INCREMENT means the user does not have to supply the id integer. Instead, the database will automatically set the id starting from 1.

Manipulating a tables data

Bash 
mysql
-> INSERT INTO todos (description, completed) VALUES('Go to the store', false);
# Will auto-add the id, will add the description 'Go to store', and the boolean false

-> SELECT * FROM todos;
# Will show all columns in the todos table

Summarizing

Installing a MySQL GUI - MySQL-Workbench

mysql Start mysql

-> create database \<database\>; Create the database with <database>.

-> use database <\database\>; Set the current database to query to <database>.

-> CREATE TABLE \<table\>(<\fields\>); Create a table within the database with a name of <table> and has the following <fields>.

-> show tables; List the tables in your database.

-> describe <\table\>; Shows the fields of the given <table>.

Simple manipulation:

-> INSERT INTO <\table\>(<\field1\>, \<field2\>) VALUES(<\value1\>, <\value2\>); Insert the following values into the specified <field> for the given table name. Order is important.

-> SELECT * FROM \<table\>;

Allows you to view all columns and their data from the given <table>.

-> SELECT * FROM \<table\>WHERE id = 1;

Select all columns from <table> but only where the id is equal to 1.

Now using the command line for everything is not very fun. You can simply google:

‘mysql database GUI tools’

I decided to use the officially supported mysql-workbench.

Bash 
sudo apt install mysql-workbench
mysql-workbench

Easy, simple, I like it.

Issues

Running mysql without sudo

Note: I ran into the issue of having to run mysql without sudo. I found this guide: DigitalOcean Guide to MySQL .

Bash 
sudo mysql -> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password
BY 'password'; -> FLUSH PRIVILEGES;

Yes, you can create a new user and then provide privileges that way. Again, beyond the scope of this, and this wont be used in production either.

Running mysql-workbench without sudo

I also had an issue running mysql-workbench without sudo. I solved this by chowning ~/.mysql

Bash 
sudo chown $USER:$USER ~/.mysql
mysql-workbench


I’ll let you figure out other configurations with mysql-workbench. Play around with it. Don’t rush, find other commands. Have fun! Ill see you in the next part about classes.

My repo
Laracasts main site
PHP for beginners
MySQL downloads page
DigitalOcean Guide to MySQL .
MySQL-Workbench

Previous Page PHP for beginners - Part 7 - Classes?...Like school? Next Page Learning PHP - Part 5 - Lets make it functional