asked on

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.

Chris Stanyon

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.

ASKER CERTIFIED SOLUTION

Julian Hansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Crazy Horse

ASKER

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.

Crazy Horse

ASKER

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

Crazy Horse

ASKER

Actually, I just noticed that answer was from you Chris :-)

Julian Hansen

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.

Chris Stanyon

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

Julian Hansen

@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?

Chris Stanyon

@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 ??

Julian Hansen

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.

Crazy Horse

ASKER

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

Crazy Horse

ASKER

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'

SOLUTION

Chris Stanyon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Crazy Horse

ASKER

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

Julian Hansen

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 ???

Chris Stanyon

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.

Crazy Horse

ASKER

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 :-)

Crazy Horse

ASKER

Thanks again guys, I really appreciate the help.

Julian Hansen

You are always welcome