PHP - MySQL - Foreach loop bottleneck

Question

I have working code but I seem to have introduced a bad bottleneck to my code. Before I go into detail, let me give you some background on what this code does. I have a table generated in PHP/HTML that shows a list of parts that are in a database (149 parts). The user can select a quantity for that item and click "Add To Cart" which all works fine.

However, I tried to spruce things up a bit by adding in a feature that only allows the user to select the quantity we actually have on hand for this product (before add this feature I previously had the max value hard coded at 100).

Now this does work, but it is TERRIBLY slow (50 seconds to load the page). However, before implementing this the page loaded immediately without a problem, therefore this is definitely a problem with the getPartQuantityOnHand() function.

I think the problem is that for every part (149) I am calling this function that then has to reconnect to the database to find the correct quantity. Does anyone see a way I can improve this? FYI, the parts are hosted on a completely separate database from where the quantity on hand for these parts are hosted.

I have left some parts out of the php code since it was unnecessary bulk for this post, I know that I am missing parts here.

getParts.php

        <?php
          $partList = $_SESSION['controller']->getParts();

          foreach ($partList as $part) {

            //Call to get the current quantity on hand for this specific part
            //THIS IS WHEN THE PROBLEM WAS INTRODUCED
            $quantityOnHand = $_SESSION['controller']->getPartQuantityOnHand($part->number);

            //Only display this part if we have at least one on hand
            if ($quantityOnHand > 0)
            {
              echo "<tr>";
                echo "<td>" . $part->number . "</td>";
                echo "<td>" . $part->description . "</td>";
                echo "<td>&#36;" . $part->price . "</td>";
                echo "<td>";
                   echo '<input name="qty'. $part->number .'" type="number" value="1" min="1" max="' . $quantityOnHand .'"/>';
                echo "</td>";
                  echo "</form>";
                echo "</td>";

              echo "</tr>";
            }
          }

          echo "</table>";
      echo "</div>";
    ?>

getPartQuantityOnHand()

    public function getPartQuantityOnHand($partNum) {
        $conn = $this->connect();

        $sql = "select Quantity from ReceivingInfo where PartNumber = '$partNum'";
        $stmt = $conn->prepare($sql);
        $stmt->execute();

        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        $quantityOnHand = $row['Quantity'];

        return $quantityOnHand;
    }

Thanks for any help you can provide!

Let's say you had an array of all the part numbers. How about 1 call to the routine that does a get onHand where id in (a,b,c,d). The result set contains partId,onHand. At this point, you pit two PHP arrays against one another, combining them. — Drew
– Drew, Commented Apr 29, 2016 at 3:05
@Drew Hmm.. I haven't ever tried anything like this but I can definitely give it a shot. Thanks for your input. — trb
– trb, Commented Apr 29, 2016 at 3:18

Scopey · Accepted Answer · 2016-04-29 03:21:14Z

1

As the two databases are separate you have two options:

Determine the part numbers before you loop and generate your HTML, then query all at the same time with an IN clause:
```
SELECT ... WHERE PartNumber IN (1, 2, 3, 4);
```

Select all stock levels for your parts when you first call getPartQuantityOnHand and store that:

public function getPartQuantityOnHand($partNum) {
    if(!$this->partQuantitiesOnHand) {
        $conn = $this->connect();

        $sql = "select Quantity, PartNumber from ReceivingInfo";
        $stmt = $conn->prepare($sql);
        $stmt->execute();

        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $this->partQuantitiesOnHand = array_column($rows, 'Quantity', 'PartNumber');
    }

    return $this->partQuantitiesOnHand[$partNum] ?? null;
}

The disadvantage of option 2 is that if you have many more parts than are listed on a single page, it will never perform as well as option 1.

answered Apr 29, 2016 at 3:21

Scopey

6,3291 gold badge24 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

trb Over a year ago

Thanks, I ended up using option 2 and it works great. My loading time is back to normal!

BizzyBob · Accepted Answer · 2016-04-29 03:20:48Z

So, instead of doing a 149 different SQL queries, just do one query that brings back all the Quantity values you need.

ex.

SELECT Quantity FROM ReceivingInfo WHERE PartNumber IN ($listOfPartNums)

of course you will have to build the string $listOfPartNums

Or, if you really are always bringing back the full list of them, you can exclude the IN clause and don't need to worry about generating the string.

You'll have to change your function a bit to store the partnum/quantity pairs in an associative array.

Then in your getParts.php get the Quantity like this:

  <?php
      $partList = $_SESSION['controller']->getParts();
      $quantityList = getQtyList(); // return assoc array of partnum/qty pairs

      foreach ($partList as $part) {

        //Call to get the current quantity on hand for this specific part
        //THIS IS WHEN THE PROBLEM WAS INTRODUCED
        $quantityOnHand = $quantityList[$part->number];

        //Only display this part if we have at least one on hand
        if ($quantityOnHand > 0)
        {
          echo "<tr>";
            echo "<td>" . $part->number . "</td>";
            echo "<td>" . $part->description . "</td>";
            echo "<td>&#36;" . $part->price . "</td>";
            echo "<td>";
               echo '<input name="qty'. $part->number .'" type="number" value="1" min="1" max="' . $quantityOnHand .'"/>';
            echo "</td>";
              echo "</form>";
            echo "</td>";

          echo "</tr>";
        }
      }

      echo "</table>";
  echo "</div>";
?>

Collectives™ on Stack Overflow

PHP - MySQL - Foreach loop bottleneck

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related