(PHP 4 >= 4.2.0, PHP 5, PHP 7, PHP 8)
pg_query — 执行查询
pg_query() 在特定数据库 connection 上执行
query。pg_query_params() 在大多数情况下应该是首选。
如果发生错误并返回 false ,那么在连接有效时可以使用 pg_last_error() 函数检索错误的详细信息。
注意: 尽管可以省略
connection,但不建议这样做,因为可能会导致脚本中的错误难以发现。
注意:
本函数以前的名字为 pg_exec()。pg_exec() 因为兼容性原因仍可使用,但鼓励用户使用新名称。
connectionAn PgSql\Connection instance.
When connection is unspecified, the default connection is used.
The default connection is the last connection made by pg_connect()
or pg_pconnect() .
As of PHP 8.1.0, using the default connection is deprecated.
query要执行的 SQL 语句。当多个语句传递给函数时,将作为一个事务自动执行,除非查询字符串中包含明确的 BEGIN/COMMIT 命令。但是,不建议在一个函数调用中使用多个事务。
用户提供的数据作为字符串插入值非常危险,很可能导致 SQL 注入漏洞。在大多数情况下,应该首选 pg_query_params() ,将用户提供的值作为参数传递,而不是将它们替换为查询字符串。
任何用户提供的数据,都应该正确转义,然后直接替换为查询字符串。
成功时为 PgSql\Result 实例, 或者在失败时返回 false 。
| 版本 | 说明 |
|---|---|
| 8.1.0 | 现在返回 PgSql\Result 实例,之前返回 resource |
| 8.1.0 |
现在 connection 参数接受 PgSql\Connection
实例,之前接受 resource 。
|
示例 #1 pg_query() 示例
<?php
$conn = pg_pconnect("dbname=publisher");
if (!$conn) {
echo "An error occurred.\n";
exit;
}
$result = pg_query($conn, "SELECT author, email FROM authors");
if (!$result) {
echo "An error occurred.\n";
exit;
}
while ($row = pg_fetch_row($result)) {
echo "Author: $row[0] E-mail: $row[1]";
echo "<br />\n";
}
?>
示例 #2 使用多条语句的 pg_query()
<?php
$conn = pg_pconnect("dbname=publisher");
// 这些语句将作为一个事务执行
$query = "UPDATE authors SET author=UPPER(author) WHERE id=1;";
$query .= "UPDATE authors SET author=LOWER(author) WHERE id=2;";
$query .= "UPDATE authors SET author=NULL WHERE id=3;";
pg_query($conn, $query);
?>
$GLOBALS["PG_CONNECT"]=pg_connect(...);
....
function query ($sqlQuery,$var=0) {
if (!$GLOBALS["PG_CONNECT"]) return 0;
$lev=error_reporting (8); //NO WARRING!!
$result=pg_query ($sqlQuery);
error_reporting ($lev); //DEFAULT!!
if (strlen ($r=pg_last_error ($GLOBALS["PG_CONNECT"]))) {
if ($var) {
echo "<p color=\"red\">ERROR:<pre>";
echo $sqlQuery;
echo "</pre>";
echo $r;
echo "</p>";
}
close_db ();
return 0;
}
return $result;
}A quick note for novice users: when gathering input from fields on a web form that maintains a database connection, *never* use pg_query to do queries from the field. Always sanitize input using pg_prepare and pg_execute.expanding on the note left by "cmoore" -
To check to see if the recordset returned no records,
<?php
$result=pg_query($conn, "SELECT * FROM x WHERE a=b;");
if (!$result) {
echo "query did not execute";
}
$rs = pg_fetch_assoc($result);
if (!$rs) {
echo "0 records"
}
?>
-jackIt would be better this way:
<?php
$result=pg_query($conn, "SELECT COUNT(*) AS rows FROM x WHERE a=b;");
if (!$result) {
echo "query did not execute";
}
if ($line = pg_fetch_assoc($result)) {
if ($line['rows'] == 0) {
echo "0 records"
}
}
else {
while ($row = pg_fetch_array($result)) {
//do stuff with $row
}
}
?>
This solution doesn't raise the load of the system with the move of matching rows (perhaps 0,1, perhaps 100, 1000, ... rows)One thing to note that wasn't obvious to me at first. If your query returns zero rows, that is not a "failed" query. So the following is wrong:
$result=pg_query($conn, "SELECT * FROM x WHERE a=b;");
if (!$result) {
echo "No a=b in x\n";
}
pg_query returns FALSE if the query can not be executed for some reason. If the query is executed but returns zero rows then you get back a resul with no rows.There was a typo in the code that I posted:
<?php
$result=pg_query($conn, "SELECT * FROM x WHERE a=b;");
if (!$result) {
echo "query did not execute";
}
if (pg_num_rows($result) == 0) {
echo "0 records"
}
else {
while ($row = pg_fetch_array($result)) {
//do stuff with $row
}
}
?>Use pg_query to call your stored procedures, and use pg_fetch_result when getting a value (like a smallint as in this example) returned by your stored procedure.
<?php
$pgConnection = pg_connect("dbname=users user=me");
$userNameToCheckFor = "metal";
$result = pg_query($pgConnection, "SELECT howManyUsersHaveThisName('$userNameToCheckFor')");
$count = pg_fetch_result($result, 0, 'howManyUsersHaveThisName');
?>Improving upon what jsuzuki said:
It's probably better to use pg_num_rows() to see if no rows were returned, as that leaves the resultset cursor pointed to the first row so you can use it in a loop.
Example:
<?php
$result=pg_query($conn, "SELECT * FROM x WHERE a=b;");
if (!$result) {
echo "query did not execute";
}
if (pg_num_rows($result) == 0) {
echo "0 records"
}
else {
while ($row = pg_fetch_array($result) {
//do stuff with $row
}
}
?>
I, personally, also find it more readable.Here is my small function to make it easier for me to use data from select queries (attention, it is sensitive to sql injection)
<?php
function requestToDB($connection,$request){
if(!$result=pg_query($connection,$request)){
return False;
}
$combined=array();
while ($row = pg_fetch_assoc($result)) {
$combined[]=$row;
}
return $combined;
}
?>
Example:
<?php
$conn = pg_pconnect("dbname=mydatabase");
$results=requestToDB($connect,"select * from mytable");
//You can now access a "cell" of your table like this:
$rownumber=0;
$columname="mycolumn";
$mycell=$results[$rownumber][$columname];
var_dump($mycell);