Working with Databases in PHP: PostgreSQL and MongoDB for Beginners

Introduction: Why PHP and Databases Are a Powerful Combination

Any modern website or application is not just a set of pages, but a dynamic system that stores and processes data. PHP, as one of the most popular languages for web development, provides powerful tools for working with databases. In this article, we will cover two key database management systems (DBMS): the relational PostgreSQL and the document-oriented MongoDB.

For a beginner, choosing between them may seem difficult, but in fact each solves its own tasks. PostgreSQL is a reliable SQL standard for structured data, while MongoDB is a flexible NoSQL system for rapid prototyping and working with JSON-like documents. We will show how to connect to both from PHP, perform basic operations (CRUD), and provide practical tips.

1. Environment Setup: What You Need to Install

Before writing code, make sure you have the necessary PHP extensions installed. For working with PostgreSQL, the pdo_pgsql extension is required; for MongoDB, mongodb (via PECL or Composer).

Installing Extensions

\n
• PostgreSQL: Uncomment the line extension=pdo_pgsql in php.ini or install via a package manager (e.g., sudo apt install php-pgsql).
\n
• MongoDB: Use Composer: composer require mongodb/mongodb — this will install the official library.
\n

After installation, verify that the extensions are active by running php -m | grep -i pgsql and php -m | grep -i mongodb.

2. PostgreSQL and PHP: Reliability and SQL

PostgreSQL is a powerful relational DBMS with support for ACID transactions, complex queries, and JSON fields. For working with it in PHP, it is most convenient to use PDO (PHP Data Objects) — a universal interface.

Connecting to PostgreSQL

Create a file connect_pgsql.php:

<?php\n$host = 'localhost';\n$dbname = 'testdb';\n$user = 'postgres';\n$pass = 'password';\n\ntry {\n    $pdo = new PDO("pgsql:host=$host;dbname=$dbname", $user, $pass);\n    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);\n    echo "Connection to PostgreSQL successful!";\n} catch (PDOException $e) {\n    die("Connection error: " . $e->getMessage());\n}\n?>

CRUD Operations Using the users Table as an Example

Let's create a table and perform basic queries.

Creating a Table

<?php\n$sql = "CREATE TABLE IF NOT EXISTS users (\n    id SERIAL PRIMARY KEY,\n    name VARCHAR(100) NOT NULL,\n    email VARCHAR(150) UNIQUE NOT NULL,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n)";\n$pdo->exec($sql);\necho "Table created.";\n?>

Inserting Data (CREATE)

<?php\n$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");\n$stmt->execute(['name' => 'Ivan Petrov', 'email' => 'ivan@example.com']);\necho "User added.";\n?>

Reading Data (READ)

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

Updating (UPDATE) and Deleting (DELETE)

<?php\n// Update\n$stmt = $pdo->prepare("UPDATE users SET name = :name WHERE id = :id");\n$stmt->execute(['name' => 'Pyotr Sidorov', 'id' => 1]);\n\n// Delete\n$stmt = $pdo->prepare("DELETE FROM users WHERE id = :id");\n$stmt->execute(['id' => 2]);\n?>

Tip: Always use prepared statements (prepare) to protect against SQL injection.

3. MongoDB and PHP: Flexibility Without a Schema

MongoDB stores data in the form of BSON documents (binary JSON).