Connecting to PostgreSQL
Then, pg_connect() connects to the data source. You have to provide a connection string that contains all important data, including the host, port, name of the database, and user credentials.
<?php
$host = 'localhost';
$port = 5432; //default port
$user = 'postgres';
$pass = 'password';
$db = 'brainbell';
$conn = pg_connect( "host=$host port=$port
dbname=$db user=$user password=$pass");
if ($conn === false) {
echo 'Connection failed';
exit;
}
echo 'Connected to the database.';
pg_close($conn);
Sending SQL with pg_query()
<?php // Syntax pg_query($conn, $sql);
The function pg_query() sends SQL to the PostgreSQL installation.
Sending SQL to PostgreSQL
<?php
$user = 'postgres';
$pass = 'password';
$db = 'brainbell';
$conn = pg_connect("dbname=$db user=$user password=$pass");
$name = 'BrainBell.com';
$email = 'admin@brainbell.com';
pg_query($conn, "INSERT INTO user_table (name, email)
VALUES ('$name', '$email')");
echo 'Record saved.';
pg_close($conn);
pg_escape_string()
Escaping potentially dangerous characters such as single quotes is a must; this can be done with the pg_escape_string() function. In this code, you see the following code accepts an HTML form and writes it to the database.
<?php
//...
$name = pg_escape_string( $_POST['name'] );
$email = pg_escape_string( $_POST['email'] );
pg_query($conn, "INSERT INTO user_table (name, email)
VALUES ('$name', '$email')");
Inserting Data in PostgreSQL
Another way to insert or update data in PostgreSQL comes in the form of the functions pg_insert() and pg_update().
The first parameter must be the database handle, the second parameter is the table to be inserted into/updated, and the third parameter contains some data in the form of an associative array (column names are the keys).
Inserting Data with pg_insert to PostgreSQL
<?php
$user = 'postgres';
$pass = 'password';
$db = 'brainbell';
$conn = pg_connect("dbname=$db user=$user password=$pass");
$name = pg_escape_string( $_POST['name'] );
$email = pg_escape_string( $_POST['email'] );
$data = array('name'=>$name, 'email'=>$email);
pg_insert($conn, 'user_table', $data);
echo 'Record saved.';
pg_close($conn);
Alternatively, you can use pg_query function with INSERT SQL statement to insert new records:
<?php
//...
// pg_insert($conn, 'user_table', $data);
pg_query($conn, "INSERT INTO user_table (name, email)
VALUES ('$name', '$email')");
Updating Data in PostgreSQL
In the event of an UPDATE SQL statement, the update condition must also be submitted as an array in the fourth parameter of the function.
Updating Data with pg_update to PostgreSQL
<?php
$user = 'postgres';
$pass = 'password';
$db = 'brainbell';
$conn = pg_connect("dbname=$db user=$user password=$pass");
$name = pg_escape_string( $_POST['name'] );
$email = pg_escape_string( $_POST['email'] );
$data = array('name'=>$name, 'email'=>$email);
$condition = array();
$condition['id'] = 21;
pg_update($conn, 'user_table', $data, $condition);
echo 'Record updated.';
pg_close($conn);
Alternatively, you can use the UPDATE statement in the pg_query function :
<?php
//...
// pg_update($conn, 'user_table', $data, $condition);
pg_query("UPDATE user_table
SET name='$name', email='$email'
WHERE id=$id");
Updating record with pg_query()
<?php
$user = 'postgres';
$pass = 'password';
$db = 'brainbell';
$conn = pg_connect("dbname=$db user=$user password=$pass");
$name = pg_escape_string( $_POST['name'] );
$email = pg_escape_string( $_POST['email'] );
$id = 21;
pg_query("UPDATE user_table
SET name='$name', email='$email'
WHERE id=$id");
echo 'Record updated.';
pg_close($conn);
Retrieving Results of a Query to PostgreSQL
The return value of a call to pg_query() is a pointer to a resultset that can be used with these functions:
- pg_fetch_assoc() returns the current row in the resultset as an associative array
- pg_fetch_object() returns the current row in the resultset as an object
- pg_fetch_row() returns the current row in the resultset as a numeric array
- pg_fetch_all() returns the complete resultset as an array of associative arrays
Retrieving Data from PostgreSQL
<?php
$user = 'postgres';
$pass = 'password';
$db = 'brainbell';
$conn = pg_connect("dbname=$db user=$user password=$pass");
$q = pg_query($conn, "SELECT * FROM user_table LIMIT 5");
while ($row = pg_fetch_row($q)) {
echo $row[0]. ', '.$row[1]. ', '. $row[2].'<br>';
}
pg_close($conn);
The code uses pg_fetch_row() to read out all data from the user_table table.
Alternatively, pg_select() works similarly to pg_insert() and pg_update(). Just provide a database handle, a table name, and maybe a WHERE clause in the form of an array, and you get the complete resultset as an array of (associative) arrays.
<?php
$user = 'postgres';
$pass = 'password';
$db = 'brainbell';
$conn = pg_connect("dbname=$db user=$user password=$pass");
$condition['id'] = 21;
$rows = pg_select($conn, 'user_table', $condition);
foreach ($rows as $row) {
echo $row['name'].', '.$row['email'].'<br>';
}
pg_close($conn);
The preceding code can be written with pg_query and SQL SELECT statement, see the following code:
<?php
//...
$q = pg_query($conn, 'SELECT * FROM user_table WHERE id=21');
while ($row = pg_fetch_assoc($q)) {
echo $row['name'].', '.$row['email'].'<br>';
}
//...
Working with Databases: