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

**PDO (PHP Data Objects)** is an extension that enables connection to any SQL database.

Let’s see how we can use this extension.


At the root our project, we’ll create a new file and call it `install.php`. Inside the file, we’ll create a new `PDO()` and store it under the `$connection` variable.

```php
$connection = new PDO(params);
``` 

The parameters required for this process are:

* [DSN (data source name)](https://www.php.net/manual/en/ref.pdo-mysql.connection.php) that defines database type, host name, database name (optional) 
* The [**DSN (data source name)**](https://www.php.net/manual/en/ref.pdo-mysql.connection.php), which defines the database type, host name, and database name (optional)
* Username
* Password
* Additional options, such as encoding and error


Let’s see this in practice:

```php
<?php
// install.php
$connection = new PDO("mysql:host=localhost", "user", "user",
 array(
 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
 ););
``` 

We can organize our code by putting all the database information into variables:

```php
<?php
// install.php
$connection = new PDO("mysql:host=$host", $username, $password, $options);
``` 
Let’s put these variables into a new file. We will call this file `config.php`:

```php
<?php

$host = "localhost";
$username = "user";
$password = "user";
$dbname = "tracker";
$dsn = "mysql:host=$host;dbname=$dbname"; 
$options = array(
 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
 );
```
> **Be aware**: 
> We must ensure that the information we use in the `config.php` file is the same as that defined in `database.sql`. 
In case there are connection issues, we can change the `localhost` to `127.0.0.1`.

Now, our `install.php` file will look like this:

```php
<?php
// install.php
require "config.php";

$connection = new PDO("mysql:host=$host", $username, $password, $options);
``` 
Let’s use the content of our SQL code. We'll place the content of each `.sql` file into a variable, using the `file_get_contents()` function, and execute each file with the `exec()` function:

```php
// order of files matters

 $sql_db = file_get_contents("data/database.sql");
 $sql_structure = file_get_contents("data/structure.sql");
 $sql_content = file_get_contents("data/content.sql");

 $connection->exec($sql_db);
 $connection->exec($sql_structure);
 $connection->exec($sql_content);
```

We’ll also use the `try/catch` block to handle errors and exceptions:

```php
try {
// code to execute
} catch() {
// exception
}
```
After putting everything together, our `install.php` file should look like this:

```php
<?php
require "config.php";

try {
 $connection = new PDO("mysql:host=$host", $username, $password, $options);

 $sql_db = file_get_contents("data/database.sql");
 $sql_structure = file_get_contents("data/structure.sql");
 $sql_content = file_get_contents("data/content.sql");

 $connection->exec($sql_db);
 $connection->exec($sql_structure);
 $connection->exec($sql_content);

 echo "Database created and populated successfully. <a href='./'>Home</a>";
} catch (PDOException $error) {
 echo $sql . " " . $error->getMessage();
}
```

# Run the installer
Congratulations! We’ve successfully created an installer. Now, it’s time for us to test it.

Click the “Run” button below and use the provided link to call the installation script.


>**Note**: If everything works fine, we’ll see a message that will tell us that the database was created and populated successfully.

**PDO (PHP Data Objects)** is an extension that enables connection to any SQL database.

Let’s see how we can use this extension.


At the root our project, we’ll create a new file and call it `install.php`. Inside the file, we’ll create a new `PDO()` and store it under the `＄connection` variable.

```php
$connection = new PDO(params);
``` 

The parameters required for this process are:

* [DSN (data source name)](https://www.php.net/manual/en/ref.pdo-mysql.connection.php) that defines database type, host name, database name (optional) 
* The [**DSN (data source name)**](https://www.php.net/manual/en/ref.pdo-mysql.connection.php), which defines the database type, host name, and database name (optional)
* Username
* Password
* Additional options, such as encoding and error


Let’s see this in practice:

```php
<?php
// install.php
$connection = new PDO("mysql:host=localhost", "user", "user",
 array(
 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
 ););
``` 

We can organize our code by putting all the database information into variables:

```php
<?php
// install.php
$connection = new PDO("mysql:host=$host", $username, $password, $options);
``` 
Let’s put these variables into a new file. We will call this file `config.php`:

```php
<?php

$host = "localhost";
$username = "user";
$password = "user";
$dbname = "tracker";
$dsn = "mysql:host=$host;dbname=$dbname"; 
$options = array(
 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
 );
```
> **Be aware**: 
> We must ensure that the information we use in the `config.php` file is the same as that defined in `database.sql`. 
In case there are connection issues, we can change the `localhost` to `127.0.0.1`.

Now, our `install.php` file will look like this:

```php
<?php
// install.php
require "config.php";

$connection = new PDO("mysql:host=$host", $username, $password, $options);
``` 
Let’s use the content of our SQL code. We'll place the content of each `.sql` file into a variable, using the `file_get_contents()` function, and execute each file with the `exec()` function:

```php
// order of files matters

 $sql_db = file_get_contents("data/database.sql");
 $sql_structure = file_get_contents("data/structure.sql");
 $sql_content = file_get_contents("data/content.sql");

 $connection->exec($sql_db);
 $connection->exec($sql_structure);
 $connection->exec($sql_content);
```

We’ll also use the `try/catch` block to handle errors and exceptions:

```php
try {
// code to execute
} catch() {
// exception
}
```
After putting everything together, our `install.php` file should look like this:

```php
<?php
require "config.php";

try {
 $connection = new PDO("mysql:host=$host", $username, $password, $options);

 $sql_db = file_get_contents("data/database.sql");
 $sql_structure = file_get_contents("data/structure.sql");
 $sql_content = file_get_contents("data/content.sql");

 $connection->exec($sql_db);
 $connection->exec($sql_structure);
 $connection->exec($sql_content);

 echo "Database created and populated successfully. <a href='./'>Home</a>";
} catch (PDOException $error) {
 echo $sql . " " . $error->getMessage();
}
```

# Run the installer
Congratulations! We’ve successfully created an installer. Now, it’s time for us to test it.

Click the “Run” button below and use the provided link to call the installation script.


>**Note**: If everything works fine, we’ll see a message that will tell us that the database was created and populated successfully.

Learn how to connect a PHP script to MySQL, using the PDO extension.

Introduction

App Initialization

Create and Read Records

Refactor the Application

PHP Data Validation

Generate Reports

Update and Delete Records

PHP Router

Conclusion

Appendix

Database Connection With PDO