Using PHP PostgreSQL Extensions (pgsql, PDO_PGSQL) in ServBay

ServBay, a robust integrated local web development environment, comes with built-in support for PostgreSQL databases and provides the essential PHP extensions that allow developers to easily connect to and work with PostgreSQL in their PHP applications. For every supported version of PHP, both the pgsql and PDO_PGSQL extensions are pre-installed and enabled by default in ServBay.

Introduction to PHP PostgreSQL Extensions

PostgreSQL is a powerful, open-source, object-relational database system, known for its reliability, data integrity, and feature-rich ecosystem. To interact with PostgreSQL databases using PHP, relevant PHP extensions are required. ServBay primarily offers two extensions:

1. pgsql Extension: This is PHP's native extension for PostgreSQL, providing a collection of functions prefixed with pg_ (such as pg_connect, pg_query, pg_fetch_assoc, etc.) for direct database operations. This extension enables developers to take full advantage of PostgreSQL-specific features.
2. PDO_PGSQL Driver: Part of PHP Data Objects (PDO), this driver is specialized for connecting PHP applications to PostgreSQL. PDO offers a unified and abstract database access layer, allowing you to use the same API with various databases (including PostgreSQL, MySQL, SQLite, etc.), making your code more portable.

PHP PostgreSQL Extensions in ServBay

ServBay ensures that for every supported PHP version, the pgsql extension and the PDO_PGSQL driver are installed and enabled by default.

How to Enable PostgreSQL Extensions

In ServBay, you do not need to perform any manual steps to enable the pgsql or PDO_PGSQL extensions. They are already configured for you and ready to use directly in your PHP code.

Using PostgreSQL in Your PHP Code

With the extensions enabled, you can use pgsql functions or PDO objects to connect to and operate on PostgreSQL databases from your PHP code.

Prerequisites:

• You need to have the PostgreSQL service running in ServBay.
• Assume you have created a database named servbay_db.
• Assume you have created a user servbay_user with the password your_password, and granted the necessary permissions to servbay_db.
• Assume the servbay_db database contains a table named users with the columns id (SERIAL PRIMARY KEY), name (VARCHAR), email (VARCHAR), and age (INT).

You can perform these setups using ServBay's integrated adminer tool, or with other database management tools like DBeaver or pgAdmin.

Below are sample code snippets showing how to connect and perform basic operations using both methods:

Example Code Using the pgsql Extension

<?php
// --- pgsql extension example ---

// Database connection parameters
$host = "127.0.0.1"; // or 'localhost'
$port = "5432";     // Default PostgreSQL port
$dbname = "servbay_db";
$user = "servbay_user";
$password = "your_password"; // Replace with your actual password

// Build the connection string
$conn_string = "host={$host} port={$port} dbname={$dbname} user={$user} password={$password}";

// Connect to PostgreSQL database
$conn = pg_connect($conn_string);

// Check if the connection is successful
if (!$conn) {
    die("pgsql Connection failed: " . pg_last_error());
}
echo "pgsql Connected successfully<br>";

// Insert data (Note: pg_query does not support parameter binding directly. To prevent injection, escape manually or use pg_query_params)
$name = "ServBay Pgsql";
$email = "[email protected]";
$age = 7;

// Use pg_query_params for secure, parameterized queries
$query = "INSERT INTO users (name, email, age) VALUES ($1, $2, $3)";
$result = pg_query_params($conn, $query, array($name, $email, $age));

if ($result) {
    echo "pgsql: New record created successfully<br>";
} else {
    echo "pgsql Error: " . pg_last_error($conn) . "<br>";
}

// Query data
$query = "SELECT id, name, email, age FROM users WHERE name = $1";
$result = pg_query_params($conn, $query, array('ServBay Pgsql'));

if ($result) {
    echo "pgsql Queried data:<br>";
    // Check if any rows are returned
    if (pg_num_rows($result) > 0) {
        // Fetch all rows as associative arrays
        $data = pg_fetch_all($result, PGSQL_ASSOC);
        foreach ($data as $row) {
             echo "id: " . $row["id"]. " - Name: " . $row["name"]. " - Email: " . $row["email"]. " - Age: " . $row["age"]. "<br>";
        }
    } else {
        echo "pgsql: 0 results found<br>";
    }
} else {
    echo "pgsql Error querying data: " . pg_last_error($conn) . "<br>";
}

// Close the database connection
pg_close($conn);
?>

Example Code Using the PDO_PGSQL Driver

<?php
// --- PDO_PGSQL example ---

