For loop in Session Array in Update SQL Statement

Hey,

I am trying to update a table based on values from a session array in a shopping basket.

When i add to cart i have the following method:


    public function AddToCart(){
        !isset($_SESSION['ID']) ? $_SESSION['ID'] = array() : '';
        !isset($_SESSION['theName']) ? $_SESSION['theName'] = array() : '';
        !isset($_SESSION['quantity']) ? $_SESSION['quantity'] = array() : '';
        !isset($_SESSION['price']) ? $_SESSION['price'] = array() : '';
        !isset($_SESSION['image']) ? $_SESSION['image'] = array() : '';

        array_push($_SESSION['ID'], $_POST['ID']);
        array_push($_SESSION['theName'], $_POST['theName']);
        array_push($_SESSION['quantity'], $_POST['quantity']);
        array_push($_SESSION['price'], $_POST['price']);
        array_push($_SESSION['image'], $_POST['image']);
        $loc = $_SERVER['HTTP_REFERER'];
        echo "<script>window.location.href='".$loc."'</script>";
    }

Now when someone confirms an order i need to update the stock list to mange the stock control. Therefore i need to update the stock value based on the quantities of the items in the basket.

So i tried doing this:


    public function deductQuantitiesFromItems(){
        $i=0;
        session_start();
        foreach($_SESSION['ID'] as $ID):
        $sql = 'UPDATE hussaini_items SET
                    stock = stock - '.$_SESSION['quantity'][$i].' 
                    WHERE ID = '.$ID;
            $result = $this->mysqli->query($sql) or die($this->mysqli->query($sql));
            return $result;
        $i++;
        endforeach;
    }

I get this error:

Warning: Invalid argument supplied for foreach() in G:\xampp\htdocs\Manstore\Models\Cart.class.php on line 121

Why doesn’t the update work?

Thanks again

I’m actually echoing inside the loop:


    public function deductQuantitiesFromItems(){
        session_start();
        foreach($_SESSION['ID'] as $i => $ID):
        $sql = 'UPDATE hussaini_items SET
                    stock = stock - '.$_SESSION['quantity'][$i].'
                   WHERE ID = '.$ID;
           echo "<p>$sql</p><p>$ID</p>";
           $result = $this->mysqli->query($sql) or die($this->mysqli->query($sql));
           return $result;
        endforeach;
    }

I will try to have a play around and see where i get… :confused:


    public function deductQuantitiesFromItems(){

        session_start();

        foreach($_SESSION['ID'] as $i => $ID):

        $sql = 'UPDATE hussaini_items SET

                    stock = stock - '.$_SESSION['quantity'][$i].'

                   WHERE ID = '.$ID;

           echo "<p>$sql</p>";

          /* $result = $this->mysqli->query($sql) or die($this->mysqli->query($sql));

           return $result;*/

        endforeach;

    } 

Ah sorry, well i now echo the sql and i get this:

UPDATE hussaini_items SET stock = stock - 1 WHERE ID = 53

And i echo out $ID and i only get one value of ‘53’.

Sorry about this :confused:

So it doesn’t seem to be working for me :frowning:

PHP stops as soon as the die() function is executed, preventing the loop to be processed completely.
Try echo instead of die() and comment the query to confirm it does work.

Ok i have removed the values and now have this:


    public function deductQuantitiesFromItems(){
        session_start();
        foreach($_SESSION['ID'] as $i => $ID):
        $sql = 'UPDATE hussaini_items SET
                    stock = stock - '.$_SESSION['quantity'][$i].'
                   WHERE ID = '.$ID;
           die ("<p>$sql</p>");
           $result = $this->mysqli->query($sql) or die($this->mysqli->query($sql));
           return $result;
        endforeach;
    }

But the $sql still only outputs one update statement although there is more than one item in the session?? the sql i get is this:

UPDATE hussaini_items SET stock = stock - 4 WHERE ID = 52

Hey,

Firstly thanks for replying.

In regards to the dump, i get this:

array(1) { [0]=> string(2) “51” }

Does this mean i am not submitting the form properly? I’m slightly confused so i am showing you my code for that also below:


<?php
$view->pageTitle = 'Order Summary';
require_once('Models/Cart.class.php');
$cart = new Cart('localhost', 'root', '', 'test');

if(isset($_POST['post']) && $_POST['post'] == 'post'):
    session_start();
    $cart->deductQuantitiesFromItems();
    $cart->emptyCart();
    header('location: order-confirmation');
endif;

require_once('Views/order-summary.phtml');

So as you can see I do the deductions and then empty the cart sending the user to the order confirmation page.

Can you see a problem?

Thanks again.

You need to remove $i=0 and $i++ when using foreach($_SESSION[‘ID’] as $i => $ID);

Alright, now you know the loop is actually running. So now see if the query actually works by running it directly in the MySQL client. Copy and paste the query and run it it directly in terminal or PHPMyAdmin – whatever client you use.


    public function deductQuantitiesFromItems(){

        $i=0;

        session_start();

        foreach($_SESSION['ID'] as $ID):

        $sql = 'UPDATE hussaini_items SET

                    stock = stock - '.$_SESSION['quantity'][$i].' 

                    WHERE ID = '.$ID;
           
           echo "<p>$sql</p>";
           /*$result = $this->mysqli->query($sql) or die($this->mysqli->query($sql));

            return $result;*/

        $i++;

        endforeach;

    }

