So far, you have used MySQL to store a simple user table with a few columns. This was good for some quick examples, but what about more complex queries that have multiple dependencies?
In this chapter, let’s chart out some data that you can use for a camp registration/management database.
This chapter consists of the following sections.
Planning for a New Database
Databases work best when they are well organized with data and tables taken into consideration. Here are a few things to consider when planning your database:
1)
Always use the proper datatype.
One of the main MySQL best practices is to utilize datatypes dependent on the idea or inherent nature of the information. Utilizing unessential datatypes may eat up more space or lead to mistakes.
For instance, using
VARCHAR (20) rather than a DATETIME datatype for storing date-time values will prompt mistakes in date-time–related computations. Additionally, it is conceivable that invalid information will be thrown into the mix, ultimately causing mistakes.
2)
Use CHAR (1) over VARCHAR (1).
VARCHAR (1) takes additional bytes to
store data, so assuming your string is a single character, it is better to utilize
CHAR (1).
3)
Utilize the CHAR datatype to store just fixed length information.
For instance, if the length of the
information is under 1,000, utilizing single (1000) rather than VARCHAR (1000) will devour more space.
4)
Try not to use provincial date designs.
When
using DATETIME or DATE datatypes, consistently use the YYYY-MM-DD date arrangement or ISO date design reasonable for your SQL Engine. Territorial organizations like DD-MM-YYYY or MM-DD-YYYY will not be stored properly and will result in errors and frustration.
It is beneficial that the inquiry returns the outcome quickly, so record the sections that are utilized in JOIN conditions.
On the off chance that you use the UPDATE proclamation including more than one table, file every one of the sections that are utilized to join the tables.
6)
Do not use functions over indexed columns.
This is the purpose of an index. By trying to replicate the indexing process through the use of functions, you are overcomplicating the situation and therefore slowing the whole process down.
For instance, say you need to get information where the initial two characters of the camper are GE. You use the following:
SELECT firstname FROM campers WHERE firstname like 'GE%'
Furthermore, don’t write
SELECT firstname FROM campers WHERE left (firstname,2)='GE'
The first example
makes use of the index, which results in a faster response time.
7)
Use ORDER BY clauses only if needed.
Let the PHP
order your data, not MySQL. With MySQL, you can set an order for the data to be returned by, like ASC for ascending or DESC for descending. This can result in your queries taking additional time that PHP or even JavaScript on your front end can do.
8)
Choose a proper database engine.
If you develop an application that reads data more often than writes it (e.g., a search engine), choose a MyISAM storage engine.
Choosing the wrong storage engine will affect the performance. The storage engines available to you are MyISAM, which is the default MySQL storage engine, or InnoDB, which is an alternative engine built into MySQL and intended for high-performance databases. One of the main differences between these two is
table locking vs. row-level locking.
Table locking is the technique of locking an entire table when one or more cells within the table need to be updated or deleted. Table locking is the default method employed by the default storage engine,
MyISAM. Row-level locking is the act of locking an effective range of rows in a table while one or more cells within the range are modified or deleted.
Row-level locking is the method used by the InnoDB storage engine and is intended for high-performance databases.
9)
Use the EXISTS clause wherever needed.
When you need to only
check if the data exists, use the MySQL EXISTS function instead of initiating an entire query in order to evaluate the return data. For example, use
If EXISTS(SELECT * from Table WHERE col='foo')
Do not use
If (SELECT count(*) from Table WHERE col='foo')>0
10)
EXPLAIN your SELECT queries.
MySQL comes with the ability to
EXPLAIN a query in terms of how MySQL executes the process, such as
mysql> EXPLAIN ANALYZE SELECT * FROM SALES;
+----------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------+
| –> Table scan on SALES (cost=0.35 rows=1) (actual time=0.070..0.070 rows=0 loops=1) |
+----------------------------------------------------+
1 row in set (4.15 sec)
Creation of a New Database
With all you’ve learned so far, let’s create your new
database with the following structure:
Table Name: Campers
Columns: ID, First Name, Last Name, Age, Camp ID, Created
Table Name: Camps
Columns: ID, Name, Size, Created
Table Name: Registered
Columns: ID, Camper ID, Camp ID, Registered, Paid, Created
This is a
basic design for a database that a camp would use in order to keep track of their campers, camp sites, and registrations. In order to use this properly for your project, you need to set up and create these tables, seed them with data, and use PHP to manage them through table relationships. While there are several
front-end GUI methods of managing
MySQL, you will not be using any of them for now. We will take you through these next steps via the command-line interface (or CLI)
of MySQL. First, get to a command prompt (Windows) or terminal (Mac OS, Linux) and run
Remember this command? This shows all the running containers. If nothing shows up, you may not be running Docker for this book. Please go back to the first chapter of this book and make sure you have Docker running and docker-compose up has been run.
If everything is running properly, you should see something similar to this:
docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
d5d98b7de503 beginning-php8-and-mysql_app "docker-php-entrypoi…" 2 days ago Up 2 days 9000/tcp php-app
63715c3c4f52 nginx:alpine "/docker-entrypoint.…" 2 days ago Up 2 days 0.0.0.0:80->80/tcp, :::80->80/tcp php-nginx
21f2a4b87b7b mysql:8.0 "docker-entrypoint.s…" 2 days ago Up 2 days 0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp mysql-db
You can see that your MySQL container is named
mysql-db and with Docker you can now connect to that container just like you would to a server.
<code>
docker exec -ti mysql-db bash
</code>
This command tells
Docker that you would like to execute a command named
-ti to create a pseudo TTYl. This basically allows you to use your terminal to be the interface to this container while the
i stands for interactive mode, meaning you want to use this container like a live system. The next attribute is
mysql-db, which is the name of the container you are connecting to, and finally you want to run bash. Bash is a shell for Unix/Linux systems that allows you access to the filesystem and to run scripts. Once you press Enter, you will be “inside” the My SQL
container. Once connected, you need to run the following:
<code>
mysql -uroot -ppass
</code>
This connects you to MySQL using the username root and password pass. This is typically not encouraged, but in closed networking situations and development environments you can allow for such casualness.
The first thing you need to know and do once connected to MySQL is list the databases.
<code>
show databases;
</code>
Note that all MySQL commands end with a semicolon. If you type a command and press Enter without the semicolon, it will just move down to the next line and wait for you to type more or to type a semicolon. You can just type a semicolon and then Enter to continue with your command.
In this list, you should see
beginningPHP. Type
<code>
use beginningPHP;
</code>
Now type
<code>
show tables;
</code>
This command, well, shows the tables available in the current database you are using. There should be a users table. This is fine and you will just set it aside for now. Let's begin creating the tables for your camping data.
In the
chapter5 directory there is a file called
campers.sql.
<code>
create table IF NOT EXISTS campers(
id INT NOT NULL AUTO_INCREMENT,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(40) NOT NULL,
age INT NOT NULL,
campId INT default 0,
created DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP default current_timestamp,
PRIMARY KEY ( id )
);
</code>
Take this code and paste it into the MySQL command line and press Enter. Now type
show tables; again.
<code>
mysql> show tables;
+------------------------+
| Tables_in_beginningPHP |
+------------------------+
| campers |
| users |
+------------------------+
2 rows in set (0.00 sec)
You now have a
campers table in the database. To see the structure of a table, type
<code>
desc campers;
</code>
Desc is for Describe and it will show you the layout for the table. Now let’s create the table to hold the information for your camps. Look inside
camps.sql in the
chapter5 directory.
<code>
create table IF NOT EXISTS camps(
id INT NOT NULL AUTO_INCREMENT,
camp_name VARCHAR(100) NOT NULL,
size INT NOT NULL,
created DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP default current_timestamp,
PRIMARY KEY ( id )
);
</code>
Copy and paste this code into the MySQL command line and press Enter.
Now type
show tables; and see the results:
<code>
show tables;
+------------------------+
| Tables_in_beginningPHP |
+------------------------+
| campers |
| camps |
+------------------------+
2 rows in set (0.03 sec)
</code>
Lastly, let’s create a table for the registered campers. Repeat the steps from above with registered.sql.
Open registered.sql. Copy and paste the code into the MySQL command line and press Enter.
Type show tables; and see the results.
Now that you have your data, let’s see next how you can use relational queries to create simple and complex queries for your app.
Summary
In this chapter, you learned which data types you can utilize in a MySQL DB table, like CHAR or VARCHAR, and how to define multiple dependencies in queries.
In the next chapter, you will combine everything you have learned into one example website to create, read, update, and delete data (otherwise known as CRUD). You will learn how a basic CRUD website can be a standard way to manage information within a business or organization.
