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 form
At the root of our project, we’ll add the `project.php` file.

First, we’ll create a form like this:


```html
<form method="post">
   <label for="title">Title</label>
   <input type="text" placeholder="New project" name="title" id="title">
   <label for="category">Category</label>
   <select name="category" id="category">
      <option value="">Select a category</option>
      <option value="Professional">Professional</option>
       <option value="Personal">Personal</option>
       <option value="Charity">Charity</option>
</select>
<input type="submit" name="submit" value="Add">
</form>
```
We need a little `CSS`, so we’ve created a `style.css` file in the root as well:

```css
label {
  display: block;
}
```

# Form handling
Let’s treat our form.We’ll use the `$_POST` method to get data for when the user submits the form. We’ll work with the following input: `<input type="text" name="title"> will translate to $_POST['title']`.

The first thing we’ll do is to check whether the form was submitted:


```php
if (isset($_POST['submit'])) {}
```
If the form was submitted, we can continue to work with the database connection in the `try/catch` block because we are already accustomed to it.

Then, we’ll place the submitted form values into variables for later use:


```php
if (isset($_POST['submit'])) {
try {
// db connection here
$title = trim($_POST['title']);
$category = $_POST['category'];
} catch {/*catch code here...*/}

} 
```
We’ll use the `trim()` function to delete white spaces.

> **Note**:<br>
> You may have noticed that the data sanitization process was not carried out. This is because weplan to work with [prepared statements](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php). However, it is recommeneded that developers [sanitize](https://www.php.net/manual/en/filter.filters.sanitize.php) as much as possible, especially when writing:
> ```php
> $title = trim($_POST['title'],  FILTER_SANITIZE_STRING);
>```
 
Now comes the SQL code, followed by the prepared statement and the value it is preparing (`$sql`):

```php
$sql =  'INSERT INTO projects(title, category) VALUES(?, ?)';
 $statement = $connection->prepare($sql);
```

The `?` value is a placeholder that will be replaced by an exact value when the code is executed. We can also use named placeholders instead, like this:

```php
$sql =  'INSERT INTO projects(title, category) VALUES(:title, :category)';
```
Before we execute the statement, we need to check that the user did not send any empty values:

```php
if (empty($title) || empty($category)) {
$error_message = "Title or category empty";}
```
After verifying that there are no empty values, we’ll  execute the statement with an array of all the submitted values:

```php
$new_project = array($title, $category);
$statement->execute($new_project);
```
If we use a named placeholder, we can create our array like this:

```php
$new_project = array(
  'title' => $title, 
  'category' => $category
);
```
Here is the full PHP code:

# The form
At the root of our project, we’ll add the `project.php` file.

First, we’ll create a form like this:


```html
<form method="post">
   <label for="title">Title</label>
   <input type="text" placeholder="New project" name="title" id="title">
   <label for="category">Category</label>
   <select name="category" id="category">
      <option value="">Select a category</option>
      <option value="Professional">Professional</option>
       <option value="Personal">Personal</option>
       <option value="Charity">Charity</option>
</select>
<input type="submit" name="submit" value="Add">
</form>
```
We need a little `CSS`, so we’ve created a `style.css` file in the root as well:

```css
label {
  display: block;
}
```

# Form handling
Let’s treat our form.We’ll use the `＄_POST` method to get data for when the user submits the form. We’ll work with the following input: `<input type="text" name="title"> will translate to ＄_POST['title']`.

The first thing we’ll do is to check whether the form was submitted:


```php
if (isset($_POST['submit'])) {}
```
If the form was submitted, we can continue to work with the database connection in the `try/catch` block because we are already accustomed to it.

Then, we’ll place the submitted form values into variables for later use:


```php
if (isset($_POST['submit'])) {
try {
// db connection here
$title = trim($_POST['title']);
$category = $_POST['category'];
} catch {/*catch code here...*/}

} 
```
We’ll use the `trim()` function to delete white spaces.

> **Note**:<br>
> You may have noticed that the data sanitization process was not carried out. This is because weplan to work with [prepared statements](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php). However, it is recommeneded that developers [sanitize](https://www.php.net/manual/en/filter.filters.sanitize.php) as much as possible, especially when writing:
> ```php
> $title = trim($_POST['title'],  FILTER_SANITIZE_STRING);
>```
 
Now comes the SQL code, followed by the prepared statement and the value it is preparing (`＄sql`):

```php
$sql =  'INSERT INTO projects(title, category) VALUES(?, ?)';
 $statement = $connection->prepare($sql);
```

The `?` value is a placeholder that will be replaced by an exact value when the code is executed. We can also use named placeholders instead, like this:

```php
$sql =  'INSERT INTO projects(title, category) VALUES(:title, :category)';
```
Before we execute the statement, we need to check that the user did not send any empty values:

```php
if (empty($title) || empty($category)) {
$error_message = "Title or category empty";}
```
After verifying that there are no empty values, we’ll  execute the statement with an array of all the submitted values:

```php
$new_project = array($title, $category);
$statement->execute($new_project);
```
If we use a named placeholder, we can create our array like this:

```php
$new_project = array(
  'title' => $title, 
  'category' => $category
);
```
Here is the full PHP code:

Learn how to use the “INSERT INTO” SQL statement.


Introduction

App Initialization

Create and Read Records

Refactor the Application

PHP Data Validation

Generate Reports

Update and Delete Records

PHP Router

Conclusion

Appendix

Add a Project

The form