Where could I be doing wrong in this PHP ORACLE pagination script?

I have this problem whereby I cannot display records from oracle database to my web application using PHP as a server side scripting language.Could someone kindly tell me where I could be doing wrong? I want by the end of the day to be able to achieve pagination and replace ROWNUM and rnum with variables that users can manipulate when moving fromm page to page.

     <?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";

    ?>

If you're getting an error its most likely the ; at the end of your query would cause an error. The ; is not part of SQL itself, its just usually required by whatever client you're playing with to mark the end of the SQL. So when embedding plain SQL in a program you should not end it with a ;

NOTE: If the ; is part of PL/SQL, so if you're embedding that you need to include it

Rownum is calculated after the result set is returned so wont help with a pagination script as if you want rows from the inner query returned as rows 10-20 in the full query, it will reset and start at 1.

Try instead to use an analytic query with ROW_NUMBER() instead like this

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;

From a performance point of view though, the above is not great because it will hit the database server and request the full result set each time so maybe better if you can run the query to get the data just once and then use PHP to programmatically step through the result set using the forward/back links.

To try this, have a look at this php pagination script (although it's against mysql, it should give you a starting point to write something similar using Oracle which doesnt cause a performance issue)

//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();  

I realized the problem came about because of the preceding unnecessary space before SOURCE_LOCATION in the query and the unnecessary semicolon (;) at the end of the query.

The code perfectly works the way I wanted.Thanks to each and everyone of you for the contribution you made towards giving Answers to the Question.

I appreciate all your efforts.

The working code now looks as follows;

       <?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>";

         ?>