Learn how to combine PHP with a PostgreSQL database to store user input.

Connecting to Databases

Now that we've created a database object, `$db`, we can call its methods to fetch data from the database.

Let's take a look at an example database. It contains a `books` table with a list of books with their `id`, `title`, `author`, and published `year`.

| id   | title                | author              | year |
| ---- | -------------------- | ------------------- | ---- |
| 1    | Don Quixote          | Miguel de Cervantes | 1605 |
| 2    | Robinson Crusoe      | Daniel Defoe        | 1719 |
| 3    | Pride and Prejudice  | Jane Austen         | 1813 |
| 4    | Emma                 | Jane Austen         | 1816 |
| 5    | A Tale of Two Cities | Charles Dickens     | 1859 |

We'll begin with a query to fetch book titles. We'll use the `query()` method on the `$db` object to create and execute the query. We can do this by referencing the `$db` object with the object operator (`->`) before calling the `query()` method.

```php
// Create the query
$bookQuery = $db->query('SELECT title FROM books');
```

Does the syntax in parenthesis look familiar? That's because it's a regular SQL query that is understood by the database.

Now let's fetch the result of the query and assign it to the `$book` variable. We do this by calling the `fetch()` method on `$bookQuery`.

```php
// Fetch the first book and assign it to $book 
$book = $bookQuery->fetch(); 
```

Although our `SELECT` statement above queries the database for _all_ book titles, the `fetch()` method returns only _one_ result.

To return a list of _all_ book titles, we can use the `fetchAll()` method instead:

```php
// Fetch all books and assign them to $books
$books = $bookQuery->fetchAll(); 
```

If we use `print_r` to look at the value of `$book`, we'll see that the data is returned as both an ordered (indexed) and associative array.

```php
Array ( [title] => Don Quixote [0] => Don Quixote )
```

