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

In this lesson, we’ll focus on filtering data by project. This is the workflow that we’ll follow to filter the data project-wise:
1. Create a basic form in `views/reports.php`.
2. Edit the controller and the model for the “Filter” feature.
3. Run the application.


# Basic implementation

Let’s create a form in our view with a list of all the projects: 
```html
<p>Filter by</p>
<form method="get">
     <select name="filter" id="project">
           <option value="">Select one</option>               
            <?php foreach ($projects as $project) : ?>
            <option value="<?php echo 'project:' . $project["id"] ?>"><?php echo escape($project["title"]) ?>
             </option>

             <?php endforeach; ?>             
</select>   
<input type="submit" value="Run">
      
</form>
```
We’ll create the projects variable that we use in the `$projects = get_all_projects();` view inside the conroller. We will also create the `$filter = 'all'` variable, which we’ll use as a parameter for the `get_all_tasks()` function. 

Now when the user submits the form, the data will be stored in `$_GET['filter']`. Let's add this in the `reports.php` controller:

```php
if (!empty($_GET['filter'])) {
    $filter = explode(":", $_GET['filter']); // convert input into an array
}

$tasks = get_all_tasks($filter);
```

To finish, we’ll edit the `get_all_tasks()` function in our model.

First, we’ll accept an optional parameter, `get_all_tasks($filter = null)`. Next, we’ll simplify the `$sql` variable like so:

 ```php
$sql =  'SELECT t.*, p.title project 
        FROM tasks t
        INNER JOIN projects p 
        ON t.project_id = p.id';
```

We can now add two new variables with their default values
```
$where = '';
$orderBy = ' ORDER BY t.date_task DESC';
```
and prepare the query like so: `$tasks = $connection->prepare($sql . $where . $orderBy);`.

If we call the `get_all_tasks()` function, we’ll get all the tasks that were ordered by `t.date_task DESC`. This is what we want in our `task_list.php` file.

If `$filter` is set and is not null, we’ll know that the call is from `reports.php`. Let’s check if the parameter is an array:

```
 if (is_array($filter)) {
       if ($filter[0] == 'project') {
            $where = ' WHERE p.id = ?';
        } 
}
``` 
If `$filter` is an array, we’ll check whether the first element is `project`. From there, we’ll know that the user wants to filter the data by project. Hence, before we execute the query, we’ll need to bind the value for the placeholder in `$where`:

```
 if (is_array($filter)) {
      $tasks->bindValue(1, $filter[1], PDO::PARAM_INT);            
}
```

# Running the application
We were able to implement everything that we needed for our feature to work. Now it’s time for us to make sure that it works as expected. In the code widget given below, click the “Run” button and enjoy the application view.


In this lesson, we’ll focus on filtering data by project. This is the workflow that we’ll follow to filter the data project-wise:
1. Create a basic form in `views/reports.php`.
2. Edit the controller and the model for the “Filter” feature.
3. Run the application.


# Basic implementation

Let’s create a form in our view with a list of all the projects: 
```html
<p>Filter by</p>
<form method="get">
     <select name="filter" id="project">
           <option value="">Select one</option>               
            <?php foreach ($projects as $project) : ?>
            <option value="<?php echo 'project:' . $project["id"] ?>"><?php echo escape($project["title"]) ?>
             </option>

             <?php endforeach; ?>             
</select>   
<input type="submit" value="Run">
      
</form>
```
We’ll create the projects variable that we use in the `＄projects = get_all_projects();` view inside the conroller. We will also create the `＄filter = 'all'` variable, which we’ll use as a parameter for the `get_all_tasks()` function. 

Now when the user submits the form, the data will be stored in `＄_GET['filter']`. Let's add this in the `reports.php` controller:

```php
if (!empty($_GET['filter'])) {
    $filter = explode(":", $_GET['filter']); // convert input into an array
}

$tasks = get_all_tasks($filter);
```

To finish, we’ll edit the `get_all_tasks()` function in our model.

First, we’ll accept an optional parameter, `get_all_tasks(＄filter = null)`. Next, we’ll simplify the `＄sql` variable like so:

 ```php
$sql =  'SELECT t.*, p.title project 
        FROM tasks t
        INNER JOIN projects p 
        ON t.project_id = p.id';
```

We can now add two new variables with their default values
```
$where = '';
$orderBy = ' ORDER BY t.date_task DESC';
```
and prepare the query like so: `＄tasks = ＄connection->prepare(＄sql . ＄where . ＄orderBy);`.

If we call the `get_all_tasks()` function, we’ll get all the tasks that were ordered by `t.date_task DESC`. This is what we want in our `task_list.php` file.

If `＄filter` is set and is not null, we’ll know that the call is from `reports.php`. Let’s check if the parameter is an array:

```
 if (is_array($filter)) {
       if ($filter[0] == 'project') {
            $where = ' WHERE p.id = ?';
        } 
}
``` 
If `＄filter` is an array, we’ll check whether the first element is `project`. From there, we’ll know that the user wants to filter the data by project. Hence, before we execute the query, we’ll need to bind the value for the placeholder in `＄where`:

```
 if (is_array($filter)) {
      $tasks->bindValue(1, $filter[1], PDO::PARAM_INT);            
}
```

# Running the application
We were able to implement everything that we needed for our feature to work. Now it’s time for us to make sure that it works as expected. In the code widget given below, click the “Run” button and enjoy the application view.


Learn how to handle a form with  the GET method.

Introduction

App Initialization

Create and Read Records

Refactor the Application

PHP Data Validation

Generate Reports

Update and Delete Records

PHP Router

Conclusion

Appendix

Filter by Project