Connect to Database with PHP PDO and Perform Basic CRUD Operations

In this article, we'll cover how to connect to a database with PHP PDO (PHP Data Objects) and perform basic CRUD (Create, Read, Update, Delete) operations.

Connecting to the Database

First, we need to connect to the database. We'll use PHP PDO to connect to a MySQL database. Here's how you can establish a connection:

$dsn = 'mysql:host=localhost;dbname=mydatabase';
$username = 'myusername';
$password = 'mypassword';

try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

In this example, we specify the host, database name, username, and password. We then create a new PDO object and set the PDO::ATTR_ERRMODE attribute to PDO::ERRMODE_EXCEPTION. This tells PDO to throw an exception if there's an error connecting to the database.

CRUD Operations

Once we've established a connection to the database, we can perform CRUD operations. Here are some examples of basic CRUD operations using PDO.

Create

To create a new record in the database, we need to use an INSERT statement. Here's an example:

$sql = "INSERT INTO users (name, email, password) VALUES (?, ?, ?)";
$stmt = $pdo->prepare($sql);
$stmt->execute(['John Doe', 'john@example.com', 'password123']);

In this example, we prepare an INSERT statement with placeholders for the values we want to insert. We then execute the statement with an array of values.

Read

To read records from the database, we use a SELECT statement. Here's an example:

$sql = "SELECT * FROM users";
$stmt = $pdo->query($sql);

while ($row = $stmt->fetch()) {
    echo $row['name'] . "\n";
}

In this example, we prepare a SELECT statement and execute it with the query() method. We then iterate over the results with the fetch() method and output the name field.

Update

To update a record in the database, we use an UPDATE statement. Here's an example:

$sql = "UPDATE users SET name = ? WHERE id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute(['Jane Doe', 1]);

In this example, we prepare an UPDATE statement with placeholders for the values we want to update. We then execute the statement with an array of values.

Delete

To delete a record from the database, we use a DELETE statement. Here's an example:

$sql = "DELETE FROM users WHERE id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([1]);

In this example, we prepare a DELETE statement with a placeholder for the value we want to delete. We then execute the statement with an array of values.

Conclusion

In this article, we covered how to connect to a database with PHP PDO and perform basic CRUD operations. While these examples are simple, they should give you a good starting point for working with databases in PHP. As you become more familiar with PDO and SQL, you can use more advanced features to build more complex applications.