That would be because you are returning inside the loop. That means that only a single iteration is occurring. No duplicate key update needed unless you want to update all stocks using a single query. If that is the case than there is a little creativity involved in that using a select and dynamic SQL case statement.

Well $_SESSION[‘ID’] apparently isn’t an array.

try:


    public function deductQuantitiesFromItems(){

        $i=0;

        session_start();

        var_dump($_SESSION['ID']);
        return;

        foreach($_SESSION['ID'] as $ID):

        $sql = 'UPDATE hussaini_items SET

                    stock = stock - '.$_SESSION['quantity'][$i].' 

                    WHERE ID = '.$ID;

            $result = $this->mysqli->query($sql) or die($this->mysqli->query($sql));

            return $result;

        $i++;

        endforeach;

    } 

That will tell you if any data is even there. If not than the problem has something with handling the form submission.

Are you echo’ing $sql inside the loop, or after the loop?
If you’re echoing it after the loop what you’re describing make sense, since that’s the last value $sql has.

FYI. Note that my first post in this thread was off-topic. What I’m showing you here is not the answer to your question, just to show you a better programming style.

Also i have tried your method ScallioXTX. However it only updates one row, if there is more than one item to update then it doesn’t update.

I have this code:


    public function deductQuantitiesFromItems(){
        $i=0;
        session_start();
        foreach($_SESSION['ID'] as $i => $ID):
        $sql = 'UPDATE hussaini_items SET
                    stock = stock - '.$_SESSION['quantity'][$i].'
                   WHERE ID = '.$ID;
           die ("<p>$sql</p>");
           $result = $this->mysqli->query($sql) or die($this->mysqli->query($sql));
           return $result;
        $i++;
        endforeach;
    }

I got this sql:

UPDATE hussaini_items SET stock = stock - 2 WHERE ID = 52

However there was another item in the basket. So as Oddz has suggested do i need a duplicate key updater?

If not how can i fix this?

Thanks again

Hmm, well thanks for the tip, i think because i’m not aware of the possible threats like injection, i haven’t thought about hidden values as being a problem. But now that you have mentioned it i will need to rectify and strengthen my existing code :goof:

For now i will get this first problem fixed, get the site fully working and then change bits at the end, i know this is the long way round but i think :wink:

Off Topic:

Since you’re only using array_push to populate the array and the indices are thus zero-based integers, you can rewrite


$i=0;
foreach($_SESSION['ID'] as $ID):
   // sql code here
   $i++;
endforeach;

to


foreach($_SESSION['ID'] as $i => $ID):
   // sql code here
endforeach;

I always try to avoid implementing counters myself, as I have found that it’s quite prone to errors (forget to increment, increment in the wrong place, etc).

Check the hussaini_items table in the MySQL client to see if the stock is decreasing. If not run a single query and see if it changes than.

Hidden values are still exposed to the front-end. The only thing hidden does is hide the visual display of them. That doesn’t prevent me from injecting my own values through cURL or some other method. What you have is HUGE security hole with that method.

Conceptually I would recommend handling the cart similar to the below. Where you load the cart, get the product data and add/update it in the session.


/*
* Example only
*/

/*
* Initialize cart
*/
$_SESSION['cart'] = isset($_SESSION['cart'])?$_SESSION['cart']:array();

/*
* Load product data
*/
$product = fetch_product($_POST['ID']);

/*
* Add/Update cart
*/
$_SESSION['cart'][$product['ID']] = array(
	'name'=>$product['name']
	,'qty'=>isset($_SESSION['cart'][$product['ID']])?$_SESSION['cart'][$product['ID']]['qty']+$_POST['qty']:$_POST['qty']
	,'price'=>$product['price']
	,'image'=>$product['image']
);

Ah i see. When i add to cart i actually have the information as hidden values in my form so when i view source it shows me this depending on the item showing:

    &lt;input type="hidden" name="ID" value="51" /&gt;
    &lt;input type="hidden" name="theName" value="Diors" /&gt;
    &lt;input type="hidden" name="price" value="69.99" /&gt;
    &lt;input type="hidden" name="image" value="king-stud1.jpg" /&gt;
    &lt;input type="hidden" name="quantity" value="1" /&gt;

Is this still not good?

Anyway, back to the original issue, this is the table:

CREATE TABLE hussaini_items (
ID int(11) NOT NULL AUTO_INCREMENT,
CID int(11) NOT NULL,
name varchar(200) NOT NULL,
description longtext NOT NULL,
price varchar(200) NOT NULL,
image varchar(200) NOT NULL,
image_1 varchar(200) NOT NULL,
image_2 varchar(200) NOT NULL,
date_added datetime NOT NULL,
stock int(11) NOT NULL,
deleted int(11) NOT NULL,
PRIMARY KEY (ID)
) ENGINE=MyISAM AUTO_INCREMENT=52 DEFAULT CHARSET=latin1

OK, before we get to that I actually took a look at your shopping cart processing method above and have one major concern with the logic. That concern is that since the price is being populated from the form it is possible to inject a fake price for a product. That is not good since I could supply the ID of a product that costs $1000 and supply a price of $1.00.

What you should consider doing instead is using the ID to select price, name and image. You should not expose that information on the front-end through the form. Given a product ID all that info can be resolved on the back-end while avoiding any major security gaps. The worst someone can do than is supply a fake product ID.

Now in regards to your problem I believe what you need is a insert with a duplicate key update. However, to make sure its not something else I would like to see the table. So run this query inside your MySQL client and post the result.


SHOW CREATE TABLE hussaini_items;