Feng’ Blog

PHP连接PostgreSQL(增删改查)
在操作之前,先确定自己搭建的php能否正常链接postgresqlPHP链接PgSql文档:https://sxf...
扫描右侧二维码阅读全文
06
2018/03

PHP连接PostgreSQL(增删改查)

在操作之前,先确定自己搭建的php能否正常链接postgresql

PHP链接PgSql文档:https://sxfblog.com/archives/1316

PHP接口API地址:http://php.net/manual/zh/book.pgsql.php
php544115641685156.png


创建表

下面的PHP程序将使用以前创建的数据库中创建一个表:

  1. <?php
  2. $host = "host=127.0.0.1";
  3. $port = "port=5432";
  4. $dbname = "dbname=testdb";
  5. $credentials = "user=postgres password=pass123";
  6. $db = pg_connect( "$host $port $dbname $credentials" );
  7. if(!$db){
  8. ​ echo "Error : Unable to open databasen";
  9. } else {
  10. ​ echo "Opened database successfullyn";
  11. }
  12. $sql =<<<EOF
  13. ​ CREATE TABLE COMPANY
  14. ​ (ID INT PRIMARY KEY NOT NULL,
  15. ​ NAME TEXT NOT NULL,
  16. ​ AGE INT NOT NULL,
  17. ​ ADDRESS CHAR(50),
  18. ​ SALARY REAL);
  19. EOF;
  20. $ret = pg_query($db, $sql);
  21. if(!$ret){
  22. ​ echo pg_last_error($db);
  23. } else {
  24. ​ echo "Table created successfullyn";
  25. }
  26. pg_close($db);
  27. ?>

上述程序执行时,它会创建表COMPANY在数据库testdb 中 ,它会显示以下消息:

Opened database successfully

Table created successfully

INSERT操作

postgresql123213123213.png

PHP程序,显示我们如何创建表COMPANY 在上面的例子中,创建表中的记录:

  1. <?php
  2. $host = "host=127.0.0.1";
  3. $port = "port=5432";
  4. $dbname = "dbname=testdb";
  5. $credentials = "user=postgres password=pass123";
  6. $db = pg_connect( "$host $port $dbname $credentials" );
  7. if(!$db){
  8. ​ echo "Error : Unable to open databasen";
  9. } else {
  10. ​ echo "Opened database successfullyn";
  11. }
  12. $sql =<<<EOF
  13. ​ INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
  14. ​ VALUES (1, 'Paul', 32, 'California', 20000.00 );
  15. ​ INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
  16. ​ VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
  17. ​ INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
  18. ​ VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
  19. ​ INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
  20. ​ VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
  21. EOF;
  22. $ret = pg_query($db, $sql);
  23. if(!$ret){
  24. ​ echo pg_last_error($db);
  25. } else {
  26. ​ echo "Records created successfullyn";
  27. }
  28. pg_close($db);
  29. ?>

上述程序执行时,它会创建COMPANY表中的记录,并会显示以下两行:

Opened database successfully

Records created successfully

SELECT 操作

PHP程序表明我们如何获取并显示COMPANY 在上面的例子中创建表的记录:

  1. <?php
  2. $host = "host=127.0.0.1";
  3. $port = "port=5432";
  4. $dbname = "dbname=testdb";
  5. $credentials = "user=postgres password=pass123";
  6. $db = pg_connect( "$host $port $dbname $credentials" );
  7. if(!$db){
  8. ​ echo "Error : Unable to open databasen";
  9. } else {
  10. ​ echo "Opened database successfullyn";
  11. }
  12. $sql =<<<EOF
  13. ​ SELECT * from COMPANY;
  14. EOF;
  15. $ret = pg_query($db, $sql);
  16. if(!$ret){
  17. ​ echo pg_last_error($db);
  18. ​ exit;
  19. }
  20. while($row = pg_fetch_row($ret)){
  21. ​ echo "ID = ". $row[0] . "n";
  22. ​ echo "NAME = ". $row[1] ."n";
  23. ​ echo "ADDRESS = ". $row[2] ."n";
  24. ​ echo "SALARY = ".$row[4] ."nn";
  25. }
  26. echo "Operation done successfullyn";
  27. pg_close($db);
  28. ?>

当上述程序执行时,它会产生以下结果。字段返回序列中,它们被用来创建表的同时保持注意。

Opened database successfully

ID = 1

NAME = Paul

ADDRESS = California

SALARY = 20000

ID = 2

NAME = Allen

ADDRESS = Texas

SALARY = 15000

ID = 3

