Php pdo and postgresql

Connecting to PostgreSQL

Summary: in this tutorial, you will learn how to connect to a PostgreSQL database server using PHP PDO.

Prerequisites

To make a connection to the PostgreSQL database server using PHP PDO, you need to have:

  • A PostgreSQL database server, a database, and an account with a username and password that can access the database.
  • PHP PDO PostgreSQL driver enabled in the php.ini file.

1) PostgreSQL database parameters

Suppose you have the following PostgreSQL database parameters:

  • A PostgreSQL database server on the localhost .
  • The dvdrental sample database.
  • The account with the user postgres and password postgres that can access the dvdrental database on the local database server

The following creates a new database configuration file named config.php that stores the PostgreSQL database parameters:

To use these parameters, you include the config.php file in the script that connects to the PostgreSQL using the require construct.

2) Enable PostgreSQL driver

The PDO_PGSQL is a driver that implements the PDO interface. It allows you to access PostgreSQL databases from PHP.

To check if the PDO_PGSQL driver is enabled, you open the php.ini file. Typically, the php.ini file is located under the php directory. For example, if you use XAMPP on Windows, you can find the php.ini file under the C:\xampp\php directory.

in the php.ini file, you can find the following line:

If you see the comma( ; ) placed at the beginning of the line, it means that the line is commented and the database driver is not enabled.

To enable the driver, you need to uncomment the line by removing the comma ( ; ) like this:

After that, you need to restart the web server to apply the change.

PostgreSQL data source name

The data source name or DSN holds database parameters that enable access to a database system. The data source name of the PostgreSQL consists of the following parameters:

  • pgsql: is the DNS prefix.
  • host: the host of the server where the PostgreSQL runs.
  • port: the port to which PostgreSQL listens. The default PostgreSQL’s port is 5432 .
  • dbname: the database name that you want to connect to.
  • And other parameters.

The following shows a DSN that connects to dvdrental database in the local PostgreSQL Server, port 5432:

Connecting to PostgreSQL

The following illustrates how to connect to the dvdrental database in PostgreSQL database server:

How the script works.

  • First, make a new connection to the PostgreSQL database by creating a new instance of the PDO class.
  • Second, show a message if the database connection is established successfully; otherwise, show an error message.

The following option instruct PDO to set an error code and throw an exception if an error occurs:

PDO has three exception handling options:

  • PDO::ERROR_SILENT – PDO sets an error code for inspecting using the PDO::errorCode() and PDO::errorInfo() methods. The PDO::ERROR_SILENT is the default mode.
  • PDO::ERRMODE_WARNING – Besides setting the error code, PDO will issue an E_WARNING message.
  • PDO::ERRMODE_EXCEPTION – Besides setting the error code, PDO will raise a PDOException .
Читайте также:  Комп виден принтер нет

Note that PHP automatically closes the database connection when the script ends. If you want to close the database connection explicitly, you can set the PDO instance to null:

Источник

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:

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

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:

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.

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.

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

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

  • 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.

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:

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

Читайте также:  Wordpress contact form 7 обработчик

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.

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

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.

Источник

PostgreSQL PHP: Querying Data

Summary: in this tutorial, you will learn to query data from the PostgreSQL database in PHP using PDO.

Querying all rows in a table

To query all rows from a table in the PostgreSQL database, you use the following steps:

  1. First, connect to the PostgreSQL database by creating a new PDO object.
  2. Second, call the query() method of the PDO object. The query() method accepts a SELECT statement as the argument. The query method returns a PDOStatement object.
  3. Third, fetch the next rows from the result by calling the fetch() method of the PDOstatement object. The fetch_style argument of the fetch() method controls how the result returned. For example, the PDO::FETCH_ASSOC instructs the fetch() method to return the result set as an array indexed by column name.

We will use the stocks table created in the creating table tutorial for the demonstration. Let’s create a new class StockDB for storing all the methods that select data from the stocks table.

The following all() method selects all rows in the stocks table.

To test the all() method, we use the following code in the index.php file.

The first part of the index.php is to connect to the PostgreSQL database and get all rows in the stocks table. The second part shows the data in HTML format.

