Here's a simple question.
I have worked on simple and small projects before where the performance didn't matter much, or at least it wasn't affected by repeating the same code twice.
I was following a bad practice whenever I needed to run a query and display the data on a webpage. I first ran the query and checked if it returned any results, if nrows > 0 then I would run the same query again and fetch the results using a while:

while ($row = oci_fetch_array ($stmt, OCI_BOTH)) {...}

(Of course, on the first query I would just retrieve one field on the database, not everything that I needed)
I really like using a while because it gives me flexibility to display the data wherever and whenever I want using

$row[i]

I am currently working on a larger project using an Oracle database, and a simple query can be pretty slow because the DB is pretty big, so now performance really matters.
I am running the query and then checking for the number of rows retrieved:

$stmt = oci_parse($conn, $query);
oci_execute ($stmt);
$nrows = oci_fetch_all($stmt, $results);

Then, if I get nrows > 0 I would use a foreach to display the results:

foreach ($results as $key => $val) {
echo '<th>' . $key . '</th>';
}

I don't really like this approach because the foreach loop doesn't give me the flexibility sometimes I need.

Question: In your opinion, what would be the best (faster, simpler, best performance and efficient) way of running a query, checking if it returns any results and then displaying the data without a foreach loop?

    If you really need to take different actions depending on whether the result set retrieved is empty or not, then two options come to mind:

    Option 1:

    if ($row = oci_fetch_array ($stmt, OCI_BOTH)) {
    do {
        // ...
    } while ($row = oci_fetch_array ($stmt, OCI_BOTH));
} else {
    // No rows in the result set.
}

    Option 2:

    $empty_result_set = true;
while ($row = oci_fetch_array ($stmt, OCI_BOTH)) {
    $empty_result_set = false;
    // ...
}

if ($empty_result_set) {
    // No rows in the result set.
}

      thanks laserlight. I think this will help.

        You're welcome 🙂
        Remember to mark this thread as resolved (if it is) using the thread tools.

          9 days later
          laserlight;10881670 wrote:

          If you really need to take different actions depending on whether the result set retrieved is empty or not, then two options come to mind:

          Option 1:

          if ($row = oci_fetch_array ($stmt, OCI_BOTH)) {
    do {
        // ...
    } while ($row = oci_fetch_array ($stmt, OCI_BOTH));
} else {
    // No rows in the result set.
}

          Option 2:

          $empty_result_set = true;
while ($row = oci_fetch_array ($stmt, OCI_BOTH)) {
    $empty_result_set = false;
    // ...
}

if ($empty_result_set) {
    // No rows in the result set.
}

          Sorry, one more question. What if I need to know the number of rows returned by the query before the do while?

            Write a Reply...