我在这个 PHP ORACLE 分页脚本中哪里可能做错了？

Question

我遇到这个问题，无法使用 PHP 作为服务器端脚本语言将 Oracle 数据库中的记录显示到我的 Web 应用程序。有人可以告诉我哪里可能做错了吗？我希望最终能够实现分页，并将 ROWNUM 和 rnum 替换为用户在从一个页面移动到另一个页面时可以操作的变量。

     <?php  

    /* Connection string to Oracle view */  
    /* user is patients */  
    /* password is patients */  
    $conn=oci_connect('patients','patients','192.168.1.100/hosecare');  

    /* Query expected to do pagination and get records */  
    $qry="select *  
    from (select a.*, ROWNUM rnum  
    from (select BILL_NO,AK_NO,PAT_NAME,VOUCHER_DATE,USER_NAME,PAYMENT_AMT from patients    WHERE VOUCHER_DATE >='01-Sep-2011' AND VOUCHER_DATE <='26-Sep-2011' AND  SOURCE_LOCATION='KIAMBU CLINIC' ORDER BY VOUCHER_DATE)a
    where ROWNUM <=20)  
    where rnum >=10;";  


    $stid=oci_parse($conn,$qry);  
    oci_execute($stid);  

    /* Table begins here */  
    echo "<table border='1'>\n";  
    echo "<tr>\n";  

    /* Table Column headers */  
    echo "<td>".'<h3>BILL NO</h3>'."</td>";  
    echo "<td>".'<h3>ACCOUNT NO</h3>'."</td>";  
    echo "<td>".'<h3>PATIENT NAME</h3>'."</td>";  
    echo "<td>".'<h3>VOUCHER DATE</h3>'."</td>";  
    echo "<td>".'<h3>USER NAME</h3>'."</td>";  
    echo "<td>".'<h3>PAYMENT AMOUNT</h3>'."</td>";  
    echo "</tr>\n";  

    /* Populating Table cells with records resulting from the pagination query */  
     while($row=oci_fetch_array($stid,OCI_ASSOC+OCI_RETURN_NULLS)) {  
    echo "<tr>\n";  
    foreach($row as $item){  
            echo "<td>".($item !==null ? htmlentities($item,ENT_QUOTES) :  "&nbsp;")."    </td>\n";
    }
    echo "</tr>\n";
    }

    echo "</table>\n";

    ?>

Answer 1

如果您收到错误，则查询末尾的 ; 很可能会导致错误。 ; 不是 SQL 本身的一部分，只是您正在使用的任何客户端通常都需要它来标记 SQL 的结束。因此，在程序中嵌入纯 SQL 时，不应以 ; 结尾。

注意：如果 ; 是 PL/SQL 的一部分，那么如果您要嵌入需要包含它

Answer 2

Rownum 是在结果集返回后计算的，因此对分页脚本没有帮助，就像您希望内部查询中的行作为完整查询中的第 10-20 行返回一样，它将重置并从 1 开始。

请尝试使用分析使用 ROW_NUMBER() 进行查询，而不是像这样

SELECT * FROM 
(SELECT BILL_NO,
        AK_NO,
        PAT_NAME,
        VOUCHER_DATE,
        USER_NAME,
        PAYMENT_AMT,
        ROW_NUMBER() OVER (ORDER BY VOUCHER_DATE ASC) RN
   FROM patients    
  WHERE VOUCHER_DATE >='01-Sep-2011'  
    AND VOUCHER_DATE <='26-Sep-2011'  
    AND  SOURCE_LOCATION='KIAMBU CLINIC' 
  ORDER BY VOUCHER_DATE)
WHERE RN BETWEEN 10 and 20;

从性能的角度来看，上面的方法并不是很好，因为它会访问数据库服务器并每次请求完整的结果集，因此如果您可以运行查询来获取数据可能会更好一次和然后使用 PHP 使用前向/后向链接以编程方式逐步浏览结果集。

要尝试这个，请看一下这个 php 分页脚本（尽管它是针对 mysql 的，它应该为您提供一个使用 Oracle 编写类似内容的起点，这不会导致性能问题）

//Include the PS_Pagination class  
include('ps_pagination.php');  

//Connect to mysql db  
$conn = mysql_connect('localhost','root','');  
mysql_select_db('yourdatabase',$conn);  
$sql = 'SELECT post_title FROM wp_posts WHERE post_type="post" ORDER BY ID DESC';  

//Create a PS_Pagination object  
$pager = new PS_Pagination($conn,$sql,10,10);  

