Trevor Trevor - 2 years ago 92
SQL Question

PHP - MySQL - Foreach loop bottleneck

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


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.


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

foreach ($partList as $part) {

//Call to get the current quantity on hand for this specific part
$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>";


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

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

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

$quantityOnHand = $row['Quantity'];

return $quantityOnHand;

Thanks for any help you can provide!

Answer Source

As the two databases are separate you have two options:

  1. 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);
  2. 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);
            $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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download