Codecademy Logo

Connecting to Databases

Related learning

  • Free course

    Learn Intermediate PHP

    Expand your knowledge in PHP to learn how to create dynamic web applications using requests, databases, files, and APIs.
    • Intermediate.
      4 hours

PHP and Databases

PHP is often combined with a database system so that a website or app can store and manipulate data.

PHP Data Objects

PHP Data Objects (PDO) provides an abstraction layer for communicating with many types of database systems using a common set of functions.

PDO Database Connections

PDO can connect to a database server hosted locally using an IP address or hostname. It can also connect to a remote database server using an IP address or domain name.

PDO Database Object

A database object is created using the new keyword to invoke the PDO class’s constructor.

$db = new PDO();

PDO Database Connection

A connection to a database is initiated by passing a DSN, username, and password, in that order, as arguments to the new PDO() call.

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

Data Source Name

A Data Source Name (DSN) is written with a database driver, then a colon, followed by a hostname and a database name in property=value format separated by a semi-colon.

$hostname = 'cutekittens.amazonaws.com';
$dbname = 'kittens';


$dsn = "pgsql:host=$hostname;dbname=$dbname";

Terminating a Database Connection

To terminate a database connection, set the database object and any references to it, to null.

$db = null;

The fetch() Method

To fetch one result from the database, create the query using the database object’s query() method, then call the query object’s fetch() method.

// Create the query
$kittenQuery = $db->query('SELECT * FROM kittens');


// Execute the query, and assign the result to $kitten
$kitten = $kittenQuery->fetch();

The fetchAll() Method

To fetch all matching results from the database, create a query using the database object’s query() method, then call the query object’s fetchAll() method.

// Create the query
$kittensQuery = $db->query('SELECT * FROM kittens');


// Execute the query and assign the result to $kittens
$kittens = $kittensQuery->fetchAll();

Fetch Modes

A fetch mode can be provided to the fetch() and fetchAll() methods to control how data is returned.

Default Fetch Mode

If no mode is passed to fetch() or fetchAll(), the mode defaults to PDO::BOTH, which returns an array containing both column names and numbers as keys.

The FETCH_ASSOC Mode

The PDO::FETCH_ASSOC mode is passed as an argument to the fetch() and fetchAll() functions to return an associative array containing column names as keys.

// Create the query
$kittensQuery = $db->query('SELECT * FROM kittens');
 
// Execute the query, and assign the result to $kittens
$kittens = $kittenQuery->fetchAll(PDO::FETCH_ASSOC);

Dangerous Queries

SQL statements that take user-defined variables are vulnerable to SQL injection.

// WARNING: Don't do this!
$kittenQuery = $db->query('SELECT * FROM kittens WHERE id = ' . $id);

Prepared Statements

Prepared statements offer protection against SQL injection by separating data from SQL commands.

Using Prepared Statements

Use a prepared statement to safely include information sent by an end-user in a SQL statement.

// Set $name to value sent by user
$name = $_POST['name'];
// Create query using ':name' placeholder
$kittenQuery = $db->prepare('SELECT * FROM kittens WHERE name = :name');
// Bind placeholder to variable and execute query
$kittenQuery->execute(['name' => $name]);
// Fetch result and assign it to $kitten
$kitten = $kittenQuery->fetch(PDO::FETCH_ASSOC);

Exception Handling

Exception handling is used to rescue an application that has encountered an unexpected error by providing alternative instructions.

Exception Handling Blocks

Exceptions in PHP can be handled using try/catch blocks.

try {
  // Try running this code first
} 
catch (Exception $e) {
  // Run this code if there's an exception
}

Exception Handling Flow

The PHP interpreter first attempts to execute code in the try block. If it encounters an exception, it stops and begins executing code in the catch block instead.

try {
  // Try running this code first
} 
catch (Exception $e) {
  // Run this code if there's an exception
}

Exception Subclasses

The catch block can take the exception class (Exception) as a parameter or a subclass of the exception class, such as a more specific exception type.

try {
  // Run checkout code
} catch (CheckoutException $e) {
    // Handle checkout exception with a custom CheckoutException class
}

Learn more on Codecademy

  • Free course

    Learn Intermediate PHP

    Expand your knowledge in PHP to learn how to create dynamic web applications using requests, databases, files, and APIs.
    • Intermediate.
      4 hours
Next