database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
ㆍPostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
PostgreSQL Tutorials 3782 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 3782
Simple Connection to PostgreSQL Using PHP
작성자
정재익(advance)
작성일
2001-12-25 21:25
조회수
8,325
"; // format results by row $i = 0; while ($i < $num) { $row = pg_fetch_array($sql_result, $i); $coffee_name = $row["COFFEE_NAME"]; $roast_type = $row["ROAST_TYPE"]; $quantity = $row["QUANTITY"]; echo " "; $i++; } echo "
첨부파일: tb_pgsql.pdf (107,058bytes)

Simple Connection to PostgreSQL

 

Like MySQL, the PostgreSQL database is quite popular among PHP developers. Understandably, there are numerous well-documented PHP functions you can use in conjunction with PostgreSQL; see the PHP Manual for a complete list. However, you only need a few of these functions in order to make a simple connection and select some data:

 

pg_connect - opens a connection to PostgreSQL; requires a hostname, database name, username and password.

pg_exec - executes the SQL statement.

pg_numrows - returns the number of rows in a result.

pg_fetch_array - puts a SQL statement result row in an array.

pg_freeresult - frees the resources in use by the current connection.

pg_close - closes the current connection.

 

Just for argument's sake, let's pretend that a PostgreSQL database is already installed on your system, for which you have a valid username and password. Let's also assume that you've created a table on that database, called COFFEE_INVENTORY. The COFFEE_INVENTORY table has three columns: COFFEE_NAME, ROAST_TYPE and QUANTITY.

+-------------------+-------------+-----------+
|  COFFEE_NAME      | ROAST_TYPE  | QUANTITY  |
+-------------------+-------------+-----------+
|  French Roast     | dark        | 18        |
|  Kenya            | medium      | 6         |
|  Ethiopian Harrar | medium      | 35        |
|  Sumatra          | dark        | 8         |
|  Columbian        | light       | 12        |
+-------------------+-------------+-----------+

Now, let's do some PHP. Before you begin, you must know the name of the server on which the database resides, and have a valid username and password. Then, start your PHP code by creating a connection variable:

 


The die() function is used to stop the script and print a given error message, should the previous function fail (in this case, if a connection was not made.)

 

Up to this point, you've told PHP to connect to a server and select a database. If you've made it this far, you can issue a SQL statement and hopefully see some results! Using the COFFEE_INVENTORY table, suppose you want to view your inventory, including the name of the coffee and the roast type, with the highest number of bags listed first. Create a variable that holds your SQL statement:

$sql = "SELECT COFFEE_NAME, ROAST_TYPE, QUANTITY
 	FROM COFFEE_INVENTORY
	ORDER BY QUANTITY DESC";

Next, create a variable to hold the result of the query, carried out by the pg_exec function. The pg_exec function takes two arguments: the connection and SQL statement variables you've just created.

$sql_result = pg_exec($connection,$sql)
	or die("Couldn't execute query.");

Get the number of rows in the result set, using the pg_numrows function:

 

$num = pg_numrows($sql_result);

 

To format the results currently held in $sql_result, first separate the results by row, using the pg_fetch_array function inside a while loop:

$i = 0;

while ($i < $num) {
	$row = pg_fetch_array($sql_result, $i);

		// more code here...	
	
	$i++;
}

The while loop will create an array called $row for each record in the result set. To get the individual elements of the record (COFFEE_NAME, ROAST_TYPE, QUANTITY), create specific variables:

$coffee_name = $row["COFFEE_NAME"];
$roast_type = $row["ROAST_TYPE"];
$quantity = $row["QUANTITY"];

You'll probably want to print the results in a simple HTML table. Step back and place this statement before the while loop begins, to open the table tag and create the row headings:

echo "";
echo "
      ";

After defining the variables within the while loop, print them in table format:

echo "
";

The new while loop now looks like this:

while ($i < $num) {
	$row = pg_fetch_array($sql_result, $i);
	$coffee_name = $row["COFFEE_NAME"];
	$roast_type = $row["ROAST_TYPE"];
	$quantity = $row["QUANTITY"];
	echo "
"; } After the while loops, close the HTML table: echo "
Coffee Name Roast Type Quantity
$coffee_name $roast_type $quantity
$coffee_name $roast_type $quantity
";

 

Finally, you'll want to free up the resources used to perform the query, and close the database connection. Failing to do so could cause memory leaks and other nasty resource-hogging things to occur.

 

 
pg_freeresult($sql_result);
pg_close($connection);

?>

The full script to perform a simple connection and data selection from a PostgreSQL database could look something like this:

 

";
echo "
Coffee Name Roast Type Quantity
$coffee_name $roast_type $quantity
"; // free resources and close connection pg_freeresult($sql_result); pg_close($connection); ?>

 

Please see the PHP Manual for additional PostgreSQL functions, and try using your own tables and SQL statements instead of the examples above.

 

원본출처 : http://www.thickbook.com/extra/php_pgsql.phtml

[Top]
No.
제목
작성자
작성일
조회
3787Combining Perl and PostgreSQL - Part 1
정재익
2001-12-26
4748
3785Beginning Database with PostgreSQL
정재익
2001-12-25
163137
3783Migrate your site from MySQL to PostgreSQL
정재익
2001-12-25
6513
3782Simple Connection to PostgreSQL Using PHP
정재익
2001-12-25
8325
3775AuthPG: 아파치-포스트그레스 인증 모듈 [1]
정재익
2001-12-25
4658
3773PostgreSQL의 JDBC 활용하기
정재익
2001-12-25
6651
3623AGGREGATE funciton 함수 이용시 간단한 tip
정재익
2001-11-07
5836
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.051초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다