postgresql 警告：pg_query()：查询失败：错误：语法错误在或附近
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27594423/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Warning: pg_query(): Query failed: ERROR: syntax error at or near
提问by ge0m3try
I have a script that is supposed to display the results of a Postgres query based on a link that the user clicks on a previous page.
我有一个脚本，它应该根据用户在上一页上单击的链接来显示 Postgres 查询的结果。
For example, when the user clicks on the Title of a project, it directs them to a page that shows them more attributes about the project, which are contained in columns in the database.
例如，当用户单击项目的标题时，它会将他们定向到一个页面，该页面向他们显示有关该项目的更多属性，这些属性包含在数据库的列中。
I already have the page working where users can click on a Title, but once they click a title, my second page to display the other attributes of the project is not working.
我已经有了用户可以单击标题的页面，但是一旦他们单击标题，我显示项目其他属性的第二个页面就不起作用了。
<?php
ini_set('display_errors',1); error_reporting(E_ALL);
$row = false;
if (isset($_GET['pid']) && filter_var($_GET['pid'], FILTER_VALIDATE_INT, array('min_range' => 1)) ) {
$pid = $_GET['pid'];
require('/var/www/postgres_connect.php');
$q = 'SELECT * FROM public.' + "tblProjects" + 'WHERE ' + "tblProjects" + '.ProjectID = ' + "$pid";
$r = pg_query ($dbconn, $q);
if (pg_num_rows($r) == 1) {
$row = pg_fetch_assoc ($r);
$page_title = $row['ProjectID'];
echo "<div align=\"center\">
<b>{$row['ProjectID']}</b> by
{$row['ProjectTitle']}<br />";
echo '<p align="center">' . ((is_null($row['totalcost'])) ? '(No Cost Recorded)' :
$row['totalcost']) . '</p>';
}
pg_close($dbconn);
}
if (!$row) {
$page_title = 'Error';
echo '<div align="center">This page encountered an error!</div>';
}
?>
Running this script produces the following error:
运行此脚本会产生以下错误：
Warning: pg_query(): Query failed: ERROR: syntax error at or near "20131418" LINE 1: 20131418 ^ in /var/www/html/view_project.php on line 13
Warning: pg_num_rows() expects parameter 1 to be resource, boolean given in /var/www/html/view_project.php on line 14
警告：pg_query()：查询失败：错误：“20131418”处或附近的语法错误第 1 行：20131418 ^ in /var/www/html/view_project.php 第 13 行
警告：pg_num_rows() 期望参数 1 是资源，布尔值在第 14 行的 /var/www/html/view_project.php 中给出
Now, I don't think the second error is a problem because solving the first error will produce a result of the query and then clear up the second error.
现在，我认为第二个错误不是问题，因为解决第一个错误会产生查询结果，然后清除第二个错误。
I don't understand what is wrong with the syntax; having $pidat the end of the query returns an integer(20131418) which is being called out as invalid syntax. What can I do to solve this issue?
我不明白语法有什么问题；具有$pid在所述查询的末尾返回正被调出作为无效的语法的整数（20131418）。我能做些什么来解决这个问题？
采纳答案by ge0m3try
The correct syntax for the SQL statement is as follows:
SQL 语句的正确语法如下：
'SELECT * FROM public."tblProjects" WHERE "tblProjects"."ProjectID"=' . $pid;
The main reason for this not working in the first place was because of mixed cases in column and table names.
这首先不起作用的主要原因是列名和表名中的大小写混合。
See this answer to a similar question: https://stackoverflow.com/a/12250721/3620249
请参阅类似问题的答案：https: //stackoverflow.com/a/12250721/3620249
I had thought the $pidneeded to be within the query string (single quotes). However, the variable would not be called unless the query string was double quoted. It then became difficult to manage the mixed cases in the column/table names with quotes as well, so I tried using +to concatenate instead of .as you can see in my question.
我曾认为$pid需要在查询字符串（单引号）内。但是，除非查询字符串是双引号，否则不会调用该变量。然后也很难用引号来管理列/表名称中的混合大小写，因此我尝试使用+连接而不是.您在我的问题中看到的那样。
Lessons learned:
得到教训：
- Concatenate in PHP using
. - Variables can be called from outside of a query string using concatenation.
- If table/column names contain mixed cases, they must be contained within double quotations
- 在 PHP 中使用连接
. - 可以使用串联从查询字符串外部调用变量。
- 如果表/列名称包含大小写混合，它们必须包含在双引号内
