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

# The controller
We’ll use the same file that we used for the “Add a project” feature to implement our controller code:  `/controllers/project.php`.

First, we’ll initialize variables to an empty string:
 
```
$project_title = $category = '';
``` 
This line of code is added just after the `require` statements.

We know that when the user clicks a list item from the project list, the item `id` is passed on to the URL. Therefore, we can capture this `id` with the `$_GET` variable in order to select the given project from the database.

```
if (isset($_GET['id'])) {
    list($id, $project_title, $category) = get_project($_GET['id']);
}
```
The `get_project($id)` function does not exist yet. We’ll create it in the model.

>**Note:** `list()` is a built-in PHP function, which helps us assign a list of variables in one operation.
>```
><?php
>$projectInfo = array(1, 'Learn PHP', 'Personal');
>// We can pick only some variables
>list(, $title, $category) = $projectInfo;
>?>
>```

The last thing to do is to pass the `$id` as the third parameter to the `add_project()` function when the user submits the form.

```
$id = null;

if (isset($_POST['id'])) {
        $id = $_POST['id'];
    }
```
Now we can call the function like this:
```
if (add_project($title, $category, $id)) {
                header('Refresh:4; url=project_list.php');
                if (!empty($id)) {
                    $confirm_message = escape($title) . ' updated successfully';
                } else {
                    $confirm_message = escape($title) . ' added successfully';
                }
            }
```

# The model
Now, in `model.php`, let's create the function to select one project:

```
function get_project($id)
{
    try {
        global $connection;

        $sql =  'SELECT * FROM projects WHERE id = ?';
        $project = $connection->prepare($sql);
        $project->bindValue(1, $id, PDO::PARAM_INT);
        $project->execute();

        return $project->fetch();
    } catch (PDOException $exception) {
        echo $sql . "<br>" . $exception->getMessage();
        exit;
    }
}
```
We can now add `$id` as the last parameter of the `add_project()` function. Inside the function, we can add the `$sql` function to update data:

```
if ($id) {
            $sql = 'UPDATE projects SET title = ?, category = ? WHERE id = ?';
        } else {
            $sql =  'INSERT INTO projects(title, category) VALUES(?, ?)';
        }

      // code here does not change
        if ($id) {
            $new_project = array($title, $category, $id);
        }
// code here does not change
```
That’s all. Let’s see it live and confirm whether it works.
![update](/api/collection/4668316850323456/4792272899014656/page/5926847364726784/image/6145268905934848)

# Our accomplishment
So far, we’ve learned step by step how to update the project records. We can find the source code [here](https://github.com/Bam92/crud-php-mysql/tree/update).

# The controller
We’ll use the same file that we used for the “Add a project” feature to implement our controller code:  `/controllers/project.php`.

First, we’ll initialize variables to an empty string:
 
```
$project_title = $category = '';
``` 
This line of code is added just after the `require` statements.

We know that when the user clicks a list item from the project list, the item `id` is passed on to the URL. Therefore, we can capture this `id` with the `＄_GET` variable in order to select the given project from the database.

```
if (isset($_GET['id'])) {
    list($id, $project_title, $category) = get_project($_GET['id']);
}
```
The `get_project(＄id)` function does not exist yet. We’ll create it in the model.

>**Note:** `list()` is a built-in PHP function, which helps us assign a list of variables in one operation.
>```
><?php
>$projectInfo = array(1, 'Learn PHP', 'Personal');
>// We can pick only some variables
>list(, $title, $category) = $projectInfo;
>?>
>```

The last thing to do is to pass the `＄id` as the third parameter to the `add_project()` function when the user submits the form.

```
$id = null;

if (isset($_POST['id'])) {
        $id = $_POST['id'];
    }
```
Now we can call the function like this:
```
if (add_project($title, $category, $id)) {
                header('Refresh:4; url=project_list.php');
                if (!empty($id)) {
                    $confirm_message = escape($title) . ' updated successfully';
                } else {
                    $confirm_message = escape($title) . ' added successfully';
                }
            }
```

# The model
Now, in `model.php`, let's create the function to select one project:

```
function get_project($id)
{
    try {
        global $connection;

        $sql =  'SELECT * FROM projects WHERE id = ?';
        $project = $connection->prepare($sql);
        $project->bindValue(1, $id, PDO::PARAM_INT);
        $project->execute();

        return $project->fetch();
    } catch (PDOException $exception) {
        echo $sql . "<br>" . $exception->getMessage();
        exit;
    }
}
```
We can now add `＄id` as the last parameter of the `add_project()` function. Inside the function, we can add the `＄sql` function to update data:

```
if ($id) {
            $sql = 'UPDATE projects SET title = ?, category = ? WHERE id = ?';
        } else {
            $sql =  'INSERT INTO projects(title, category) VALUES(?, ?)';
        }

      // code here does not change
        if ($id) {
            $new_project = array($title, $category, $id);
        }
// code here does not change
```
That’s all. Let’s see it live and confirm whether it works.
![update](/api/collection/4668316850323456/4792272899014656/page/5926847364726784/image/6145268905934848)

# Our accomplishment
So far, we’ve learned step by step how to update the project records. We can find the source code [here](https://github.com/Bam92/crud-php-mysql/tree/update).

Learn how to modify a database record in PHP.

Introduction

App Initialization

Create and Read Records

Refactor the Application

PHP Data Validation

Generate Reports

Update and Delete Records

PHP Router

Conclusion

Appendix

More on Update Records

The controller