NAME = Teddy

ADDRESS = Norway

SALARY = 20000

ID = 4

NAME = Mark

ADDRESS = Rich-Mond

SALARY = 65000

Operation done successfully

UPDATE 操作

下面的PHP代码显示了如何我们可以使用UPDATE语句来更新任何记录,然后获取并显示更新的记录,从COMPANY 表:

  1. <?php
  2. $host = "host=127.0.0.1";
  3. $port = "port=5432";
  4. $dbname = "dbname=testdb";
  5. $credentials = "user=postgres password=pass123";
  6. $db = pg_connect( "$host $port $dbname $credentials" );
  7. if(!$db){
  8. ​ echo "Error : Unable to open databasen";
  9. } else {
  10. ​ echo "Opened database successfullyn";
  11. }
  12. $sql =<<<EOF
  13. ​ UPDATE COMPANY set SALARY = 25000.00 where ID=1;
  14. EOF;
  15. $ret = pg_query($db, $sql);
  16. if(!$ret){
  17. ​ echo pg_last_error($db);
  18. ​ exit;
  19. } else {
  20. ​ echo "Record updated successfullyn";
  21. }
  22. $sql =<<<EOF
  23. ​ SELECT * from COMPANY;
  24. EOF;
  25. $ret = pg_query($db, $sql);
  26. if(!$ret){
  27. ​ echo pg_last_error($db);
  28. ​ exit;
  29. }
  30. while($row = pg_fetch_row($ret)){
  31. ​ echo "ID = ". $row[0] . "n";
  32. ​ echo "NAME = ". $row[1] ."n";
  33. ​ echo "ADDRESS = ". $row[2] ."n";
  34. ​ echo "SALARY = ".$row[4] ."nn";
  35. }
  36. echo "Operation done successfullyn";
  37. pg_close($db);
  38. ?>

当上述程序执行时,它会产生以下结果:

Opened database successfully

Record updated successfully

ID = 2

NAME = Allen

ADDRESS = 25

SALARY = 15000

ID = 3

NAME = Teddy

ADDRESS = 23

SALARY = 20000

ID = 4

NAME = Mark

ADDRESS = 25

SALARY = 65000

ID = 1

NAME = Paul

ADDRESS = 32

SALARY = 25000

Operation done successfully

DELETE 操作

下面的PHP代码,表明我们如何能够使用DELETE语句删除任何记录,然后获取并显示COMPANY 表剩余的记录:

  1. <?php
  2. $host = "host=127.0.0.1";
  3. $port = "port=5432";
  4. $dbname = "dbname=testdb";
  5. $credentials = "user=postgres password=pass123";
  6. $db = pg_connect( "$host $port $dbname $credentials" );
  7. if(!$db){
  8. ​ echo "Error : Unable to open databasen";
  9. } else {
  10. ​ echo "Opened database successfullyn";
  11. }
  12. $sql =<<<EOF
  13. ​ DELETE from COMPANY where ID=2;
  14. EOF;
  15. $ret = pg_query($db, $sql);
  16. if(!$ret){
  17. ​ echo pg_last_error($db);
  18. ​ exit;
  19. } else {
  20. ​ echo "Record deleted successfullyn";
  21. }
  22. $sql =<<<EOF
  23. ​ SELECT * from COMPANY;
  24. EOF;
  25. $ret = pg_query($db, $sql);
  26. if(!$ret){
  27. ​ echo pg_last_error($db);
  28. ​ exit;
  29. }
  30. while($row = pg_fetch_row($ret)){
  31. ​ echo "ID = ". $row[0] . "n";
  32. ​ echo "NAME = ". $row[1] ."n";
  33. ​ echo "ADDRESS = ". $row[2] ."n";
  34. ​ echo "SALARY = ".$row[4] ."nn";
  35. }
  36. echo "Operation done successfullyn";
  37. pg_close($db);
  38. ?>

当上述程序执行时,它会产生以下结果:

Opened database successfully

Record deleted successfully

ID = 3

NAME = Teddy

ADDRESS = 23

SALARY = 20000

ID = 4

NAME = Mark

ADDRESS = 25

SALARY = 65000

ID = 1

NAME = Paul

ADDRESS = 32

SALARY = 25000

Operation done successfully

关闭数据库postgresql

可直接调用 pg_close(); 返回true/false 或1/0

原文章摘自:https://www.yiibai.com/html/postgresql/2013/080896.html

Last modification:January 9th, 2019 at 10:40 am
If you think my article is useful to you, please feel free to appreciate

Leave a Comment