raminshahab Newbie Poster

I have three tables that I query. Problem is my ORACLE MIF table has 500,000 rows. And it takes more than 1 hour.

How can I reduce the time?

SELECT WB_FIELD_REQ.MODEL, WB_FIELD_REQ.MFG_SERIAL_NUMBER, CONFIG_SERIAL_NUMBER, SH_CONTRACTS.EQUIPMENT_ID, "", SH_CONTRACTS.SERVICE, "", SH_CONTRACTS.CONTRACT, "X", "", "X" 
FROM WB_FIELD_REQ 
INNER JOIN SH_CONTRACTS ON WB_FIELD_REQ.MFG_SERIAL_NUMBER=SH_CONTRACTS.MFG_SERIAL_NUMBER 
NOT IN (SELECT DISTINCT MFG_SERIAL_NUMBER FROM ORACLE_MIF WHERE PROJECT_NUMBER = "701") 
AND WB_FIELD_REQ.DEVICE="701"

raminshahab

@matrixdevuk @jsuna @veedeoo Thanks guys! Big help. After fiddling with the nested data I was able to extract it all.

raminshahab

@matrixdevuk Gives me a NOTICE Undefined offset 0:

To me it looks like it has more than 2 levels once rendering with the brower as @jsuna mentioned.

raminshahab

foreach($json_info as $key => $value)
{

 print_r($value);

}

Gives me back same assoc array??

raminshahab Newbie Poster

Array ( [data] => Array ( [0] => Array ( [ucsfeduworkingdepartmentname] => Array ( [0] => ITS ) [telephonenumber] => Array ( [0] => +1 415 502-7575 ) [ucsfeduprofilenodeid] => Array ( [0] => 39487740 ) [displayname] => Array ( [0] => Kevin Dale ) [postaladdress] => Array ( [0] => Box 0272 1855 Folsom Street, MCB Room 401S San Francisco, CA 94143 ) [uid] => Array ( [0] => 88834 ) [ucsfeduprimarydepartmentnumber] => Array ( [0] => 411112 ) [ucsfeduworkingtitle] => Array ( [0] => Sr Manager, Identity Mgmt ) [mobile] => Array ( [0] => +1 415 806-8480 ) [roomnumber] => Array ( [0] => 401S ) [mail] => Array ( [0] => kevin.dale@ucsf.edu ) [box] => Array ( [0] => Box 0272 ) [baseaddress] => Array ( [0] => 1855 Folsom Street San Francisco, CA 94143 ) [primary] => Array ( [box] => Array ( [0] => Box 0272 ) [building] => Array ( [0] => MCB ) [baseaddress] => Array ( [0] => 1855 Folsom Street San Francisco, CA 94143 ) [postaladdress] => Array ( [0] => Box 0272 1855 Folsom Street, MCB Room 401S San Francisco, CA 94143 ) [cn] => Array ( [0] => Campus ) [ucsfeduaddressprimaryflag] => Array ( [0] => true ) [roomnumber] => Array ( [0] => 401S ) [telephonenumber] => Array ( [0] => +1 415 502-7575 ) [ucsfedusecondarytelephonenumber] => Array ( [0] => ) [ucsfedutelephonenumberreleasecode] => Array ( [0] => ) [ucsfedusecondarytelephonenumberreleasecode] => Array ( [0] => ) ) ...

raminshahab

Thanks for the help! Found the issue. As @pritaeus suggested mysqli doesn't catch exceptions. Now Im getting return values.

raminshahab

Thanks, I tried the semi-colon and replaced with older mysql connector. Still same problem.

raminshahab Newbie Poster

I can't seem to get a response from my query method. I have a database connection from method ping_server and use mysqli as connector. Can anyone help me figure out why I cannot print results from mysqli_fetch_array.

dbclass.php

class db
 {

    private $connection;
    private $selectdb;
    private $lastQuery;
    private $config;

    function __construct($config)
    {
        $this->dbConfig = $config;
    }

    function __destruct()
    {

    }

    public function openConn()
    {

        try
        {
            if($this->dbConfig->connector == "mysqli")
            {
                $this->connection = mysqli_connect($this->dbConfig->hostname, $this->dbConfig->username, $this->dbConfig->password);
                $this->selectdb = mysqli_select_db($this->dbConfig->database,$this->connection);

            }
        }   

        catch(exception $e)
        {
            echo'Caught Exception:', $e->getMessage(), "\n";
        }       

    }

    public function closeConn()
    {
        try 
        {
            if($this->dbConfig->connector == "mysqli")
            {
                mysqli_close($this->connection);
            }
        }
        catch(exception $e)
        {
            echo'Caught Exception:', $e->getMessage(), "\n";
        }

    }

    public function query($query)
    {
        //$query = str_replace("}", "", $query);
        //$query = str_replace("{", $this->dbConfig->prefix, $query);

       try 
       {
           if(empty($this->connection))
           {

                $this->openConn();
                $this->lastQuery = mysqli_query($this->connection, $query); 

                $this->closeConn();
                return $this->lastQuery;
            }
            else 
            {
                echo '2nd';

                $this->lastQuery = mysqli_query($this->connection, $query);
               // $this->closeConn();
                echo $lastQuery;
                return $this->lastQuery;

            }
       }

       catch(exception $e)
       {

        echo'Caught Exception:', $e->getMessage(), "\n";

       }
    }

    #get last query 
    public function lastQuery()

    {

        return $this->lastQuery;

    }

    #check to see if we have a connection
    public function pingServer()
    {

        try 
        {
            if($this->dbConfig->connector == "mysqli")
            {
                if(!mysqli_ping($this->connection))
                {

                    return false;

                }
                else
                {

                    return true;

                }

            }       

        }
        catch(exception $e)

        {
            echo'Caught Exception:', $e->getMessage(), "\n";

        }

    }

    public function fetchArray($result)

    {
        try
        {
            if($this->dbConfig->connector == "mysqli")
            {

                return mysqli_fetch_array($result,MYSQL_ASSOC);

            }
            else
            {
                return mysqli_fetch_array($result,MYSQL_ASSOC);

            }

        }
        catch(exception $e)
        {

            echo'Caught Exception:', $e->getMessage(), "\n";

        }

    }

 }

dbfile.php

<?php 

include ('dbclass.php');

#dbConfig object with database data ...

raminshahab

Never mind I solved it myself.

raminshahab Newbie Poster

My issue is this: I call this method twice once inside a function and another in the HTML onclick. Reason is the DIV is part of a context menu. The first function call res is the correct variable I want. The second however changes res b/c Im forced to pass something as res. How do I either keep the value of the first function value of res and have the second invoke but not change res?