CODEWITHSUNDEEP
phpwebodbc
Nick Roberts
Nick Roberts

Reputation: 245

(PHP) How to Prevent Discarding of an ODBC Result Set?

So I have a web app that I'm trying to create where I have to use odbc_exec to gather the results of two different queries and then create a JSON file with combined info from the two queries.

Example below (connection and query omitted)...

$result = odbc_exec($c, $q);
$result1 = odbc_exec($c, $q1);
$resultRows = array();
$response = array();

while($row = odbc_fetch_array($result)) {
    $tempResult = $result1;
    $value = "0";
    $other = $row['FIELD'];
    while($row1 = odbc_fetch_array($tempResult)) {
        if($row['FIELD'] == $row1 ['FIELD']) {
            $value = $row1['FIELD'];
        }
    }
    if($value != "0") {
        $resultRows[] = array('FIELD'=>$value, 'OTHER'=>$other);
    }
}

$response['data'] = $resultRows;

$fp = fopen('somefile.json', 'w');
fwrite($fp, json_encode($response));
fclose($fp);

The problem with this is that it stops going into the nested while loop after the first loop through. I know that odbc_fetch_array removes the data from the results set which is why I attempted to create a reference to the result set that resets after each big loop, but that still doesn't resolve my problem.

Any info would be extremely helpful! Thanks in advance!

Upvotes: 2

Views: 232

Answers (1)

Jeff Puckett
Jeff Puckett

Reputation: 40971

$tempResult = $result1; does not make a deep copy of the object, just a copy by reference to the original object, so when you later call odbc_fetch_array($tempResult) it's really the same thing as odbc_fetch_array($result1) which means you only ever have one object. So any subsequent calls to odbc_fetch_array will be exhausted on either variable. You could clone the object each time, but I think a much more efficient approach would be to iterate through it once and save the values to an array. Then you could re-iterate over the array in your nested loop.

$result = odbc_exec($c, $q);
$result1 = odbc_exec($c, $q1);
$resultRows = array();
$response = array();

// save this to a regular array for re-use later
$innerQueryResult = array();
while($rowTemp = odbc_fetch_array($result1)) {
    $innerQueryResult []= $rowTemp;
}

while($row = odbc_fetch_array($result)) {
    $value = "0";
    $other = $row['FIELD'];

    // iterate through the inner query result set
    foreach ($innerQueryResult as $row1) {
        if($row['FIELD'] == $row1 ['FIELD']) {
            $value = $row1['FIELD'];
        }
    }
    if($value != "0") {
        $resultRows[] = array('FIELD'=>$value, 'OTHER'=>$other);
    }
}

$response['data'] = $resultRows;

$fp = fopen('somefile.json', 'w');
fwrite($fp, json_encode($response));
fclose($fp);

Upvotes: 1

Related Questions