CODEWITHSUNDEEP
php
Jeromy French
Jeromy French

Reputation: 12121

Why isn't my local variable being overwritten?

I am trying to add some execution time reporting to the diagnostics I append to the end of my page. I have a function that handles database connectivity and query execution:

<?php
    // get_data.php
    function get_data($sql_statement, $sql_bind_variables=array(), $sql_result_type="cursor", $sql_result_cursor=":results_cursor") {
        $time_start = microtime(true);

        //tie-in to the existing $connection
        global $connection;

        global $debugging;
        if($debugging['show_debugging_ind']){
            global $variables;
            $variables['debugging_sql']=$sql_statement;
        };

        //since we have to determine if the sql result is packaged within a cursor multiple times, let's perform the slow text comparison once, and reference the result in multiple places.
        $cursor_ind=0;
        if ($sql_result_type=="cursor") {
            $cursor_ind=1;
        }
        if ($cursor_ind) {
            $returned_cursor = oci_new_cursor($connection);
            if (!$returned_cursor) {
                $e = oci_error($connection);
                trigger_error('
                <h2>Could not create new cursor:</h2>
                <div class="row-fluid">
                    <div class="span1">Connection</div>
                    <div class="span11"><pre>'.$connection.'</pre></div>
                </div>
                <div class="row-fluid">
                    <div class="span1">Message</div>
                    <div class="span11"><pre class="text-error">'.$e['message'],E_USER_ERROR.'</pre></div>
                </div>');
            }
        }

        $sql_parsed=oci_parse($connection, $sql_statement);
        if (!$sql_parsed) {
            $e = oci_error($connection);
            trigger_error('
                <div id="parse_error" class="clearfix">
                    <h2>Could not parse statement:</h2>
                    <div class="row-fluid">
                        <div class="span1">SQL</div>
                        <div class="span11"><pre>'.$sql_parsed.'</pre></div>
                    </div>
                    <div class="row-fluid">
                        <div class="span1">Message</div>
                        <div class="span11"><pre class="text-error">'.$e['message'],E_USER_ERROR.'</pre></div>
                    </div>
                </div>');
        }

        if ($cursor_ind) {
            oci_bind_by_name($sql_parsed, $sql_result_cursor, $returned_cursor, -1, OCI_B_CURSOR);
        }

        //loop over the array of bind variables
        foreach($sql_bind_variables AS $bind_variable_name => $bind_variable_value) {
//          echo "<p>processing ".$bind_variable_name.", value=".$sql_bind_variables[$bind_variable_name]."<br />";
//          echo "oci_bind_by_name(sql_parsed, ".$bind_variable_name.", ".$sql_bind_variables[$bind_variable_name].");</p>";
            oci_bind_by_name($sql_parsed, $bind_variable_name, $sql_bind_variables[$bind_variable_name]);
        }

        attempt_execute($sql_parsed);

        if ($cursor_ind) {
            oci_execute($returned_cursor);
            oci_fetch_all($returned_cursor, $sql_results, null, null, OCI_FETCHSTATEMENT_BY_ROW);
        }
        else {
            oci_fetch_all($sql_parsed, $sql_results, null, null, OCI_FETCHSTATEMENT_BY_ROW);
        }


        oci_free_statement($sql_parsed);
        if ($cursor_ind) {
            oci_free_statement($returned_cursor);
        }

        return $sql_results;

        if($debugging['show_debugging_ind']){
            $time_end = microtime(true);
            $variables['execution_time']=$time_end-$time_start;
        };
    }
?>

So this get_data() function gets called with each query, and if the $debugging['show_debugging_ind'] bit is flipped, I store some other information:

$get_my_cases=get_data('BEGIN pkg_common.get_cases(:results_cursor,p_type => :p_type,P_USER_ID => :P_USER_ID); END;', array(':p_type'=>'INBOX',':P_USER_ID'=>$variables['username']));
if($debugging['show_debugging_ind']){
    $debugging['queries']['get_my_cases']['sql']=$variables['debugging_sql'];
    $debugging['queries']['get_my_cases']['results']=$get_my_cases;
    $debugging['queries']['get_my_cases']['execution_time']=$variables['execution_time'];
};

$get_team_cases=get_data('BEGIN pkg_common.get_cases(:results_cursor, p_type => :p_type, P_USER_ID => :P_USER_ID, P_ASSIGNED_TO_LOC_CD => :P_ASSIGNED_TO_LOC_CD); END;',array(':p_type'=>'INBOX', ':P_USER_ID'=>$variables['username'], ':P_ASSIGNED_TO_LOC_CD'=>$variables['post']));
if($debugging['show_debugging_ind']){
    $debugging['queries']['get_team_cases']['sql']=$variables['debugging_sql'];
    $debugging['queries']['get_team_cases']['results']=$get_team_cases;
    $debugging['queries']['get_team_cases']['execution_time']=$variables['execution_time'];
};

In my diagnotics, the sql text ($variables['debugging_sql']) is getting updated and displaying great, but the $variables['execution_time'] time variable is either not getting set, or is getting the same value for all the queries. What am I doing wrong?

Upvotes: 0

Views: 69

Answers (1)

Jeromy French
Jeromy French

Reputation: 12121

Move the setting of $variables['execution_time'] to before the return statement:

<?php
    // get_data.php
    function get_data($sql_statement, $sql_bind_variables=array(), $sql_result_type="cursor", $sql_result_cursor=":results_cursor") {
        $time_start = microtime(true);

        //tie-in to the existing $connection
        global $connection;

        //since we have to determine if the sql result is packaged within a cursor multiple times, let's perform the slow text comparison once, and reference the result in multiple places.
        $cursor_ind=0;
        if ($sql_result_type=="cursor") {
            $cursor_ind=1;
        }
        if ($cursor_ind) {
            $returned_cursor = oci_new_cursor($connection);
            if (!$returned_cursor) {
                $e = oci_error($connection);
                trigger_error('
                <h2>Could not create new cursor:</h2>
                <div class="row-fluid">
                    <div class="span1">Connection</div>
                    <div class="span11"><pre>'.$connection.'</pre></div>
                </div>
                <div class="row-fluid">
                    <div class="span1">Message</div>
                    <div class="span11"><pre class="text-error">'.$e['message'],E_USER_ERROR.'</pre></div>
                </div>');
            }
        }

        $sql_parsed=oci_parse($connection, $sql_statement);
        if (!$sql_parsed) {
            $e = oci_error($connection);
            trigger_error('
                <div id="parse_error" class="clearfix">
                    <h2>Could not parse statement:</h2>
                    <div class="row-fluid">
                        <div class="span1">SQL</div>
                        <div class="span11"><pre>'.$sql_parsed.'</pre></div>
                    </div>
                    <div class="row-fluid">
                        <div class="span1">Message</div>
                        <div class="span11"><pre class="text-error">'.$e['message'],E_USER_ERROR.'</pre></div>
                    </div>
                </div>');
        }

        if ($cursor_ind) {
            oci_bind_by_name($sql_parsed, $sql_result_cursor, $returned_cursor, -1, OCI_B_CURSOR);
        }

        //loop over the array of bind variables
        foreach($sql_bind_variables AS $bind_variable_name => $bind_variable_value) {
//          echo "<p>processing ".$bind_variable_name.", value=".$sql_bind_variables[$bind_variable_name]."<br />";
//          echo "oci_bind_by_name(sql_parsed, ".$bind_variable_name.", ".$sql_bind_variables[$bind_variable_name].");</p>";
            oci_bind_by_name($sql_parsed, $bind_variable_name, $sql_bind_variables[$bind_variable_name]);
        }

        attempt_execute($sql_parsed);

        if ($cursor_ind) {
            oci_execute($returned_cursor);
            oci_fetch_all($returned_cursor, $sql_results, null, null, OCI_FETCHSTATEMENT_BY_ROW);
        }
        else {
            oci_fetch_all($sql_parsed, $sql_results, null, null, OCI_FETCHSTATEMENT_BY_ROW);
        }


        oci_free_statement($sql_parsed);
        if ($cursor_ind) {
            oci_free_statement($returned_cursor);
        };

        global $debugging;
        if($debugging['show_debugging_ind']){
            global $variables;
            $time_end = microtime(true);
            $variables['execution_time']=$time_end-$time_start;
            $variables['debugging_sql']=$sql_statement;
        };

        return $sql_results;

    }
?>

Upvotes: 2

Related Questions