The following screenshot illustrates the output of the index.php file.

Querying a specific row in a table

To query a specific row in a table, you use the following steps:

  1. First, connect to the PostgreSQL database by creating a new PDO object.
  2. Next, prepare the SELECT statement for execution by calling the prepare() method of the PDO object. The prepare() method returns an instance of the PDOStatement class.
  3. Then, bind the values to the statement by using the bindValue() method.
  4. After that, execute the SELECT statement by calling the execute() method of the PDOStatement object.
  5. Finally, fetch the next row in the result using the fetch() method. If the SELECT statement returns 1 row, you can use the fetchObject() method to return an object.

The following findByPK() method selects a row in the stocks table based on a specified id and returns a Stock object.

To test the findByPK() method, we create a new PHP file named stock.php .

Run the stock.php file, we get the following result.

In this tutorial, you have learned various ways to query data from the tables in the PostgreSQL database using PHP PDO.

Источник

How to connect to PostgreSQL using PHP

This article describes two methods for connecting to a PostgreSQL database using PHP:

  • PostgreSQL native functions
  • PDO (PHP Data Objects)

Connecting to PostgreSQL using native functions

PHP provides many functions for working directly with PostgreSQL databases.

To connect to PostgreSQL using native functions, follow these steps:

Читайте также:  Как выгрузить модуль ядра linux

    Use the following PHP code to connect to PostgreSQL and select a database. Replace username with your username, password with your password, and dbname with the database name:

After the code connects to PostgreSQL and selects the database, you can run SQL queries and perform other operations. For example, the following PHP code runs a SQL query that extracts the last names from the employees table, and stores the result in the $result variable:

Connecting to PostgreSQL using PDO (PHP Data Objects)

The PostgreSQL functions in the previous procedure can only be used with PostgreSQL databases. PDO abstracts database access, and enables you to use code that can handle different types of databases.

To connect to PostgreSQL using PDO, follow these steps:

    Use the following PHP code to connect to PostgreSQL and select a database. Replace username with your username, password with your password, and dbname with the database name:

After the code connects to PostgreSQL and selects the database, you can run SQL queries and perform other operations. For example, the following PHP code runs a SQL query that extracts the last names from the employees table, and stores the result in the $result variable:

Источник

PostgreSQL PHP

This PostgreSQL PHP section shows you how to interact with the PostgreSQL database using PHP Data Objects (PDO) API. It teaches you the steps of performing the common database operations in PHP such as creating new tables, inserting data, updating data, querying data, deleting data, using transactions, calling stored procedures, and working with binary large objects.

PHP is one of the most popular scripting languages for developing web applications and websites. PHP is fast, flexible, and easy to learn. PHP powers everything from a personal blog to the most popular websites in the world.

The PHP Data Objects (PDO) defines a unified interface for accessing the relational databases in PHP. Each database defines the database-specific driver that implements the PDO interface. Also, each driver can expose the database-specific features as regular extension functions.

Most PHP distributions have the PDO_PGSQL driver that allows you to interact with PostgreSQL databases via PDO API.

  • Connecting to a PostgreSQL database – shows you how to setup a simple PHP application structure and connect to a PostgreSQL database.
  • Creating new PostgreSQL database tables – walks you through the steps of creating database tables in PostgreSQL using PHP.
  • Inserting data into PostgresQL tables – guides you how to use insert data into a table using PHP PDO.
  • Updating data in the table – provides you with the steps of updating data in the database tables.
  • Querying data from a table – shows you various ways to query data in the PostgreSQL database from PHP.
  • Performing transactions – explains the transaction concept and shows you how to perform transactions in PHP.
  • Working with the binary large objects (BLOB) – shows you how to insert, select, and delete the large objects in the PostgreSQL using PHP.
  • Calling PostgreSQL stored procedures – explains you the steps of calling PostgreSQL stored procedures from PHP.
  • Deleting data in a PostgreSQL table using PHP PDO – teaches you how to delete data from the PostgreSQL table in the PHP application using PDO.

Источник

Поделиться с друзьями
КомпСовет
Adblock
detector