PostgreSQL

PostgreSQL is the advanced, open-source object-relational database management system (ORDBMS) built for extensibility and on standards-compliance. PostgreSQL provides complete support for reliable transactions, i.e. Atomicity, Consistency, Isolation, Durability (ACID) and concurrency is achieved with the help of Multiversion Concurrency Control (MVCC).

Advantages of PostgreSQL:

  • An open source SQL standard compliant RDBMS
  • Extensible
  • Reliability and data integrity
  • Variety of data types like HStore, JSON,.. etc

CRUD Operations

Any software or a web application will typically do these set of operations called C.R.U.D.
CRUD Stands for
  • Create (Insert)
  • Read (Select)
  • Update
  • Delete

Lets do insert, select, update and delete operations on Postgres Database using PHP.

Connect to Postgres database

First we need to connect to the postgres database before doing any operation on database. Connecting to the postgres database requires its Database host name, Port, Database name, Database username and Password. The pg_connect() function in php helps us to connect to postgres database in php.

<?php 

$host = "localhost";
$port = "5432";
$dbname = "dummy";
$user = "postgres";
$password = "pass";
$pg_options = "--client_encoding=UTF8";

$connection_string = "host={$host} port={$port} dbname={$dbname} user={$user} password={$password} options='{$pg_options}'";
$dbconn = pg_connect($connection_string);


if($dbconn){
    echo "Connected to ". pg_host($dbconn); 
}else{
    echo "Error in connecting to database.";
}

echo "<br />";

Create a Table in Postgres

To create a table in postgres database using php, we need to run the create table sql query using pg_query() function. Or you can do it via postgres commandline.

<?php

require_once 'db-connect.php';


$sql = "CREATE TABLE USERS
      (ID       SERIAL PRIMARY KEY,
      NAME      TEXT    NOT NULL,
      AGE       INT     NOT NULL,
      COUNTRY   TEXT     NOT NULL
      )";

$result = pg_query($dbconn, $sql);
if(!$result){
  echo pg_last_error($dbconn);
} else {
  echo "Table created successfully";
}

// Close the connection
pg_close($dbconn);

Insert records with pg_insert()

pg_insert() function takes an associative array of column & their values and does the insert operation. Always sanitize user inputs before running sql query on it with the help of functions like pg_escape_string() or some others ways to sanitize inputs.

<?php 

require_once 'db-connect.php';


$user1 = array(
        'name' => "Alex", 
        'age' => "24", 
        'country' => "INDIA" 
        );

$user2 = array(
        'name' => "Ben", 
        'age' => 30, 
        'country' => "USA" 
        );

$user3 = array(
        'name' => "Dennis", 
        'age' => 28, 
        'country' => "USA"
        );

$users = array(
        $user1,
        $user2,
        $user3
        );

// Insert one by one
foreach ($users as $key => $user) {

    $res = pg_insert($dbconn, 'users' , $user);
    if ($res) {
      echo "Inserted user: ".$user['name']." <br />";
    } else {
      echo pg_last_error($dbconn) . " <br />";
    }

}

// Close the connection
pg_close($dbconn);

Insert records using SQL with pg_query()

we can also use pg_query() to insert records without the help of pg_insert() but always use pg_escape_string() or any other method to sanitize user inputs.


require_once 'db-connect.php';

$sql = "insert into users (name, age, country)  values('Dark Night', 30, 'Gotham')";

$result = pg_query($dbconn, $sql);

if(!$result){
  echo pg_last_error($dbconn);
} else {
  echo "Updated successfully";
}

// Close the connection
pg_close($dbconn);

Select records from postgres database

Using pg_fetch_all()


<?php 

require_once 'index.php';

$result = pg_query($dbconn, "SELECT * FROM users");
if (!$result) {
    echo "An error occurred.\n";
    exit;
}

$arr = pg_fetch_all($result);

echo "<pre>";
print_r($arr);
echo "</pre>";

Using pg_fetch_array()


require_once 'index.php';

$result = pg_query($dbconn, "SELECT * FROM users");
if (!$result) {
    echo "An error occurred.\n";
    exit;
}



echo "Fetch array: <br>";

$arr = pg_fetch_array($result);
// $arr = pg_fetch_array($result,7,PGSQL_NUM);  //Start index 
// $arr = pg_fetch_array($result,NULL, PGSQL_ASSOC); // Fetch Associative array


echo "<pre>";
print_r($arr);
echo "</pre>";


Using pg_fetch_row()


require_once 'index.php';

$result = pg_query($dbconn, "SELECT * FROM users");
if (!$result) {
    echo "An error occurred.\n";
    exit;
}


echo "Fetch row: <br>";


while ($row = pg_fetch_row($result)) {
 print_r($row[1]);
}

Update record with pg_update()

To update a record we can user pg_update() function which takes the where condition and data to update a particular record. The below code is similar to the SQL Query
Update users set name=‘Alexander’ where name = ‘Alex’;

<?php 

require_once 'db-connect.php';

$where_condition = array('name'=>'Alex');

$data = array("name" => "Alexander");


$res = pg_update($dbconn, 'users', $data, $where_condition);
if ($res) {
  echo "Data is updated: $res";
} else {
  echo "error in input.. <br />";
  echo pg_last_error($dbconn);
}

// Close the connection
pg_close($dbconn);

Update record with pg_query()

Alternatively we can use pg_query() to run a sql query that updates a particular record without using pg_update().

<?php 

require_once 'db-connect.php';


$sql = "update users set name ='Alex' where name = 'Alexander'";

$result = pg_query($dbconn, $sql);
if(!$result){
  echo pg_last_error($dbconn);
} else {
  echo "Updated successfully.";
}

// Close the connection
pg_close($dbconn);

Delete record with pg_delete()

To delete a record we can user pg_delete() function which takes the where condition to delete a particular record. The below code is similar to the SQL Query
Delete from users where name=‘Dennis’;

<?php

require_once 'db-connect.php';

$where = array("name" => "Dennis");

$res = pg_delete($dbconn, 'users', $where);
if ($res) {
  echo "Deleted successfully.";
} else {
  echo "Error in input..";
}

// Close the connection
pg_close($dbconn);

Delete record using SQL with pg_query()

Alternatively we can use pg_query() to run a sql query that deltes a particular record without using pg_delete().

<?php

require_once 'db-connect.php';


$sql = "delete from users where name = 'Dark Night'";

 $result = pg_query($dbconn, $sql);
if(!$result){
  echo pg_last_error($dbconn);
} else {
  echo "Deleted successfully\n";
}


// Close the connection
pg_close($dbconn);