Create table php postgres

Содержание

PostgreSQL with php — a thorough introduction
PostgreSQL PHP: Create New Tables
Creating new tables using PHP PDO steps
Creating new table example
Summary

PostgreSQL with php — a thorough introduction

To install PostgreSQL on Linux and Windows we have discussed here, detail installation process.

You can use Apache Web server, PHP, PostgreSQL and phpPgAdmin (which is an excellent tool to manage PHP-PostgreSQL projects) to build a basic web application.

Connect to PostgreSQL using PHP native functions

Assume we have the following table (book) under «postgres» database. Here is the structure of the table :

column_name | is_nullable | data_type | character_maximum_length | numeric_precision | numeric_scale ---------------------+-------------+-------------------+--------------------------+-------------------+--------------- book_id | NO | character varying | 10 | | author | YES | character varying | 25 | | publisher | YES | character varying | 25 | | date_of_publication | YES | date | | | price | YES | numeric | | 8 | 2

PHP provides many functions for working directly with PostgreSQL databases. Here are some functions :

pg_connect : The function is used to open a PostgreSQL connection.

resource pg_connect ( string $connection_string [, int $connect_type ] )

connection_string: The connection_string can be empty to use all default parameters, or it can contain one or more parameter settings separated by whitespace. The currently recognized parameter keywords are: host, hostaddr, port, dbname (defaults to value of user), user, password, connect_timeout, options, tty (ignored), sslmode, requiressl (deprecated in favor of sslmode), and service. Which of these arguments exist depends on your PostgreSQL version.
connect_type : If PGSQL_CONNECT_FORCE_NEW is passed, then a new connection is created, even if the connection_string is identical to an existing connection. If PGSQL_CONNECT_ASYNC is given, then the connection is established asynchronously. The state of the connection can then be checked via pg_connect_poll() or pg_connection_status().

Return Value: PostgreSQL connection resource on success, FALSE on failure.

pg_query: Execute a query

resource pg_query ([ resource $connection ], string $query )

connection : PostgreSQL database connection resource. When connection is not present, the default connection is used. The default connection is the last connection made by pg_connect() or pg_pconnect().
query : The SQL statement or statements to be executed.

Return Value: A query result resource on success or FALSE on failure.

Insert data into table with PHP

We will create a an HTML form and a PHP script to insert data into the «book» table. Here is the code (file name insert.php) :

Here is how the form to insert data looks :

Use this form to enter some data into the «book» table.

Retrieving and updating data with PHP

In the next step, we will create a form so that we can see the detail of book records and update the existing data of the «book» table. For this, we will create a form where the user can supply the book id and it will show all the information stored in the database regarding that particular book. So, you will learn how to fetch data from the table, how to display that data with PHP and how to update the data.

Here is the code (file name enter-bookid):

Connect to PostgreSQL using PDO (PHP Data Objects)

As of version 5.1 PHP provides new database connection abstraction library, PHP Data Objects or PDO. PDO abstracts database access, and enables you to use code that can handle different types of databases.

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

Handling connection errors:

If there is any connection errors, a PDOException object will be thrown. You may catch the exception if you want to handle the error condition, or you can leave it to global exception handler which can be set up via set_exception_handler().

Here is an example to handle errors:

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 first name, last name and country (order by country) from the user_details table, and stores the result in $sql.

