Insert multiple cart items from session into database

I am trying to insert details from a cart session into a database. I need to pass the session data from the controller to the model.


This is the foreach loop in the controller:
				foreach($_SESSION['cart_array'] as $cart) {
					
					
					$data = [
						
						'prod_id' => $cart['prod_id'],
						'quantity' => $cart['quantity'],
						
					];
				}

Open in new window


The model :

public function UserItemsOrder($data)
	{
		$this->db->beginTransaction();
		
		try {
			
		
		$this->db->query("INSERT INTO `order_detail` (`product_id`, `product_qty`) VALUES (:product_id, :product_qty)");
		$this->db->bind(":product_id", $data['prod_id']);
		$this->db->bind(":product_qty", $data['quantity']);
		$this->db->execute();
			
		$this->db->commit();
		return true;
			
		}
		
		catch (Exception $e) {
			
			$this->db->rollBack();
			return false;
		}
	}

Open in new window


The current problem is that if I have 3 items in the cart, only 1 row is inserted into the database instead of 3. I don't know if there is a problem with what I am sending from the controller in that it isn't sending all the items through or because I also need a foreach loop in my database insert record. If I do need a foreach loop for my insert record, please could someone advise on how to do it as I am a bit stuck.
LVL 1
Black SulfurAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris StanyonWebDevCommented:
Nowhere in your code are you actually calling the UserItemsOrder() but it looks like you need to call the UserItemsOrder() within your foreach loop:

foreach($_SESSION['cart_array'] as $cart) {
    $data = [
        'prod_id' => $cart['prod_id'],
        'quantity' => $cart['quantity'],
    ];

    UserItemsOrder($data);
}

Open in new window

Don't know how you reference your model so you may need to change the way the UserItemsOrder() method is called.
0
Julian HansenCommented:
Lets look at your code
foreach($_SESSION['cart_array'] as $cart) {
  $data = [
    'prod_id' => $cart['prod_id'],
    'quantity' => $cart['quantity'],
  ];
}

Open in new window

Cart looks like this
array (
    array('prod_id' => 123, 'quantity' => 1),
    array('prod_id' => 456, 'quantity' => 2),
    array('prod_id' => 789, 'quantity' => 3),
);

Open in new window

First iteration data looks like this
$data = array (
   'prod_id' => 123,
   'quantity' => 1
)

Open in new window

Which by the way is identical to the $cart variable - you have effectively just renamed $cart to $data - might as well just use the $cart variable directly.

Then as Chris pointed out there is no call to the UserItemsOrder() function - which takes a single record at a time.
You could do this (assuming you need to pass all your data to the controller in one go)
$data = is_array($_SESSION['cart_array']) ? $_SESSION['cart_array'] : [];
UserItemsOrder($data);

Open in new window

Then in your UserItemsOrder Function
public function UserItemsOrder($data)
{
  $this->db->beginTransaction();

  try {
    foreach($data as $item) {
      $this->db->query("INSERT INTO `order_detail` (`product_id`, `product_qty`) VALUES (:product_id, :product_qty)");
      $this->db->bind(":product_id", $item['prod_id']);
      $this->db->bind(":product_qty", $item['quantity']);
      $this->db->execute();
    }
    
    $this->db->commit();
    return true;

  }
  catch (Exception $e) {
    $this->db->rollBack();
    return false;
  }
}

Open in new window

In other words your UserItemsOrder function expects an array of data arrays rather than a single item.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Black SulfurAuthor Commented:
Apologies, I was in a rush when I posted this question. UserItemsOrder() is used in the controller after the $data array.

if($this->ProductModel->UserItemsOrder($data) {
  // return true

Open in new window


Will try out your suggestions when I get home from my non-web development day job and give feedback.
0
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Black SulfurAuthor Commented:
Oh, and the reason I didn't try the foreach loop as Julian has it in the model when I was trying to do this, is because in the back of my mind I remembered from a previous question that the answer I received was as below. But this only worked when I had one item to loop, not multiple like now.

when preparing queries to be ran in a loop, you only need to prepare the query once and set up the bindings once. You should NOT be prepping and binding inside the loop - you lose all the performance gains of a prepared query if you do that.

// Prepare the Query
$this->db->query("INSERT INTO `accom_pics` (`accom_id`, `pic_name`) VALUES (:accom_id, :pic_name)");

// Bind the parameters
$this->db->bind(":accom_id", $data['id']);
$this->db->bind(":pic_name", $pic);

// Now loop through the data
foreach($data['pics'] as $pic) {
    $this->db->execute();
}

Open in new window

0
Black SulfurAuthor Commented:
Actually, I just noticed that answer was from you Chris :-)
0
Julian HansenCommented:
That is correct (assuming you bind to the right variables) - but go back to my post - you are not building an array of items with your loop.

Each iteration is overwriting the $data array with the next cart item - after the loop your version of the code only has ONE element in it - the last element in the cart.

That is why I suggested sending the $_SESSION value directly to the function - no need to move the data into another array.
0
Chris StanyonWebDevCommented:
I thought that sounded familiar :)

