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

Let’s create a file called `project_list.php` at the root of our project, and add some basic `HTML` code into it. We’ll add our `PHP` code in the `body` tag.

First things first, let’s add the connection to the database and the `try/catch` block:

```php
<?php
 try {
        require "./config.php";

        $connection = new PDO($dsn, $username, $password, $options);
       
    } catch (PDOException $err) {
        echo $err->getMessage();
    }
    ?>
``` 

# Read from the database
Inside the `try` block, under the `$connection` variable, we’ll add our `SELECT` SQL query:

```php
$sql =  'SELECT * FROM projects ORDER BY title';
$statement = $connection->query($sql);
```


We’ll execute the SQL query with the [PDO::query](https://www.php.net/manual/en/pdo.query.php) and place the result into a `$statement` variable.

We’ll also count the number of records: `$projectCount = $statement->rowCount();`

> **Note:** We can also use the fetchAll() method to return all the entrees of our query:
>```php
>$result = $statement->fetchAll();
>```
> or, if we want to chain all the entrees in the same line:
>```php
>$statement = $connection->query($sql)->fetchAll();
>```


> However, remember to be mindful of [memory consumption](https://phpdelusions.net/pdo#fetchall).

Now, we have a run-down of the whole process that is used to retrieve all the projects from the database. The next step is to print the result on the screen.

Outside the `try/catch` block, we’ll create a simple container, for instance, a `div` tag.

We’ll add the following  as the title of the page: `<h1>Project list(<?php echo $projectCount ?>)</h1>`

Then, we’ll create a `foreach` loop to iterate over the database result:

```php
<?php foreach ($statement as $row) : ?>
   <ul><li><?php echo $row["title"] ?> </li></ul>
<?php endforeach; ?>
```
We’ll print each title on the screen, using the `li` tag.

> **Notes**:<br>
> * In case you feel confused about the syntax used for the `foreach` loop, consider reading through the document linked [here](https://www.php.net/manual/en/control-structures.alternative-syntax.php).
> * If you are already familiar with the `while` loop and want to use it, below is how we will do it in the course:
> ```php
> while ($result = $statement->fetch) { ?>
> <ul><li><?php echo $row["title"] ?> </li></ul>
> <?php } ?>
>```

In case a project is created,  we’ll add an `if` statement to display a message:

```php
 if ( $projectCount == 0 ) {
        echo "<p>You have not yet added any project </p>";
        echo "<p><a href='#'>Add project</a></p>";
 }
```
# Run the code 
The full code inside the `body` tag will look like this:

```html
<?php
    try {
        require "./config.php";

        $connection = new PDO($dsn, $username, $password, $options);

        $sql =  'SELECT * FROM projects ORDER BY title';

        $statement = $connection->query($sql);
        // $result = $statement->fetchAll();
        $projectCount = $statement->rowCount();
    } catch (PDOException $err) {
        echo $sql . "<br>" . $err->getMessage();
    }
    ?>
    <div>
        <h1>Project list (<?php echo $projectCount ?>)</h1>
        
        <?php
        if ($projectCount <= 0) {
            echo "<p>You have not yet added any project </p>";
            echo "<p><a href='#'>Add project</a></p>";
        }

        ?>
          <?php foreach ($statement as $row) : ?>
      <ul><li><?php echo $row["title"] ?> </li></ul>
<?php endforeach; ?>
    </div>

    <p><a href="./">Home</a></p>
```
Now, we can run the code and get this:

Let’s create a file called `project_list.php` at the root of our project, and add some basic `HTML` code into it. We’ll add our `PHP` code in the `body` tag.

First things first, let’s add the connection to the database and the `try/catch` block:

```php
<?php
 try {
        require "./config.php";

        $connection = new PDO($dsn, $username, $password, $options);
       
    } catch (PDOException $err) {
        echo $err->getMessage();
    }
    ?>
``` 

# Read from the database
Inside the `try` block, under the `＄connection` variable, we’ll add our `SELECT` SQL query:

```php
$sql =  'SELECT * FROM projects ORDER BY title';
$statement = $connection->query($sql);
```


We’ll execute the SQL query with the [PDO::query](https://www.php.net/manual/en/pdo.query.php) and place the result into a `＄statement` variable.

We’ll also count the number of records: `＄projectCount = ＄statement->rowCount();`

> **Note:** We can also use the fetchAll() method to return all the entrees of our query:
>```php
>$result = $statement->fetchAll();
>```
> or, if we want to chain all the entrees in the same line:
>```php
>$statement = $connection->query($sql)->fetchAll();
>```


> However, remember to be mindful of [memory consumption](https://phpdelusions.net/pdo#fetchall).

Now, we have a run-down of the whole process that is used to retrieve all the projects from the database. The next step is to print the result on the screen.

Outside the `try/catch` block, we’ll create a simple container, for instance, a `div` tag.

We’ll add the following  as the title of the page: `<h1>Project list(<?php echo ＄projectCount ?>)</h1>`

Then, we’ll create a `foreach` loop to iterate over the database result:

```php
<?php foreach ($statement as $row) : ?>
   <ul><li><?php echo $row["title"] ?> </li></ul>
<?php endforeach; ?>
```
We’ll print each title on the screen, using the `li` tag.

> **Notes**:<br>
> * In case you feel confused about the syntax used for the `foreach` loop, consider reading through the document linked [here](https://www.php.net/manual/en/control-structures.alternative-syntax.php).
> * If you are already familiar with the `while` loop and want to use it, below is how we will do it in the course:
> ```php
> while ($result = $statement->fetch) { ?>
> <ul><li><?php echo $row["title"] ?> </li></ul>
> <?php } ?>
>```

In case a project is created,  we’ll add an `if` statement to display a message:

```php
 if ( $projectCount == 0 ) {
        echo "<p>You have not yet added any project </p>";
        echo "<p><a href='#'>Add project</a></p>";
 }
```
# Run the code 
The full code inside the `body` tag will look like this:

```html
<?php
    try {
        require "./config.php";

        $connection = new PDO($dsn, $username, $password, $options);

        $sql =  'SELECT * FROM projects ORDER BY title';

        $statement = $connection->query($sql);
        // $result = $statement->fetchAll();
        $projectCount = $statement->rowCount();
    } catch (PDOException $err) {
        echo $sql . "<br>" . $err->getMessage();
    }
    ?>
    <div>
        <h1>Project list (<?php echo $projectCount ?>)</h1>
        
        <?php
        if ($projectCount <= 0) {
            echo "<p>You have not yet added any project </p>";
            echo "<p><a href='#'>Add project</a></p>";
        }

        ?>
          <?php foreach ($statement as $row) : ?>
      <ul><li><?php echo $row["title"] ?> </li></ul>
<?php endforeach; ?>
    </div>

    <p><a href="./">Home</a></p>
```
Now, we can run the code and get this:

Learn about the SELECT statement for reading data from the database and the foreach loop, which is used for looping over selected data and displaying it to the user.

Introduction

App Initialization

Create and Read Records

Refactor the Application

PHP Data Validation

Generate Reports

Update and Delete Records

PHP Router

Conclusion

Appendix

List All Project