PHP and PostgreSQL CRUD Operations: A Step-by-Step Guide

PostgreSQL is a powerful, open-source relational database management system, and integrating it with PHP allows developers to build robust web applications. In this guide, we’ll explore the basics of connecting PHP to PostgreSQL and performing CRUD (Create, Read, Update, Delete) operations.

1. Set Up Your Development Environment

Before starting, ensure you have PHP and PostgreSQL installed on your machine. You’ll also need the PostgreSQL PHP extension. You can install it using:

sudo apt-get install php-pgsql

2. Create a PostgreSQL Database

In PostgreSQL, create a database and a table for your CRUD operations. Here’s a simple example:

CREATE DATABASE mydatabase;
\c mydatabase;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    age INT
);

3. Connect PHP to PostgreSQL

Create a PHP file (e.g., db.php) to handle the database connection:

<?php
$host = "localhost";
$port = "5432";
$dbname = "mydatabase";
$user = "your_username";
$password = "your_password";

$conn = pg_connect("host=$host port=$port dbname=$dbname user=$user password=$password");

if (!$conn) {
    die("Connection failed: " . pg_last_error());
}
?>

Replace 'your_username' and 'your_password' with your PostgreSQL username and password.

4. Perform CRUD Operations

Create (Insert) Record

Create a PHP file (e.g., create.php) to insert a new record into the users table:

<?php
require_once 'db.php';

$name = "John Doe";
$email = "[email protected]";
$age = 25;

$query = "INSERT INTO users (name, email, age) VALUES ('$name', '$email', $age)";

$result = pg_query($conn, $query);

if ($result) {
    echo "Record created successfully";
} else {
    echo "Error creating record: " . pg_last_error($conn);
}

pg_close($conn);
?>

Read (Select) Records

Create a PHP file (e.g., read.php) to fetch and display records:

<?php
require_once 'db.php';

$query = "SELECT * FROM users";
$result = pg_query($conn, $query);

if ($result) {
    while ($row = pg_fetch_assoc($result)) {
        echo "ID: {$row['id']} | Name: {$row['name']} | Email: {$row['email']} | Age: {$row['age']} <br>";
    }
} else {
    echo "Error fetching records: " . pg_last_error($conn);
}

pg_close($conn);
?>

Update Record

Create a PHP file (e.g., update.php) to update a record:

<?php
require_once 'db.php';

$id = 1;
$newAge = 30;

$query = "UPDATE users SET age=$newAge WHERE id=$id";
$result = pg_query($conn, $query);

if ($result) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . pg_last_error($conn);
}

pg_close($conn);
?>

Delete Record

Create a PHP file (e.g., delete.php) to delete a record:

<?php
require_once 'db.php';

$id = 1;

$query = "DELETE FROM users WHERE id=$id";
$result = pg_query($conn, $query);

if ($result) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . pg_last_error($conn);
}

pg_close($conn);
?>

5. Test Your CRUD Operations

Execute these PHP files in your web browser or through the command line to test your PostgreSQL CRUD operations. Make sure to handle errors gracefully and implement additional features based on your application’s requirements.

Conclusion

Integrating PHP with PostgreSQL provides a robust foundation for building database-driven web applications. Understanding CRUD operations allows you to manage data effectively and create dynamic, interactive experiences for users. As your application evolves, consider enhancing security measures, optimizing queries, and incorporating additional features to meet specific business needs.