As Julian has pointed out, you can pass in the whole $cart array rather than re-assign the values to another array. The part about not preparing in the loop stands true, so you would need to adjust your UserItemsOrder() function:

public function UserItemsOrder($data)
{
    $this->db->beginTransaction();

    try {

        $this->db->query("INSERT INTO `order_detail` (`product_id`, `product_qty`) VALUES (:product_id, :product_qty)");
        $this->db->bind(":product_id", $item['prod_id']);
        $this->db->bind(":product_qty", $item['quantity']);

        foreach($data as $item) {
            $this->db->execute();
        }

        $this->db->commit();
        return true;
    }
    catch (Exception $e)
    {
        $this->db->rollBack();
        return false;
    }
}

Open in new window

0
Julian HansenCommented:
@Chris - not sure that is quite right

Shouldn't it look like this
public function UserItemsOrder($data)
{
    $this->db->beginTransaction();

    try {

        $this->db->query("INSERT INTO `order_detail` (`product_id`, `product_qty`) VALUES (:product_id, :product_qty)");
        $this->db->bind(":product_id", $prod_id);
        $this->db->bind(":product_qty", $quantity);

        foreach($data as $item) {
            $prod_id = $item['prod_id'];
            $quantity = $item['quantity'];
            $this->db->execute();
        }

        $this->db->commit();
        return true;
    }
    catch (Exception $e)
    {
        $this->db->rollBack();
        return false;
    }
}

Open in new window

The $item array does not exist at the bind - not sure PHP is going to create that correctly as it would with scalars?
1
Chris StanyonWebDevCommented:
@Julian - yeah ... my bad :)

I was thinking about an alternative way at the time and got things mixed up (not unusual!)

What I was aiming at was rather than re-assigning the values, just pass the array into the execute method. No need to then explicitly bind and re-assign the variables. Just check that the parameter names match the array keys:

$this->db->query("INSERT INTO `order_detail` (`product_id`, `product_qty`) VALUES (:prod_id, :quantity)");

foreach($data as $item) {
    $this->db->execute($item);
}

Open in new window

** Not entirely sure what DB driver is being used. It sort of looks like PDO but maybe with a wrapper as there's no prepare() and in PDO binding is done with bindParam or bindValue. Any ideas ??
0
Julian HansenCommented:
I am not sure - which is why I did not put anything in my original post about re-using the bind in the loop - we would need to know how the library you are using is going to  deal with that.
I am guessing it is PDO because of the named parameters - as far as I know MySQLi uses '?' only.
0
Black SulfurAuthor Commented:
Yep, using PDO. Here is the database class:

class Database {
	
	private $host = DB_HOST;
	private $user = DB_USER;
	private $pass = DB_PASS;
	private $dbname = DB_NAME;
	
	private $dbh;
	private $stmt;
	private $error;
	
	public function __construct() {
		$dsn = "mysql:host=$this->host;dbname=$this->dbname";
		$options = array(
			PDO::ATTR_PERSISTENT => true,
			PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
		);
		
		try {
			$this->dbh = new PDO($dsn, $this->user, $this->pass, $options);
			
		} catch(PDOexception $e) {
			$this->error = $e->getMessage();
			echo $this->error;
		}
	}
	
	// Prepare statement with query
	public function query($sql) {
		$this->stmt = $this->dbh->prepare($sql);
	}
	
	// Bind values (added & before $value)
	public function bind($param, &$value, $type = null) {
		if(is_null($type)) {
			switch(true) {
				case is_int($value):
					$type = PDO::PARAM_INT;
					break;
				case is_bool($value):
					$type = PDO::PARAM_BOOL;
					break;
				case is_null($value):
					$type = PDO::PARAM_NULL;
					break;
				default:
					$type = PDO::PARAM_STR;
			}
		}
		
//		$this->stmt->bindValue($param, $value, $type);
		$this->stmt->bindParam($param, $value, $type);
	}
	
	// Execute the prepared statement
	public function execute() {
		return $this->stmt->execute();
	}
	
	// Get result set as array object
	public function resultSet() {
		$this->execute();
		return $this->stmt->fetchAll(PDO::FETCH_OBJ);
	}
	
	// Get single record as object
	public function single() {
		$this->execute();
		return $this->stmt->fetch(PDO::FETCH_OBJ);
	}
	
	// Get row count
	public function rowCount() {
		return $this->stmt->rowCount();
	}
	
	// Get last id
	public function lastId() {
		return $this->dbh->lastInsertId();
	}
	
	public function beginTransaction() {
		return $this->dbh->beginTransaction();
	}
	
	public function rollBack() {
		return $this->dbh->rollBack();
	}
	
	public function commit() {
		return $this->dbh->commit();
	}
}

Open in new window

0
Black SulfurAuthor Commented:
I understand what you are saying about that fact that $data and $cart are the same thing, but I don't know actually know how to do it.

