Using PostgreSQL with PHP
Description
In this tutorial, we will explore how to use PostgreSQL with PHP, covering setup, connecting to a PostgreSQL database, and performing basic operations using PDO (PHP Data Objects).
Setup
Before starting, ensure you have PHP installed on your system with PDO enabled. Also, make sure the PostgreSQL PHP extension (pdo_pgsql) is installed:
<?php
// Check if pdo_pgsql extension is enabled
if (!extension_loaded('pdo_pgsql')) {
echo "PDO PostgreSQL extension not installed or enabled.";
} else {
echo "PDO PostgreSQL extension is enabled.";
}
?>
Connecting to PostgreSQL
To connect to a PostgreSQL database in PHP using PDO, use the following code snippet:
<?php
$host = 'localhost';
$dbname = 'mydatabase';
$user = 'myuser';
$password = 'mypassword';
try {
$pdo = new PDO("pgsql:host=$host;dbname=$dbname", $user, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected to PostgreSQL database";
} catch (PDOException $e) {
die("Connection failed: " . $e->getMessage());
}
?>
Connected to PostgreSQL database
Performing Basic Operations
Here are examples of basic CRUD operations using PHP with PostgreSQL:
Create (INSERT)
<?php
try {
$sql = "INSERT INTO employees (name, age) VALUES (?, ?)";
$stmt = $pdo->prepare($sql);
$stmt->execute(['Alice', 30]);
echo "Record inserted successfully.";
} catch (PDOException $e) {
die("Error: " . $e->getMessage());
}
?>
Record inserted successfully.
Read (SELECT)
<?php
try {
$sql = "SELECT name, age FROM employees WHERE age > ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([25]);
while ($row = $stmt->fetch()) {
echo "Name: " . $row['name'] . ", Age: " . $row['age'] . "
"; } } catch (PDOException $e) { die("Error: " . $e->getMessage()); } ?>
"; } } catch (PDOException $e) { die("Error: " . $e->getMessage()); } ?>
Update
<?php
try {
$sql = "UPDATE employees SET age = ? WHERE name = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([31, 'Alice']);
echo "Record updated successfully.";
} catch (PDOException $e) {
die("Error: " . $e->getMessage());
}
?>
Record updated successfully.
Delete
<?php
try {
$sql = "DELETE FROM employees WHERE name = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute(['Alice']);
echo "Record deleted successfully.";
} catch (PDOException $e) {
die("Error: " . $e->getMessage());
}
?>
Record deleted successfully.
Conclusion
Explanation: This concludes the tutorial on using PostgreSQL with PHP. You've learned how to install the necessary extensions, connect to a PostgreSQL database using PDO, and perform basic CRUD operations.