Is there a more effective way to loop a prepared statements execute and get_result functions?

Question

I was trying to gather an unknown amount of data from my MySQL database to up load it on my webpage using PHP. But for some reason I kept getting ERR_CONNECTION_RESET in the browser whenever I tried to get more than 1 row of data.

//Connect to database
$Conn = Connect();

//Prepare statement
$SQL = $Conn->prepare("CALL GetProductByID (?)");
$SQL->bind_param("i", $ProductID);

//Loop to get data
foreach ($_SESSION['Cart'] as $ProductID) {
        $SQL->execute();
        $Result = $SQL->get_result();
        while ($Product = $Result->fetch_array(MYSQLI_ASSOC)) {
                //Show all items in cart
                echo '<div class="product"><h3>Namn: ' . $Product["Name"] . '</h3><p>Pris: ' . $Product["Price"] . ' kr</p><label for="' . $Product["ProductID"] . '">Antal: </label><input type="number" name="' . $Product["ProductID"] . '" placeholder="Antal" min="0" max="150" value="1" required></div>';
        }       
}
//Close all connections
$SQL->close();
$Conn->close();

The code above would work so long as it only ran the loop once, but as soon as it tried running it a second time the ERR_CONNECTION_RESET issue would appear.
Every time I would try to enter the page and the error occurred the following would be logged in Apaches error log:

[Thu Feb 03 21:56:08.649559 2022] [mpm_winnt:notice] [pid 20304:tid 568] AH00428: Parent: child process 36668 exited with status 3221225477 -- Restarting.
[Thu Feb 03 21:56:08.743608 2022] [ssl:warn] [pid 20304:tid 568] AH01909: www.example.com:443:0 server certificate does NOT include an ID which matches the server name
[Thu Feb 03 21:56:08.754618 2022] [mpm_winnt:notice] [pid 20304:tid 568] AH00455: Apache/2.4.51 (Win64) OpenSSL/1.1.1l PHP/8.0.12 configured -- resuming normal operations
[Thu Feb 03 21:56:08.754618 2022] [mpm_winnt:notice] [pid 20304:tid 568] AH00456: Apache Lounge VS16 Server built: Oct  7 2021 16:27:02
[Thu Feb 03 21:56:08.754618 2022] [core:notice] [pid 20304:tid 568] AH00094: Command line: 'f:\\program\\xampp\\apache\\bin\\httpd.exe -d F:/Program/XAMPP/apache'
[Thu Feb 03 21:56:08.756620 2022] [mpm_winnt:notice] [pid 20304:tid 568] AH00418: Parent: Created child process 25108
[Thu Feb 03 21:56:09.069307 2022] [ssl:warn] [pid 25108:tid 564] AH01909: www.example.com:443:0 server certificate does NOT include an ID which matches the server name
[Thu Feb 03 21:56:09.108307 2022] [ssl:warn] [pid 25108:tid 564] AH01909: www.example.com:443:0 server certificate does NOT include an ID which matches the server name
[Thu Feb 03 21:56:09.121319 2022] [mpm_winnt:notice] [pid 25108:tid 564] AH00354: Child: Starting 150 worker threads.

However, the code below would work without any issues.

//For some reason the connect and close needs to be inside the foreach loop

foreach ($_SESSION['Cart'] as $ProductID) {
        //Connect to database
        $Conn = Connect();

        //Prepare statement
        $SQL = $Conn->prepare("CALL GetProductByID (?)");
        $SQL->bind_param("i", $ProductID);
        $SQL->execute();
        $Result = $SQL->get_result();
        while ($Product = $Result->fetch_array(MYSQLI_ASSOC)) {
                //Show all items in cart
                echo '<div class="product"><h3>Namn: ' . $Product["Name"] . '</h3><p>Pris: ' . $Product["Price"] . ' kr</p><label for="' . $Product["ProductID"] . '">Antal: </label><input type="number" name="' . $Product["ProductID"] . '" placeholder="Antal" min="0" max="150" value="1" required></div>';
        }
        //Close all connections
        $SQL->close();
        $Conn->close();
}

My question is if there is a way to make my first solution work. It seems an awful waste to me to open and close the entire connection every loop and very much defeats the purpose of a prepared statement.

Can you try something like [this example](https://www.php.net/manual/en/mysqli.quickstart.stored-procedures.php#example-1409) in the manual where it uses `while ($SQL->next_result())` — Phil, Feb 03 '22 at 23:55
Could also try this, https://stackoverflow.com/a/19107074/296555. Don't forget to turn `autocommit` back on as the final comment suggests. — waterloomatt, Feb 04 '22 at 02:33
On a side note, running queries in a loop is generally seen as bad practice. It is more common to change your program to do something like, `... WHERE product_id IN (...)` as opposed to running multiple queries for each product. That said, sometimes it is justified to do it in a loop; just be aware that it could have a big performance impact. — waterloomatt, Feb 04 '22 at 02:35
@waterloomatt Can i just bind an array straight in as the parameter for ```IN(...) ``` or do i need to assemble it as a comma separated string first? — Huggorm, Feb 04 '22 at 18:31
You could do this https://stackoverflow.com/questions/8149545/pass-array-to-mysql-stored-routine, or if you store your carts in a database you could simply pass in the _cartId_ and let the stored procedure retrieve the cart items itself. The other option is to leave it as is and only change it when needed; ie. don't optimize prematurely. — waterloomatt, Feb 04 '22 at 19:05

Is there a more effective way to loop a prepared statements execute and get_result functions?

0 Answers0