Working with Databases in PHP: PostgreSQL and MongoDB

Introduction: Why a PHP Developer Should Be Able to Work with Different Databases

Modern web development rarely goes without interacting with databases. PHP, as one of the most popular server-side languages, offers flexible tools for working with both relational (SQL) and document-oriented (NoSQL) systems. In this article, we will thoroughly examine connecting to and performing basic operations with two popular databases: PostgreSQL and MongoDB. You will learn how to set up a connection, execute queries, and process results using modern PHP practices.

1. Working with PostgreSQL in PHP via PDO

PostgreSQL is a powerful object-relational DBMS. To work with it in PHP, it is recommended to use the PDO (PHP Data Objects) extension, which provides a unified interface for different databases and enhances security through prepared statements.

1.1 Connecting to the Database

First, make sure the pdo_pgsql extension is enabled in your php.ini. Connection example:

<?php$host = 'localhost';$dbname = 'my_database';$user = 'my_user';$password = 'my_password';

try {    $dsn = "pgsql:host=$host;dbname=$dbname";    $pdo = new PDO($dsn, $user, $password);    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    echo "Connection established successfully";} catch (PDOException $e) {    die("Connection error: " . $e->getMessage());}?>

1.2 Executing Queries (CRUD)

Let's look at the basic operations using the users table as an example.

Creating a record (INSERT):

<?php$sql = "INSERT INTO users (name, email) VALUES (:name, :email)";$stmt = $pdo->prepare($sql);$stmt->execute([':name' => 'Ivan Ivanov', ':email' => 'ivan@example.com']);echo "User added, ID: " . $pdo->lastInsertId();?>

Reading data (SELECT):

<?php$stmt = $pdo->query("SELECT id, name, email FROM users");while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {    echo "ID: {$row['id']}, Name: {$row['name']}, Email: {$row['email']}<br>";}?>

Updating (UPDATE) and deleting (DELETE):

<?php// Update$sql = "UPDATE users SET email = :email WHERE id = :id";$stmt = $pdo->prepare($sql);$stmt->execute([':email' => 'newemail@example.com', ':id' => 1]);

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

2. Working with MongoDB in PHP

MongoDB is a leading NoSQL database that stores data in JSON-like documents. To work with it in PHP, the official mongodb extension and the MongoDB PHP Library (installed via Composer) are used.

2.1 Installation and Connection

Install the library via Composer:

composer require mongodb/mongodb

Example of connecting to a local MongoDB server:

<?phprequire 'vendor/autoload.php';

$client = new MongoDB\\Client("mongodb://localhost:27017");$database = $client->selectDatabase('my_database');$collection = $database->selectCollection('users');echo "Connection to MongoDB established";?>

2.2 CRUD Operations with Documents

Inserting a document (INSERT):

<?php$insertResult = $collection->insertOne([    'name' => 'Maria Petrova',    'email' => 'maria@example.com',    'age' => 28]);echo "Document added with ID: " . $insertResult->getInsertedId();?>

Finding documents (SELECT):

<?php$cursor = $collection->find(['age' => ['$gte' => 25]]);