Php postgresql pdo connect

How to connect a PostgreSQL database server using PHP PDO?

Before connecting with the PostgreSQL database using PHP PDO, make sure PHP PDO PostgreSQL driver enabled.

To check if the PDO PostgreSQL driver is enabled, open the php.ini file and check if the following line is un-commented. If it is not, you can remove the semicolon ( ; ) in front of the entry.

extension=php_pdo_pgsql.dll

PostgreSQL data source name

The data source name or DSN passes on the database parameters that permit you to connect with the database. PDO characterizes distinctive DSN for different databases. The data source name of the PostgreSQL is made out of the following parameters:

1. DNS prefix: pgsql:
2. host: the database server’s hostname where the PostgreSQL database locates.
3. port: default port is 5432.
4. dbname: database name.
5. user: The name of the user that connects to the database dbname .
6. password: The password of the user name.

Notice that PDO ignores the username and password in the PDO constructor if you put them in the data source name (DSN).

The following snippet allows you to connect to the database in the PostgreSQL database server:

$db database successfully!"; > > catch (PDOException $e)< // report error message echo $e->getMessage(); >

Источник

PostgreSQL Functions (PDO_PGSQL)

PDO_PGSQL is a driver that implements the PHP Data Objects (PDO) interface to enable access from PHP to PostgreSQL databases.

Resource Types

This extension defines a stream resource returned by PDO::pgsqlLOBOpen() .

Installation

Use —with-pdo-pgsql[=DIR] to install the PDO PostgreSQL extension, where the optional [=DIR] is the PostgreSQL base install directory, or the path to pg_config.

Predefined Constants

The constants below are defined by this driver, and will only be available when the extension has been either compiled into PHP or dynamically loaded at runtime. In addition, these driver-specific constants should only be used if you are using this driver. Using driver-specific attributes with another driver may result in unexpected behaviour. PDO::getAttribute() may be used to obtain the PDO::ATTR_DRIVER_NAME attribute to check the driver, if your code can run against multiple drivers.

PDO::PGSQL_ATTR_DISABLE_PREPARES ( int )

Send the query and the parameters to the server together in a single call, avoiding the need to create a named prepared statement separately. If the query is only going to be executed once this can reduce latency by avoiding an unnecessary server round-trip.

General notes

Note:

bytea fields are returned as streams.

Table of Contents

• PDO_PGSQL DSN — Connecting to PostgreSQL databases
• PDO::pgsqlCopyFromArray — Copy data from PHP array into table
• PDO::pgsqlCopyFromFile — Copy data from file into table
• PDO::pgsqlCopyToArray — Copy data from database table into PHP array
• PDO::pgsqlCopyToFile — Copy data from table into file
• PDO::pgsqlGetNotify — Get asynchronous notification
• PDO::pgsqlGetPid — Get the server PID
• PDO::pgsqlLOBCreate — Creates a new large object
• PDO::pgsqlLOBOpen — Opens an existing large object stream
• PDO::pgsqlLOBUnlink — Deletes the large object

Источник

PostgreSQL PHP: Connect to PostgreSQL Database Using PDO

Summary: in this tutorial, you will learn how to setup a simple project structure and connect to the PostgreSQL database using PHP PDO API.

Enable PDO_PGSQL driver

Most PHP distributions include the PostgreSQL extension PDO_PGSQL by default so you don’t need to do any further configuration in PHP.

However, if this is not the case, you can enable the extension by editing the php.ini file to uncomment the following line:

;extension=php_pdo_pgsql.dllCode language: Shell Session (shell)

To uncomment the line, you remove the semicolon (;) at the beginning of the line and restart the webserver.

extension=php_pdo_pgsql.dllCode language: SQL (Structured Query Language) (sql)

Create a PHP project structure with Composer

The Composer is a tool for managing dependency that allows you to declare the PHP library in a project and manage the update automatically.

We will use the Composer to set up the project structure of all the projects that we will be working on.