//The paginate() function returns a mysql result set  
$rs = $pager->paginate();  
while($row = mysql_fetch_assoc($rs)) {  
    echo $row['post_title'],"\n";  
}  

//Display the full navigation in one go  
echo $pager->renderFullNav();  

Answer 3

我意识到问题的出现是因为查询中 SOURCE_LOCATION 之前有多余的空格以及查询末尾不必要的分号 (;)。

该代码完美地按照我想要的方式工作。感谢你们每一个人为回答问题所做的贡献。

我感谢你的所有努力。

工作代码现在如下所示；

       <?php

        //Connection string to Oracle view
        //user is patients
        //password is patients
        $conn=oci_connect('patients','patients','192.168.1.100/hosecare');
        //Query expected to do pagination and get records

        //$page will vary depending on which page the user has accessed.

        $page=1;
        $pageSize=20;
        $maxrowfetch=(($page * $pageSize) + 1);
        $minrowfetch=((($page - 1) * $pageSize) + 1);

        //QUERY WORKING...MODIFIED TO FIT USER REQUIREMENTS
        $qry="select * 
        from (select a.*, ROWNUM rnum
        from (select BILL_NO,AK_NO,PAT_NAME,VOUCHER_DATE,USER_NAME,PAYMENT_AMT from smart WHERE VOUCHER_DATE >='20-Sep-2011' AND VOUCHER_DATE <='26-Sep-2011' AND SOURCE_LOCATION='KIAMBU CLINIC' ORDER BY BILL_NO ASC)a
        where ROWNUM <="."$maxrowfetch".")
        where rnum >="."$minrowfetch"."";


        //QUERY NOT WORKING...THE 2 SPACES BEFORE SOURCE_LOCATION IN THE QUERY WAS THE PROBLEM
        /***
        $qry="select * 
        from (select a.*, ROWNUM rnum
        from (select BILL_NO,AK_NO,PAT_NAME,VOUCHER_DATE,USER_NAME,PAYMENT_AMT from patients WHERE VOUCHER_DATE >='01-Sep-2011' AND VOUCHER_DATE <='26-Sep-2011' AND  SOURCE_LOCATION='KIAMBU CLINIC' ORDER BY VOUCHER_DATE ASC)a
        where ROWNUM <=20)
        where rnum >=10";
        ***/


        //QUERY WORKING...1 SPACE BEFORE SOURCE_LOCATION IN QUERY
        /***
        $qry="select * 
        from (select a.*, ROWNUM rnum
        from (select BILL_NO,AK_NO,PAT_NAME,VOUCHER_DATE,USER_NAME,PAYMENT_AMT from patients WHERE VOUCHER_DATE >='01-Sep-2011' AND VOUCHER_DATE <='26-Sep-2011' AND  SOURCE_LOCATION='KIAMBU CLINIC' ORDER BY VOUCHER_DATE ASC)a
        where ROWNUM <=20)
        where rnum >=10";
        ***/


        $stid=oci_parse($conn,$qry);
        oci_execute($stid);

        //Table begins here
        echo "<table border='1'>\n";
        echo "<tr>\n";

        //Table Column headers
        echo "<td>".'<h3>BILL NO</h3>'."</td>";
        echo "<td>".'<h3>ACCOUNT NO</h3>'."</td>";
        echo "<td>".'<h3>PATIENT NAME</h3>'."</td>";
        echo "<td>".'<h3>VOUCHER DATE</h3>'."</td>";
        echo "<td>".'<h3>USER NAME</h3>'."</td>";
        echo "<td>".'<h3>PAYMENT AMOUNT</h3>'."</td>";
        echo "</tr>\n";

        //Populating Table cells with records resulting from the pagination query
        while($row=oci_fetch_array($stid,OCI_ASSOC+OCI_RETURN_NULLS)) {
        echo "<tr>\n";


            echo "<td>";
            echo $row["BILL_NO"];
            echo "</td>";
            echo "<td>";
            echo $row["AK_NO"];
            echo "</td>";
            echo "<td>";
            echo $row["PAT_NAME"];
            echo "</td>";
            echo "<td>";
            echo $row["VOUCHER_DATE"];
            echo "</td>";
            echo "<td>";
            echo $row["USER_NAME"];
            echo "</td>";
            echo "<td>";
            echo $row["PAYMENT_AMT"];
            echo "</td>";
            echo "</tr>";


        }

        echo "</table>\n";

        echo "MAX ROW FETCH ".$maxrowfetch."<br>";
        echo "MIN ROW FETCH ".$minrowfetch."<br>";
        echo $qry."<br>";

         ?>