FOREACH loop within WHILE loop Getting Run Multiple Times - PHP

Question

I am attempting to run a mysqli query via php that will update an item's quantity based on what was selected. So if a user chooses two quantity of an item, that quantity should go down by two. I can successfully do this without issue with this code:

$postparts = $_POST['part_name']; <-- This is an array of the items chosen
$postqty = $_POST['qty']; <-- This is an array of the qty of each item chosen

$combine = array_combine($postparts, $postqty); <-- I combine the arrays to match the qty to the item that was chosen.  

foreach ($combine as $part=>$qty)
    {
        $update = <<<SQL
UPDATE `Houston` SET `qty` = (`qty`-?) WHERE `partnumber` = ?;
SQL;
        $result = $mysqli->prepare($update);
        $result->bind_param("is", $qty, $part);
        $result->execute();
    }

The above code will subtract the appropriate qty from the item chosen - no issue here. However, I need to implement an additional check so that if two people were to check out the same item at the same time, the first would get a successful result, but if the qty of the item is then 0 due to the first checkout process, the second person would get a failure. I tried doing that like this:

$postparts = $_POST['part_name'];
$postqty = $_POST['qty'];

$partarray = array();
foreach ($postparts as $part => $id)
{
    $finalpart = ($postparts[$part]);
    $stuff = explode(',', $finalpart);
    $partarray = array_merge($partarray, $stuff);
}

$combine = array_combine($postparts, $postqty);
$in = implode(',', array_fill(0, count($partarray), '?'));
$check = <<<SQL
SELECT * FROM `Houston` WHERE `partnumber` in ($in);
SQL;

$statcheck = $mysqli->prepare($check);
$statcheck->bind_param(str_repeat('s', count($partarray)), ...$partarray);
$statcheck->execute();
$statcheck->store_result();
$statcheck->bind_result($id, $partcat, $partnumber, $partqty);
while ($statcheck->fetch())
{
    if ($partqty > 0)
    {
        foreach ($combine as $part=>$qty)
        {
            $update = <<<SQL 
UPDATE `Houston` SET `qty` = (`qty`-?) WHERE `partnumber` = ?;
SQL;
            $result = $mysqli->prepare($update);
            $result->bind_param("is", $qty, $part);
            $result->execute();
        }
   }
   if ($partqty == 0)
   {
        header("location:error.php");
   }
}

So this "almost" works - except that the foreach loop is getting run multiple times. Say if the user checks out two items - then the foreach loop gets run twice as many times - so quantities ordered end up doubling for each item (if 3 items, then quantities are tripled, etc). I suspect the reason for this behavior is because I am running the foreach loop within the while loop, but I am not sure how I can fix this to make it work properly. Anyone know how?

after a quick look I think you don't need the foreach at all!? You're itererating over the same dataset twice. — Jeff, Jul 27 '15 at 18:48
You are probably right, but then how do I go about doing the mysqli update? I need to make sure that the correct quantity amount is getting subtracted from the correct item. I thought about doing the same thing with qty that I did with the part and putting that in the while loop...I guess I should try that, but not sure how I bind_param two variables with str_repeat? Maybe I'll have to go the PDO route? — Wes, Jul 27 '15 at 18:53

Your Common Sense · Answer 1 · 2015-07-27T19:56:40.107

0

You't taking whole idea of prepared statements totally wrong. As name suggests, you have to prepare only once.

Also, there is no point in selecting data only to update it. Whatever you need to check, have to be checked by means of SQL query.

$combine = array_combine($postparts, $postqty);
$sql = "UPDATE Houston SET qty = qty-?) WHERE partnumber=? AND qty > 0";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("is", $qty, $part);
foreach ($combine as $part=>$qty)
{
    $stmt->execute();
}

I need to implement an additional check so that if two people were to check out the same item at the same time, the first would get a successful result, but if the qty of the item is then 0 due to the first checkout process, the second person would get a failure.

You need table locking for this. If you indeed need such a check.

To see if update were succesfull or not use PDOStatement::rowCount()

edited Jul 27 '15 at 19:56

answered Jul 27 '15 at 18:56

Your Common Sense

156,878
40
214
345

Ah...this makes it so much simpler. Thanks! I will try this. – Wes Jul 27 '15 at 18:59
So this sort of works, but how do I direct the user to an error page if the qty is already set to 0 when they do a checkout attempt? – Wes Jul 27 '15 at 19:55
2

@Wes - Redirecting is a different question entirely. If you can't find the [answer you're looking for](http://stackoverflow.com/q/768431/477563) in an already existing question, feel free to start your own. (If someone else later on has a similar question, they won't know to check the comments on this specific Q&A, so it does no good for anybody except you for it to be answered here.) – Mr. Llama Jul 27 '15 at 20:04

score -1 · Accepted Answer · answered Jul 27 '15 at 21:50

I was able to find a solution that works, though not sure if it's the most elegant one. If anyone has a better idea please let me know.

$postparts = $_POST['part_name'];
$postqty = $_POST['qty'];
$combine = array_combine($postparts, $postqty);

$update = <<<SQL
UPDATE `Houston` SET `qty` = (`qty`-?) WHERE `partnumber` = ?;
SQL;
$result = $mysqli->prepare($update);
$result->bind_param("is", $qty, $part);

$check = <<<SQL
SELECT * FROM `Houston` WHERE `partnumber` = ?;
SQL;
$checkpart = $mysqli->prepare($check);
$checkpart->bind_param('s', $part);

foreach ($combine as $part=>$qty)
{
    $checkpart->execute();
    $checkpart->store_result();
    $checkpart->bind_result($id, $partcat, $partnumber, $partqty);

    if ($checkpart->fetch())
    {
        if ($partqty > 0)
        {
            $result->execute();
        }
        elseif ($partqty == 0)
        {
            header("location:error.php");
        }
    }
}

FOREACH loop within WHILE loop Getting Run Multiple Times - PHP

2 Answers2