// Database connection parameters
$host = '127.0.0.1'; // or 'localhost'
$port = 5432;       // Default PostgreSQL port
$dbname = 'servbay_db';
$username = 'servbay_user';
$password = 'your_password'; // Replace with your actual password

// Data Source Name (DSN) for PostgreSQL
$dsn = "pgsql:host=$host;port=$port;dbname=$dbname";

// PDO connection options
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // Throw exceptions instead of warnings
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,       // Fetch as associative arrays by default
    // PDO::ATTR_EMULATE_PREPARES   => false, // Typically not required for PostgreSQL
];

try {
    // Create a new PDO instance
    $pdo = new PDO($dsn, $username, $password, $options);
    echo "PDO_PGSQL Connected successfully<br>";

    // Insert data (using prepared statements)
    $sql = "INSERT INTO users (name, email, age) VALUES (:name, :email, :age)";
    $stmt = $pdo->prepare($sql);

    $name = 'ServBay PDO Pgsql';
    $email = '[email protected]';
    $age = 12;

    // Bind parameters and execute
    $stmt->execute([
        ':name' => $name,
        ':email' => $email,
        ':age' => $age
    ]);
    echo "PDO_PGSQL: New record created successfully<br>";

    // Query data
    $stmt = $pdo->prepare("SELECT id, name, email, age FROM users WHERE name = :name");
    $stmt->execute([':name' => 'ServBay PDO Pgsql']);

    // Fetch all results
    $users = $stmt->fetchAll(); // Defaults to FETCH_ASSOC

    if ($users) {
        echo "PDO_PGSQL Queried data:<br>";
        foreach ($users as $row) {
            echo "id: " . $row['id'] . " - Name: " . $row['name'] . " - Email: " . $row['email'] . " - Age: " . $row['age'] . "<br>";
        }
    } else {
        echo "PDO_PGSQL: 0 results found<br>";
    }

} catch (\PDOException $e) {
    // Handle and display connection or query errors
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

// PDO connections are usually closed automatically at script end; explicit close is not needed.
// $pdo = null; // Optionally set to null to close
?>

Placing the Code: Save either example code as a .php file (e.g., pg_test_native.php or pg_test_pdo.php) and place it in your ServBay website root directory (such as /Applications/ServBay/www/myproject/). Then, access the corresponding URL in your browser (e.g., http://myproject.servbay.demo/pg_test_native.php). Make sure both your website and the PostgreSQL service are properly configured and running in ServBay.

Frequently Asked Questions (FAQ)

Q: Do I need to manually install the PHP PostgreSQL extensions (pgsql or PDO_PGSQL) in ServBay?

A: No. ServBay has already pre-installed and enabled the pgsql extension and PDO_PGSQL driver for all PHP versions it manages. You can use them directly in your code without any further installation or configuration.

Q: What’s the difference between the pgsql extension and the PDO_PGSQL driver? Which should I use?

A:

• The pgsql extension offers a set of functions that are specific to PostgreSQL (like pg_connect, pg_query_params). If you need to access database features unique to PostgreSQL, this can be more direct.
• PDO_PGSQL is part of the PDO abstraction layer. Using PDO makes your code more portable across different databases (like MySQL) with minimal changes. PDO enforces the use of prepared statements, which is considered a secure, modern standard in PHP development.
• Recommendation: For new projects, or if you value portability and security, it’s recommended to use PDO_PGSQL. If maintaining legacy projects or you specifically need some pgsql functions, you can use the pgsql extension.

Q: Where can I find the necessary info (host, port, username, password, database name) to connect to PostgreSQL in ServBay?

A:

• Host: Typically 127.0.0.1 or localhost.
• Port: The standard PostgreSQL port is 5432. You can confirm your actual port in ServBay's PostgreSQL package management interface.
• Username / Password / Database Name: These are created by you within the PostgreSQL service. Use ServBay’s integrated adminer or other PostgreSQL client tools (like pgAdmin, DBeaver) to create databases, users, and set passwords and permissions.

Q: How can I prevent SQL injection when using the pgsql extension?

A: Avoid directly interpolating user input into SQL queries. Always use the pg_query_params() function, which separates queries and their parameters so the driver can safely handle input. Alternatively, if using pg_query(), escape all user input using pg_escape_string() or pg_escape_literal() (but pg_query_params remains the better option).

Conclusion

ServBay greatly simplifies connecting PHP applications to PostgreSQL databases in a local macOS environment by pre-installing and enabling both the pgsql and PDO_PGSQL extensions by default. Developers do not have to worry about the hassles of extension installation or configuration and can choose the extension that best fits their project needs (with PDO recommended for compatibility and security). This allows you to focus on application logic and efficiently leverage PostgreSQL’s robust features to build reliable web applications.