Gain insights into building a modern, production-ready PHP app. Delve into CRUD operations, I/O security, routing, and more. Discover how to effectively use PHP and MySQL for database applications.

Build a CRUD Database App with PHP & MySQL-01.png

lamp.tar.gz

lamp

lamp-copy

lamp-new

Learning a programming language is one thing and using it to build something great is another. I've been teaching code newbies and aspiring developers for a long time and I see many of them struggling to put into practice their newly acquired skills. I've also noticed that many resources on the Internet are out-of-date, misleading, or don't provide real hands-on practice.

I've built this course to fix the issues above. You'll learn a better way to build a modern and production-ready PHP app connected to a database like a pro. The app, a CRUD one, will also have some more advanced features like I/O security, router, and more. 

At the end of this course, you'll be well equipped to build database apps with PHP.

Hands-on PHP & MySQL: Build a CRUD Application

We’ll create three `.sql` files in the `data` directory: `database.sql`, `structure.sql`, and `content.sql`.

Let's get started. 
  
# Database schema
The main purpose of the intended application is to track the time we spend on a given project. Each project consists of a number of tasks. All the projects are stored in a relational database called `tracker`. The relational database management system that is used in this context is MySQL.

In the root directory of our project, we’ll create a new folder called `data/`:


```
mkdir data & cd data
``` 

Then, we’ll create the following .sql files in our new folder:

database.sql

```
touch database.sql
``` 
This file will be in charge of creating our database and selecting it for use. We’ll add the following SQL code within it:

```sql
CREATE DATABASE IF NOT EXISTS tracker CHARACTER SET UTF8 COLLATE UTF8_UNICODE_CI;
USE tracker; 
```
The name of the database we’ll create is `tracker`.

**structure.sql**
```
touch structure.sql
``` 


We can now create tables for our database:
```sql
DROP TABLE IF EXISTS tasks, projects;

CREATE TABLE projects (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    category VARCHAR(100) NOT NULL
);

CREATE TABLE tasks (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    date_task DATE NOT NULL,
    time_task INT(3) NOT NULL,
    project_id INT(11) NOT NULL,
    CONSTRAINT fk_tas_pro 
        FOREIGN KEY (project_id) 
        REFERENCES projects(id)
        ON DELETE CASCADE
)
```
> **Note:** The script shown above is in charge of creating two tables —the `tasks` and the `projects` table— if they don’t already exist in the database. Each table has properties, like its `id` and `title`. The `tasks` table depends on the `projects` table and we'll use the `foreign key` and the `references` constraint to link them together. 

**content.sql**
```
touch content.sql
``` 
We can now populate our tables with some initial content:

```sql
INSERT INTO projects
VALUES
    (1, 'Learn Laravel', 'Personal'),
    (2, 'Learn Reactjs', 'Personal'),
    (3, 'Project 3', 'Business');

INSERT INTO tasks
VALUES
    (1, 'Task 1', '2020-08-01', 25, 1),
    (2, 'Task 2', '2020-09-15', 45, 2),
    (3, 'Task 3', '2020-09-09', 25, 3);  
```

Our file structure will look like this:

Create the SQL files that we'll use to initialize our application.

Introduction

App Initialization

Create and Read Records

Refactor the Application

PHP Data Validation

Generate Reports

Update and Delete Records

PHP Router

Conclusion

Appendix

Database Definition

Database schema