Querying JSON from PHP Function

Question

I have a sequence order of PHP programming that i need to query from a table and insert it in a JSON array. I dont know where my mistake is but it just doesnt go through. please help me,

in dbinfo.inc.php,

define("ORA_CON_UN", "ADMIN");
define("ORA_CON_PW", "pass");
define("ORA_CON_DB", "192.168.100.195/finance");

class db {
    private $conn;
    private $lastId;
    private static $instance;

private function _construct(){
    $this->connect();   
}

public static function create(){
    if (!isset(self::$instance)) {
        self::$instance = new db();
    }

    return self::$instance;
}

public function connect($dbconn = ORA_CON_DB, $dbuser = ORA_CON_UN, $dbpass = ORA_CON_PW){
    $this->conn = oci_connect($dbuser, $dbpass, $dbconn);
}
}

and in DBFunction.php

include 'dbinfo.inc.php';

class Connection{
    private $dbConnection;

    public function _construct($dbConnection){
        $this->dbConnection = $dbConnection;
    }

    function initConnection(){
        $db = new db();
        $dbConnection = $db->connect(); 
    }
}

class PurchaseOrder {
    private $job = '%';
    private $subjob = '%';

    public function _construct($job, $subjob){
        $this->job = $job;
        $this->subjob = $subjob;
    }

    function listPO($job, $subjob){

        $conn = new Connection();
        $conn->initConnection();
        $sql = oci_parse($conn, 'SELECT VPI.PO_NO FROM VW_PO_INFO@WENFINANCE_WENLOGINV_LINK WHERE VPI.PROJECT_NO = ' .$job. ' AND VPI.PROJECT_NAME =  ' .$subjob);

        if (!$conn) {
            $oerr = OCIError($conn); 
            trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
            exit();
        } else {
            echo 'CONNECTION SUCCEDED';
        }

        $rows = array();
        while ($r = oci_fetch_assoc($sql)){
            $rows[] = $r;
        } 
        $listPO = json_encode($rows);

        oci_execute($sql);
        oci_close($conn);
    }
}

and lastly, testDBFunction.php

include('DBFunction.php');

$queryOracle = new PurchaseOrder();

$queryOracle->listPO('W-IGG','');

var_dump($queryOracle);

and this is my error message,

Warning: oci_parse() expects parameter 1 to be resource, object given in C:\xampp\htdocs\WeltesFinance\pages\lib\DBFunction.php on line 36 CONNECTION SUCCEDED Warning: oci_fetch_assoc() expects parameter 1 to be resource, null given in C:\xampp\htdocs\WeltesFinance\pages\lib\DBFunction.php on line 47

Warning: oci_execute() expects parameter 1 to be resource, null given in C:\xampp\htdocs\WeltesFinance\pages\lib\DBFunction.php on line 52 object(PurchaseOrder)#1 (2) { ["job":"PurchaseOrder":private]=> string(1) "%" ["subjob":"PurchaseOrder":private]=> string(1) "%" }

I dont know exactly where my error is, Please help me

Alex Andrei · Accepted Answer · 2015-10-20 06:53:35Z

UPDATE:
I made some more updates to your code and classes, here's relevant changes for each file.

dbinfo.inc.php

<?php

define("ORA_CON_UN", "ADMIN");
define("ORA_CON_PW", "pass");
define("ORA_CON_DB", "192.168.100.195/finance");

Class DbConnect{
    private $user = null;
    private $password = null;
    private $db_string = null;

    public function __construct(){
        $this->user = ORA_CON_UN;
        $this->password = ORA_CON_PW;
        $this->db_string = ORA_CON_DB;
    }

    public function connect() {
        $connection = oci_pconnect($this->user, $this->password, $this->db_string);
        return $connection;
    }
}
?>

DBfunction.php

<?php
include 'dbinfo.inc.php';

// there is no need for a Connection class unless you want further wrapping or something :-/

Class PurchaseOrder {

    // ....

    public function listPO($job,$subjob){
        $db = new DbConnect();
        $conn = $DbConnect->connect();

        if (!$conn) {
            // keep your code, throw error, exit
        }
        else{
            // move all your database processing here
            $sql = oci_parse($conn, 'SELECT VPI.PO_NO FROM VW_PO_INFO...');
            // also note that you are passing an empty value for the $subjob parameter, thus making the query likely to fail
            oci_execute($sql);

            $rows = array();
            while($r = oci_fetch_assoc($sql)){
                $rows[] = $r;
            }
            $listPO = json_encode($rows);
            oci_close($conn);
        }

        return $listPO; // you need to return $listPO in order to be able to dump it
    }

    // ....
}

testDBFunction.php

<?php

include('DBFunction.php');

$queryOracle = new PurchaseOrder();

// either pass what listPO() returns to a variable and dump it
$jsonResults = $queryOracle->listPO('W-IGG','');    
var_dump($jsonResults);

// or dump the return directly
// var_dump($queryOracle->listPO('W-IGG',''));

You need to return the actual connection resource in the initConnection function

See the following inline comments

function initConnection(){
    $db = new db();
    // $dbConnection = $db->connect(); // this is not needed since you call connect from the 
                                    // constructor, but you need to return the connection
                                    // see below the EDIT
    // return the actual connection resource
    // return $dbConnection;
    return $db;
}

$sql = oci_parse($conn->initConnection(), 'SELECT VPI.PO_NO FROM VW_ ...')`

EDIT:

public function connect($dbconn = ORA_CON_DB, $dbuser = ORA_CON_UN, $dbpass = ORA_CON_PW){
    $this->conn = oci_connect($dbuser, $dbpass, $dbconn);
    return $this->conn;
}

I made an update to the answer. I changed a bit your initial db class. Also note that you don't really need a further wrapper on the connection class. See the comments for details.

Collectives™ on Stack Overflow

Querying JSON from PHP Function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related