oracle select query with where clause returns incomplete record when run using php script

Question

I have an sql query that runs just fine when directly run in Oracle SQL developer which shows 3k+ records. The problems comes when I run it through a php script it only shows about 20+ records please help! btw both queries are used the same values for WHERE clause i also tried using order by but same results appear.

here my query:

sql run in php:

function getAllCOBDataList() 
{
            $branchcode = $_SESSION['branchcode'];
            $officeId = $_SESSION['officeid'];
            //$expenseFilter = $_SESSION['earmark-expenseCode'];
            $cob_data_list = array();

            $conn = persistentDBConnect();

            $query = "WITH CTE_TotalBalance AS (Select BUDGETID ,sum(TOTALCOST)"
                   . " as CURRENT_BALANCE from (Select BUDGETID, TOTALCOST from"
                   . " FARU.BEMM_DATA_COB"
                   . " Union all"
                   . " Select BUDGETID, EARMARKAMOUNT "
                   . " from FARU.BEMM_TRANS_EARMARKINGLIST)"
                   . " GROUP BY BUDGETID)"
                   . "SELECT"
                   . " A.FYEAR, A.BUDGETID, A.PERSPECTIVEID as perspective, A.FUNDTYPE, A.PARTICULAR, A.TOTALCOST,"
                   . " A.Q1UNITS, A.Q2UNITS, A.Q3UNITS, A.Q4UNITS,"
                   . " B.PERSPECTIVEID, B.PERSPECTIVEDESC,"
                   . " C.BPAID, C.BPADESC,"
                   . " D.INITIATIVEID, D.INITIATIVEDESC,"
                   . " E.STOBID, E.STOBDESC,"
                   . " F.TARGETID, F.TARGETDESC,"
                   . " G.OFFICEID, G.BRANCHCODE, G.PPACODE, G.PPADESC,"
                   . " H.SUBOFFICEID, H.SUBPPADESC,"
                   . " I.EXPCODE, I.EXPCODEDESC,"
                   . " J.EXPENSECLASS, J.EXPENSEDESC,"
                   . " K.CURRENT_BALANCE"
                   . " FROM"
                   . " FARU.BEMM_DATA_COB A"
                   . " INNER JOIN FARU.BEMM_LIB_PERSPECTIVE B ON A.PERSPECTIVEID = B.PERSPECTIVEID"
                   . " INNER JOIN FARU.BEMM_LIB_BPAS C ON A.BPAID = C.BPAID"
                   . " INNER JOIN FARU.BEMM_LIB_INITIATIVE D ON A.INITIATIVEID = D.INITIATIVEID"
                   . " INNER JOIN FARU.BEMM_LIB_STOB E ON A.STOBID = E.STOBID"
                   . " INNER JOIN FARU.BEMM_LIB_TARGET F ON A.TARGETID = F.TARGETID"
                   . " INNER JOIN FARU.BEMM_LIB_PPALIST G ON A.OFFICEID = G.OFFICEID"
                   . " INNER JOIN FARU.BEMM_LIB_SUBPPALIST H ON A.SUBOFFICEID = H.SUBOFFICEID"
                   . " INNER JOIN FARU.BEMM_LIB_EXPCODE I ON A.EXPENSECODE = I.EXPCODE"
                   . " INNER JOIN FARU.BEMM_LIB_EXPENSELIST J ON A.EXPENSECLASS = J.EXPENSECLASS"
                   . " INNER JOIN FARU.CTE_TotalBalance K ON A.BUDGETID = K.BUDGETID"
                   . " WHERE G.BRANCHCODE = '$branchcode'";
                  // . " WHERE G.OFFICEID = '$officeId'"
                   //. " Order by A.BUDGETID Asc";
            //echo $query;
            $stmt = oci_parse($conn, $query);
            $result =oci_execute($stmt);

            while($result = oci_fetch_assoc($stmt)){
                $cob_data_list[] = $result;
            }
            return $cob_data_list;


}

TIA!

This is just the query itself, but you are not showing the code which actually uses this string. Are you using mysqli, PDO, or what? Please edit the snippet to include the way you are actually trying to execute the query. — mrodo
– mrodo, Commented Feb 27, 2020 at 23:41
matteo.rebeschi i edited my snippet this function is called in a table from another php file. — sprocra
– sprocra, Commented Feb 28, 2020 at 0:11
so are you wondering why PHP is unable to display 30k+ records? have you checked the memory footprint on the browser? — THBBFT
– THBBFT, Commented Feb 28, 2020 at 0:46
@THBBFT yes im wondering whats with it. not yet havent done that — sprocra
– sprocra, Commented Feb 28, 2020 at 1:54

Christopher Jones · Accepted Answer · 2020-03-01 23:23:19Z

1

Check that you are actually connecting to the same user & DB in PHP and in SQL Developer (a common mistake).

Try adding some var_dump() calls and run the query in the command line PHP to check it works.

Add some error handling, see page 161 of Oracle's free book The Underground PHP and Oracle Manual.

You almost certainly should be using a bind variable instead of " WHERE G.BRANCHCODE = '$branchcode'"; because this is a potential, big security hole. And will impact application performance and scalability. Read up on oci_bind_by_name().

When fetching lots of rows, you can tune performance with oci_set_prefetch().

answered Mar 1, 2020 at 23:23

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

oracle select query with where clause returns incomplete record when run using php script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related