警告：pg_query（）：查询失败：错误：语法错误在或附近 [英] Warning: pg_query(): Query failed: ERROR: syntax error at or near

问题描述

我有一个脚本，该脚本应该根据用户在上一页上单击的链接来显示Postgres查询的结果。

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.

例如，当用户单击项目的标题，将其定向到一个页面，该页面向他们显示有关该项目的更多属性，这些属性包含在数据库的列中。

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用户可以单击标题的页面已经可以使用，但是单击标题后，用于显示项目其他属性的第二页不起作用。

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:

警告：pg_query（）：查询失败：错误： 20131418或附近的语法错误LINE 1：20131418 ^ /var/www/html/view_project.php在第13行

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

警告：pg_num_rows（）期望参数1为资源，在第14行的/var/www/html/view_project.php中给出的布尔值

Warning: pg_num_rows() expects parameter 1 to be resource, boolean given in /var/www/html/view_project.php on line 14

现在，我认为第二个错误不是问题，因为解决第一个错误将产生查询结果，然后清除第二个错误。

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.

我不了解语法有什么问题；查询末尾具有 $ pid 的整数返回一个整数（20131418），该整数被作为无效语法调用。我该怎么办才能解决此问题？

I don't understand what is wrong with the syntax; having $pid at 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?

推荐答案

SQL语句的正确语法如下：

The correct syntax for the SQL statement is as follows:

'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.

请参见以下类似问题的答案： https://stackoverflow.com / a / 12250721/3620249

See this answer to a similar question: https://stackoverflow.com/a/12250721/3620249

我以为 $ pid 必须在查询中字符串（单引号）。但是，除非查询字符串用双引号引起来，否则不会调用该变量。然后，也难以管理带引号的列/表名称中的混合大小写，因此我尝试使用 + 进行连接，而不是使用。 / code>，就像我在问题中看到的那样。

I had thought the $pid needed 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.

经验教训：

1. 使用在PHP中进行连接。


2. 可以使用连接从查询字符串的外部调用变量。


3. 如果表/列名包含大小写混合的情况，则必须将其包含在双引号中

这篇关于警告：pg_query（）：查询失败：错误：语法错误在或附近的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！