if(isset($_SESSION['cart_array'])) {

foreach($_SESSION['cart_array'] as $cart) {


	$cart = [

		'prod_id' => $cart['prod_id'],
		'quantity' => $cart['quantity'],
		'sale_price' => $cart['sale_price']

	         ];
}

if($this->CustomerModel->UserItemsOrder($cart)) {

	$response['success'] = true;
	$response['message'] = 'it worked!';

}

Open in new window


I then tried both:

      
public function UserItemsOrder($cart)
	{
		$this->db->beginTransaction();
		
		try {
			
		
		$this->db->query("INSERT INTO `order_detail` (`product_id`, `product_qty`) VALUES (:product_id, :product_qty)");
        $this->db->bind(":product_id", $prod_id);
        $this->db->bind(":product_qty", $quantity);

        foreach($cart as $item) {
            $prod_id = $item['prod_id'];
            $quantity = $item['quantity'];
            $this->db->execute();
        }
			
		$this->db->commit();
		return true;
			
		}
		
		catch (Exception $e) {
			
			$this->db->rollBack();
			return false;
		}
	}

Open in new window


and:

public function UserItemsOrder($data)
{
    $this->db->beginTransaction();

    try {

        $this->db->query("INSERT INTO `order_detail` (`product_id`, `product_qty`) VALUES (:product_id, :product_qty)");
        $this->db->bind(":product_id", $prod_id);
        $this->db->bind(":product_qty", $quantity);

        foreach($data as $item) {
            $prod_id = $item['prod_id'];
            $quantity = $item['quantity'];
            $this->db->execute();
        }

        $this->db->commit();
        return true;
    }
    catch (Exception $e)
    {
        $this->db->rollBack();
        return false;
    }
}

Open in new window


And I get the same error for both which is:

Illegal string offset 'prod_id'
Illegal string offset 'quantity'
0
Chris StanyonWebDevCommented:
OK. In your first block of code, you're iterating over the loop, and just creating a new variable called $cart inside of it. By the time you've finished your loop, the $cart variable will be a simple array containing the last record. It's only after you've finished the loop that you try and insert the data into the database. It will fail because the method needs an array of arrays.

You should just be pasing the whole $cart array into your method and allowing that to do loop, so your first code block should look like this:

if(isset($_SESSION['cart_array'])) {

    if($this->CustomerModel->UserItemsOrder($_SESSION['cart_array'])) {
        $response['success'] = true;
        $response['message'] = 'it worked!';
    }

}

Open in new window

Now your UserItemsOrder() method will recieve the $_SESSION['cart'] array so you can loop through each item in the array and insert into your DB.
1
Black SulfurAuthor Commented:
Hmm. Okay, great. This is working.

But, I apologize for being thick but even though it is working, I still don't fully understand something.

Normally, I would create $data = []; and put whatever I want in there to send to the model.

I now have no $data value but am sending the entire $_SESSION['cart_array'] instead of $data

if($this->CustomerModel->UserItemsOrder($_SESSION['cart_array'])) {

Open in new window


So, how is it that the database query still works when I am using $data even though I never send $data from the controller?

public function UserItemsOrder($data)
{
    $this->db->beginTransaction();

    try {

        $this->db->query("INSERT INTO `order_detail` (`product_id`, `product_qty`, `product_price`) VALUES (:product_id, :product_qty, :product_price)");
        $this->db->bind(":product_id", $prod_id);
        $this->db->bind(":product_qty", $quantity);
		$this->db->bind(":product_price", $sale_price);

        foreach($data as $item) {
            $prod_id = $item['prod_id'];
            $quantity = $item['quantity'];
			$sale_price = $item['sale_price'];
            $this->db->execute();
        }

        $this->db->commit();
        return true;
    }
    catch (Exception $e)
    {
        $this->db->rollBack();
        return false;
    }
}

Open in new window

0
Julian HansenCommented:
See my first post
A safer way of getting the cart - in case it is not defined on the session.
$data = is_array($_SESSION['cart_array']) ? $_SESSION['cart_array'] : [];

Open in new window


Normally, I would create $data = []; and put whatever I want in there to send to the model.

What is the difference between the two?
$_SESSION['cart_array'] is an array - it is passed by reference so irrespective of whether there are more fields than you need you are still only passing a reference to it.

This vs iterating through the cart just to make another array that has data that is also in $_SESSION['cart_array'] gains you ???
0
Chris StanyonWebDevCommented:
When you define a function, you specify the name of the arguments that function will use inside of itself so you could have a function like this:

public function SayHello($name) {
     echo "Hello " . $name;
}

inside the function, whatever is passed in will be referred to by the variable called $name.

Now when you call this function, it doesn't matter what you call the variable outside of it, or whether you even use a variable:

SayHello("Chris");

$me = "chris";
SayHello($me);

It only cares that once you're inside the function, the data passed in is called $data.
0
Black SulfurAuthor Commented:
Sorry Julian, I seem to have missed this:

$data = is_array($_SESSION['cart_array']) ? $_SESSION['cart_array'] : [];

Open in new window


Just ran it now and works great. Sorry for frustrating you guys! Now that I have time to read properly, I can tell that you repeated yourself a few times. At least I finally get it now :-)
0
Black SulfurAuthor Commented:
Thanks again guys, I really appreciate the help.
0
Julian HansenCommented:
You are always welcome
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.