$dbuser = 'postgres'; $dbpass = 'abc123'; $dbhost = 'localhost'; $dbname='postgres'; 
$connec = new PDO("pgsql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass); >catch (PDOException $e) < echo "Error : " . $e->getMessage() . "
"; die(); > $sql = 'SELECT fname, lname, country FROM user_details ORDER BY country'; foreach ($connec->query($sql) as $row) < print $row['fname'] . " "; print $row['lname'] . "-->"; print $row['country'] . "
"; > ?>

So you have learned how to insert data from an HTML form and how to fetch, display and update data to PostgreSQL with PHP.

Previous: Connect to PostgreSQL
Next: Data Types

Weekly Trends
Java Basic Programming Exercises
SQL Subqueries
Adventureworks Database Exercises
C# Sharp Basic Exercises
SQL COUNT() with distinct
JavaScript String Exercises
JavaScript HTML Form Validation
Java Collection Exercises
SQL COUNT() function
SQL Inner Join
JavaScript functions Exercises
Python Tutorial
Python Array Exercises
SQL Cross Join
C# Sharp Array Exercises

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook

Источник

PostgreSQL PHP: Create New Tables

Summary: in this tutorial, you will learn how to create new tables in the PostgreSQL database using PHP PDO API.

Creating new tables using PHP PDO steps

To create new tables in a PostgreSQL database using PHP PDO, you follow these steps:

First, connect to the database by creating a new PDO object.
Second, call the exec() method of the PDO object to execute the CREATE TABLE statement.

Let’s look at an example of creating new tables.

Creating new table example

In the previous tutorial, we created the stocks database in the PostgreSQL database server.

For the demonstration, we’ll create two new tables in the stocks database: stocks and stock_evaluations with the following structures:

CREATE TABLE IF NOT EXISTS stocks ( id SERIAL PRIMARY KEY, symbol CHARACTER VARYING(10) NOT NULL UNIQUE, company CHARACTER VARYING(255) NOT NULL UNIQUE );Code language: SQL (Structured Query Language) (sql)

CREATE TABLE IF NOT EXISTS stock_valuations ( stock_id INTEGER NOT NULL, value_on DATE NOT NULL, price NUMERIC(8 , 2 ) NOT NULL DEFAULT 0, PRIMARY KEY (stock_id , value_on), FOREIGN KEY (stock_id) REFERENCES stocks (id) );Code language: SQL (Structured Query Language) (sql)

We create a new class named PostgreSQLCreateTable in the app folder.

 namespace PostgreSQLTutorial; /** * Create table in PostgreSQL from PHP demo */ class PostgreSQLCreateTable < /** * PDO object * @var \PDO */ private $pdo; /** * init the object with a \PDO object * @param type $pdo */ public function __construct($pdo) < $this->pdo = $pdo; > /** * create tables */ public function createTables() < $sqlList = ['CREATE TABLE IF NOT EXISTS stocks ( id serial PRIMARY KEY, symbol character varying(10) NOT NULL UNIQUE, company character varying(255) NOT NULL UNIQUE );', 'CREATE TABLE IF NOT EXISTS stock_valuations ( stock_id INTEGER NOT NULL, value_on date NOT NULL, price numeric(8,2) NOT NULL DEFAULT 0, PRIMARY KEY (stock_id, value_on), FOREIGN KEY (stock_id) REFERENCES stocks(id) );']; // execute each sql statement to create new tables foreach ($sqlList as $sql) < $this->pdo->exec($sql); > return $this; > /** * return tables in the database */ public function getTables() < $stmt = $this->pdo->query("SELECT table_name FROM information_schema.tables WHERE table_schema= 'public' AND table_type='BASE TABLE' ORDER BY table_name"); $tableList = []; while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) < $tableList[] = $row['table_name']; > return $tableList; > > Code language: PHP (php)

First, the constructor of the class accepts a PDO object as the argument.
Second, the createTables() method creates new tables in the database. The $sqlList array holds all the CREATE TABLE statements. To execute a statement, you call the exec() method of the PDO object. We iterate over the array of SQL statements and execute them one by one by calling the exec() method.
Third, the getTables() method returns all tables in the connected database. We use it to query the tables in the stocks database after calling the createTables() method.

In the index.php file, connect to the PostgreSQL database execute the statement to create tables and query tables.

 require 'vendor/autoload.php'; use PostgreSQLTutorial\Connection as Connection; use PostgreSQLTutorial\PostgreSQLCreateTable as PostgreSQLCreateTable; try < // connect to the PostgreSQL database $pdo = Connection::get()->connect(); // $tableCreator = new PostgreSQLCreateTable($pdo); // create tables and query the table from the // database $tables = $tableCreator->createTables() ->getTables(); foreach ($tables as $table)< echo $table . '
'; > > catch (\PDOException $e) < echo $e->getMessage(); > Code language: PHP (php)

Launch the index.php file in a web browser. You’ll see the following output:

stock_valuations stocksCode language: SQL (Structured Query Language) (sql)

The output shows that the script has created two tables successfully.

Summary

Use the CREATE TABLE statement to create a new table.
Use the PDO exec() method to to execute a CREATE TABLE statement to create a new table in the datatabase.

Источник