We can change that by setting a [**fetch mode**](https://www.php.net/manual/en/pdo.constants.php), which tells PDO in what format to return our data. To get our book as an associative array, we pass `PDO::FETCH_ASSOC` as an argument to the `fetch()` method.

```php
// Fetch the next row and assign the result to $book
$book = $bookQuery->fetch(PDO::FETCH_ASSOC);
```

If we check `$book` now, we'll see:

```php
Array ( [title] => Don Quixote )
```

Creating Queries

Although we can use PHP to send SQL queries to PostgreSQL, sometimes SQL queries can be dangerous.

For example, how would we write a query that lets a user get a book's details by providing its ID?

```php
// Get the ID from the frontend
$id = $_POST['id'];

// Like this?
$booksQuery = $db->query('SELECT * FROM books WHERE id = ' . $id);
```

But what if instead of entering a number, a malicious user enters `1 or 1 = 1`?

Then the database will run this query:

```php
$booksQuery = $db->query('SELECT * FROM books WHERE id = 1 or 1 = 1');
```

And since 1 = 1 is always true, the database will return _every_ row from the books table. While returning all books might not be a big problem, an attacker can use the same technique to return a list of all users, passwords, and other confidential information!

**SQL injection** happens when a malicious user provides a SQL command instead of data, and the database executes it. We can prevent SQL injection by telling the database which values should be treated *_only_* as data.

We do that with  **prepared statements**.

A **prepared statement** is a pre-defined template containing SQL and optionally **placeholders**. We use placeholders to tell the database where to place the data we will provide when executing the statement.

We create prepared statements using the `$db` object's `prepare()` method.  And instead of appending the `$id` variable, we use the **placeholder** `:id` .

```php
// Get the ID from the frontend
$id = $_POST['id'];

// Create a prepared statement
$bookQuery = $db->prepare('SELECT * FROM books WHERE id = :id');
```

The next step is to run the `execute()` method, and pass in an array with a key-value pair which maps our placeholders to variables.

```php
// Map :id to $id
$bookQuery->execute(['id' => $id]);
```

The final step is to fetch the result:

```php
$book = $bookQuery->fetch(PDO::FETCH_ASSOC);
```

SQL Injection

So, how did you do with all those exercises? If your experience was anything like ours, you probably didn't get everything right the first time. In fact, to let you in on a little secret, even the best programmers make mistakes.

And sometimes, even when our code works well, there can be issues beyond our control. For example, PHP might be unable to connect to the database, or a third-party service we rely on, such as a payment processor or an email delivery service, might be down.

How do we handle such problems? PHP provides us with **exception handling**, which lets us rescue our program from such situations and tell it what to do next. 

We do this using **`try`/`catch`** blocks.

We start with a `try` block, which tells the PHP interpreter to _try_ executing some code:

```php
try {
    // Charge user's credit card
    // Add transaction to the database
    // Email receipt to user
}
```

If an exception is **thrown**, it will be **caught**, and PHP will immediately begin running code in the `catch` block.

```php
// When an exception occurs
catch (Exception $e) {
    // Rollback transaction
    // Add error to the log
    // Notify user that an error occurred
}
```

The argument to the `catch` block is the exception object which will catch _all_ exceptions thrown. Assigning the exception object to the variable `$e` lets us call several useful methods to help us troubleshoot the exception.

```php
// Echo out the exception object's message which often has useful information
echo $e->getMessage();
```

Exception Handling

Great job on completing this lesson. Let's take a moment to review some of the material covered in this lesson:
- Databases store data for PHP applications.
- PDO lets us write code that will work with many common database systems.
- We write queries by inserting SQL into the `query()` method.
- SQL queries with user-defined parameters (variables) are vulnerable to SQL injection.
- We use prepared statements to prevent SQL injection.
- Prepared statements use the `prepare()` and `execute()` methods and send SQL commands and data separately.
- For exception handling, we wrap code in a `try`/`catch` block.
- We use exception handling to catch and handle exceptions when our program throws them.
- We can use methods on the exception object to troubleshoot exceptions.

You're now well on your way to creating database-driven PHP applications!

Review

In our previous lesson, we looked at how to handle user input from forms. Combining PHP with a database will let us store the user input to use in our applications. In this lesson, we will learn how to use PostgreSQL to store large amounts of structured data and perform complex operations.

Let's take a look at how we will structure our application. We will use PHP to:
- Process information
- Apply business logic
- Communicate with customers, vendors, and services
- Tell the database which operations to perform and when

We will connect a PostgreSQL database to:
- Store data
- Read, write, update, and delete data when PHP instructs it

There are several ways to connect PHP to a database. In this lesson, we will be using [PDO (PHP Data Objects)](https://www.php.net/manual/en/book.pdo.php). PDO comes out of the box with all supported PHP versions. It provides a layer of abstraction that lets us write code for many popular database systems such as PostgreSQL, MySQL, MariaDB, and SQLite.

Once we connect PHP to PostgreSQL, we'll learn how to interact with the database securely, identify and fix insecure statements, and catch errors during our program's execution.

Let's get started with learning how to combine PHP with PostgreSQL to create powerful and dynamic user experiences!

Introduction

When working with databases, you may have used a graphical tool such as Postbird, PgAdmin, or PhpMyAdmin. These tools are **clients**, which create a connection to the database **server** and send commands to it.

Similarly, we will be setting up PHP to act as a client which will send queries to PostgreSQL.

To connect to a server, a client needs to know where it is. We provide this information in the form of a **DSN** (data source name), which holds our database type (PostgreSQL), hostname, and database name.  The **hostname** contains the location of the database. If PostgreSQL is running on the same host (computer) as PHP, we use `localhost`. If it's running on a remote server, we use its IP address or domain name. For Codecademy's environment, we will use `/tmp`.

```php
// Assign variables for the hostname and database name
$hostname = '/tmp';
$dbname = 'ccuser';

// Create the DSN (data source name)
$dsn = "pgsql:host=$hostname;dbname=$dbname";
```

The first part of the DSN is the **prefix**, where we specify which database system to use. For PostgreSQL, that is `pgsql`. Then we add a colon, followed by a key/value pair containing the hostname and database name, separated by a semi-colon. The DSN uses a precise format, so it's important not to include spaces or extra characters.

We'll also need to store the username and password of an account with the correct permissions for the database. 

```php
$username = 'ccuser';
$password = 'pass';
```

We'll start the connection by creating a database object from the PDO class and instantiating it using the `new` keyword. The PDO constructor takes the `$dsn`, `$username`, and `$password` variables as its arguments in that order. If the connection is successful, the database object will be assigned to `$db`.

```php
$db = new PDO($dsn, $username, $password);
```

After we complete our database operations, we will end the connection by setting the database object and any of its references to null. We can do this with:

```php
$db = null;
```