First, create the postgresqlphpconnect folder in the webroot folder to store the project files.

Next, create the app folder and a new composer.json file in the postgresqlphpconnect folder with the following content:

 < "autoload": < "psr-4": < "PostgreSQLTutorial\\": "app/" >> >Code language: SQL (Structured Query Language) (sql)

It means that every class that you create in the app folder will map to the PostgreSQLTutorial namespace.

Then, go to the window terminal, navigate to the postgresqlphpconnect folder, and type the following command:

This command instructs the Composer to download the declared libraries in the composer.json file and generate an autoload file.

The command will also place all third-party libraries in the newly created vendor folder. Because we didn’t declare any library in the composer.json file, it just generates the autoload file.

Loading composer repositories with package information Updating dependencies (including require-dev) Nothing to install or update Generating autoload filesCode language: JavaScript (javascript)

After that, create the index.php file in the postgresqlphpconnect folder.

Finally, create two more files in the app folder: Connection.php and database.ini .

The project structure will look like the following picture:

Connect to the PostgreSQL database

First, create a new database named stocks for the demonstration.

CREATE DATABASE stocks;Code language: SQL (Structured Query Language) (sql)

Next, use the database.ini file to store the PostgreSQL database parameters as follows:

host=localhost port=5432 database=stocks user=postgres password=postgresCode language: SQL (Structured Query Language) (sql)

Then, create a new class called Connection in the Connection.php file.

 namespace PostgreSQLTutorial; /** * Represent the Connection */ class Connection < /** * Connection * @var type */ private static $conn; /** * Connect to the database and return an instance of \PDO object * @return \PDO * @throws \Exception */ public function connect() < // read parameters in the ini configuration file $params = parse_ini_file('database.ini'); if ($params === false) < throw new \Exception("Error reading database configuration file"); > // connect to the postgresql database $conStr = sprintf("pgsql:host=%s;port=%d;dbname=%s;user=%s;password=%s", $params['host'], $params['port'], $params['database'], $params['user'], $params['password']); $pdo = new \PDO($conStr); $pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION); return $pdo; > /** * return an instance of the Connection object * @return type */ public static function get() < if (null === static::$conn) < static::$conn = new static(); > return static::$conn; > protected function __construct()  < >private function __clone()  < >private function __wakeup()  < >>Code language: PHP (php)

• The Connection class is a singleton class. It means that you can create only one instance for the class. If the instance already exists and you try to create a new one, the class will return the existing reference.
• To connect to a PostgreSQL database, you need to create a new instance of the PDO class. In the connect() method, we read the database configuration parameters in the database.ini file, construct a connection string and pass it to the PDO constructor.

After that, place the following code in the index.php file.

 require 'vendor/autoload.php'; use PostgreSQLTutorial\Connection as Connection; try < Connection::get()->connect(); echo 'A connection to the PostgreSQL database sever has been established successfully.'; > catch (\PDOException $e) < echo $e->getMessage(); >Code language: PHP (php)

PHP throws a \PDOException if there is an exception occurs when connecting to the PostgreSQL database server, therefore, you need to place the code of creating a new PDO object inside the try. catch block to handle the exception.

Run the following composer command to update the autoload files:

>composer dump-autoload -o Generating optimized autoload filesCode language: SQL (Structured Query Language) (sql)

Finally, launch the index.php file from the web browser to test it.

A connection to the PostgreSQL database sever has been established successfully.

If you want to see the exception that may occur, you can change the parameters in the database.ini file to an invalid one and test it.

The following is the error message when the password is invalid.

SQLSTATE[08006] [7] FATAL: password authentication failed for user "postgres"Code language: CSS (css)

And the following is the error message when the database is invalid.

SQLSTATE[08006] [7] FATAL: database "stockss" does not existCode language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to connect to the PostgreSQL database from a PHP application using the PDO API. We will reuse the Connection class in the subsequent tutorials.

Источник