How to Use PHP's pg_connect() Function to Connect to PostgreSQL Database and Perform Basic Operations

In PHP, the pg_connect() function is typically used to connect to a PostgreSQL database (rather than the connect() function itself). This article will provide practical examples on how to connect to a PostgreSQL database using PHP and perform basic CRUD operations.

1. Connect to the Database

The pg_connect() function is used to establish a connection to a PostgreSQL database. Below is a basic example:


<?php
// Database connection parameters
$conn_string = "host=m66.net port=5432 dbname=testdb user=testuser password=secret";

echo "Successfully connected to the PostgreSQL database";
?>


2. Create a Table

Once connected, we can create a simple table, such as a user information table:


<?php
$query = "CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
)";

if ($result) {
echo "Table created successfully";
} else {
echo "Failed to create table: " . pg_last_error($dbconn);
}
?>


3. Insert Data

Next, we can insert data into the table:


<?php
$name = 'Zhang San';
$email = 'zhangsan@m66.net';

if ($result) {
echo "Data inserted successfully";
} else {
echo "Failed to insert data: " . pg_last_error($dbconn);
}
?>


4. Query Data

You can query data and output the results:


<?php
$query = "SELECT * FROM users";
$result = pg_query($dbconn, $query);

if ($result) {
while ($row = pg_fetch_assoc($result)) {
echo "ID: " . $row['id'] . "，Name: " . $row['name'] . "，Email: " . $row['email'] . "
";
}
} else {
echo "Query failed: " . pg_last_error($dbconn);
}
?>


5. Update Data

Here is an example of updating a user's email:


<?php
$query = "UPDATE users SET email='newemail@m66.net' WHERE name='Zhang San'";
$result = pg_query($dbconn, $query);

if ($result) {
echo "Update successful";
} else {
echo "Update failed: " . pg_last_error($dbconn);
}
?>


6. Delete Data

The following code deletes a user record:


<?php
$query = "DELETE FROM users WHERE name='Zhang San'";
$result = pg_query($dbconn, $query);

if ($result) {
echo "Deletion successful";
} else {
echo "Deletion failed: " . pg_last_error($dbconn);
}
?>


7. Close the Connection

After completing the operations, you should close the database connection:


<?php
pg_close($dbconn);
?>


Conclusion

This article explained how to use PHP's pg_connect() function to connect to a PostgreSQL database and perform basic operations like table creation, data insertion, querying, updating, and deletion. Properly encapsulating these operations can improve code reusability and maintainability. In real projects, it is recommended to use prepared statements to prevent SQL injection issues.