Embedded Files
Introduction
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). 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!
Instructions
Instructions
Take a look at the code editor. You’ll see the code we will go over throughout this lesson. Click “Next” when you’re ready to move on!
<?php
// Set the database name
$dbname = 'ccuser';
// Our database is hosted on the same machine as PHP so we'll use localhost
$hostname = '/tmp';
// Create the DSN (data source name) by combining the database type (PostgreSQL), hostname and dbname
$dsn = "pgsql:host=$hostname;dbname=$dbname";
// Set the username and password with permissions to the database
$username = 'ccuser';
$password = 'pass';
// Handle exceptions gracefully
try {
// Setup a connection by creating a database object
$db = new PDO($dsn, $username, $password);
// Query to SELECT the title of all books in the books table
$bookQuery = $db->query('SELECT title FROM books');
// Fetch just the next row
$book = $bookQuery->fetch(PDO::FETCH_ASSOC);
echo "Fetch first book:\n";
print_r($book);
// Fetch all rows
$books = $bookQuery->fetchAll(PDO::FETCH_ASSOC);
echo "Fetch all books:\n";
print_r($books);
// Create a prepared statement to find a book by ID
$id = 2;
// Prepare the query with :id as a placeholder
$bookQuery = $db->prepare('SELECT * FROM books WHERE id = :id');
// Map placeholder :id to variable $id
$bookQuery->execute(['id' => $id]);
// Fetch the book
$book = $bookQuery->fetch(PDO::FETCH_ASSOC);
echo "Fetch the book with id of 2:\n";
print_r($book);
// Find and return all books by Jane Austen
$author = 'Jane Austen';
$booksQuery = $db->prepare('SELECT * FROM books WHERE author = :author');
$booksQuery->execute(['author' => $author]);
$books = $booksQuery->fetchAll(PDO::FETCH_ASSOC);
echo "Fetch all books written by Jane Austen:\n";
print_r($books);
// Insert a new book into the database using a prepared statement
$title = 'Invisible Man';
$author = 'Ralph Ellison';
$year = 1953;
$newBookQuery = $db->prepare('INSERT INTO books (title, author, year) VALUES (:title, :author, :year)');
$newBookQuery->execute(['title' => $title, 'author' => $author, 'year' => $year]);
// Update an existing book in the database
$author = 'Charles Dickens';
$title = 'David Copperfield';
$year = 1850;
$updateBookQuery = $db->prepare('UPDATE books SET title = :title, year = :year WHERE author = :author');
$updateBookQuery->execute(['title' => $title, 'year' => $year, 'author' => $author]);
// Delete a book from the database
$id = 1;
$deleteBookQuery = $db->prepare('DELETE FROM books WHERE id = :id');
$deleteBookQuery->execute(['id' => $id]);
// To close the database connection, we must set all queries to null
$bookQuery = null;
$booksQuery = null;
$newBookQuery = null;
$updateBookQuery = null;
// Finally, setting the connection to null will close it
$db = null;
} catch (\Exception $e) {
// If an error is thrown, catch it, echo the message, then exit
echo $e->getMessage();
exit();
}
Page updated
Google Sites
Report abuse