PHP store, retrieve and edit an array from database

Question

I have a problem with arrays, I'm not so new in PHP but I have a little experience with manipulating complex arrays like one I need now.

I'm building shopping cart and my idea of cart is database row which every user will have and shopping process should go something like this

When user buys a product if product doesn't exist in the cart script should add product to the cart like e.g. 1=1 or in array words $cart = [ [1]=>'1' ] which means product ID1 quantity 1, now if product already exist in the cart script should just update quantity like e.g. 1=2 and so on for higher quantity.

This is my poor attempt to accomplish aforementioned

PHP

<?php session_start();

require_once('config.php');

$member_id = '1'; // Test data
$pr_id     = filter_input(INPUT_POST, 'pr_id', FILTER_SANITIZE_STRING); // Sending ID with jQuery/AJAX which works fine of course
$pr_qty    = '1'; // Start quantity
$up_qty    = '5'; // Test data

try {
  $getCart = $conn->prepare("SELECT cart FROM members WHERE member_id = :member_id ");
  $getCart->bindParam(':member_id', $member_id);
  $getCart->execute();
  $cart    = $getCart->fetch(PDO::FETCH_ASSOC);

  if(array_key_exists($pr_id, $cart)) {
     $cart[$pr_id] = $up_qty . ',';
  }
  else {
     $cart[$pr_id] = $pr_qty . ',';
  }

  $updateCart = $conn->prepare("UPDATE members SET cart = :cart WHERE member_id = :member_id ");
  $updateCart->bindParam(':member_id', $member_id);
  $updateCart->bindParam(':cart', implode($cart));

  if($updateCart->execute()) {
     echo '1';
  }

}
catch(PDOException $error) {
      echo 'Error: ' . $error->getMessage();
}

$conn = null;
?>

Later on I should be able to retrieve product ID's and quantities from database but when the time comes...

I'm working about three days on this and I'm hopeless, I searched everywhere, find many tutorials on multidimensional arrays but I think that my problem is a little unique or I'm just stupid enough and can't solve it.

Thanks everyone in advance, I hope we'll solve it together, more brains = more knowledge = solution ;)

@u_mulder It shows NULL but when I echo it with implode it shows 1,1,1, — Milan and Friends
– Milan and Friends, Commented Jun 7, 2014 at 17:50
Within your first try you've used variables within your prepare statement. Prepared statements (or at least in mySQLi::prepare) do not allow variable injection this way to prevent SQL injection so I believe. try using bindParam to add your variables to the statement. — bashleigh
– bashleigh, Commented Jun 7, 2014 at 17:55
@Beneto in the first statement I'm pulling cart from database and it works ok when I echo it with implode it shows data in the cart but array_key_exists doesn't work. — Milan and Friends
– Milan and Friends, Commented Jun 7, 2014 at 18:00

Adi Bradfield · Accepted Answer · 2014-06-08 03:04:47Z

1

If I understand your problem correctly, one way to implement this would be by having multiple database rows per cart, instead of encoding all the data into one. I would propose a database structure a little bit like this:

Database Diagram

This completely separates everything, so that editing and expanding is a lot simpler. For example, this way would make it really easy to allow users to have multiple carts, like Amazon does!

This simplifies the code a hell of a lot!

I won't write out the code for you, because I don't have the time, but I'll explain how it works a little bit, so you can have a go yourself!

To retrieve a cart:

You can get any carts belonging to a user by:

Getting the user id
Searching for a cart_id from the Cart table with that user id. If each user only has one, select the first one.
Select all the rows from the Cart_items table with that cart_id. This will give you each individual product in that cart
To get information on each product, select that products row from the Products table, using it's PID.

To add an item:

Get the cart ID as above
check if a row exists with both the ID of the product and ID of the cart
If there is, increase the quantity (see below)
If there isn't, create the row.

To edit items:

Get the cart id as above.
Get the Product id of the product you want to edit
Update that row of Cart_items with those 2 IDs to change the quantity etc.

I hope this helps!

edited Jun 8, 2014 at 3:04

answered Jun 8, 2014 at 2:55

Adi Bradfield

2,0931 gold badge17 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Milan and Friends Over a year ago

Thank you for such elaborate answer but it doesn't help me. Proposing a totally new db structure isn't good answer. I'm creating a simple shopping cart and don't want to complicate things with so many db tables just one cart row per user with simple ID=QTY product entry. Anyway that's my idea of cart structure for this project.

Azure · Accepted Answer · 2014-06-09 13:40:36Z

0

The implode function you use will not work for your cart, because the array indices get lost, so you lose the product IDs. But you can serialize or json_encode your shopping cart array.

$_SESSION['cart'] = array(
    1 => 1,
    6581 => 4
);

$json = json_encode($_SESSION['cart']);

Then you can store the $json string to your member table. When you want to fetch it from the database later, you have to json_decode it and put the function result in $_SESSION['cart'] again.

$_SESSION['cart'] = json_decode($json, true);

As you see, I have put your $cart variable into the $_SESSION. Thus you do not need to retrieve it again from the database when the user navigates on your site. This will reduce load on the database as well as speed up your script. For a shopping website it is really important that the website will load as fast as possible, so that the customers enjoy shopping and staying on the site.

To add a new product or to increase the quantity of an existing position simply do this:

function addProduct($id)
{
    if (isset($_SESSION['cart'][$id]))
        $_SESSION['cart'][$id]++;
    else
        $_SESSION['cart'][$id] = 1;
}

After calling addProduct you should json_encode and update the database to make the cart persistent.

edited Jun 9, 2014 at 13:40

answered Jun 7, 2014 at 17:52

Azure

4093 silver badges13 bronze badges

9 Comments

Milan and Friends Over a year ago

Ok but I need to edit/update array and show data based on cart id's.

Milan and Friends Over a year ago

I'll take a better look at this.

Milan and Friends Over a year ago

Sorry doesn't help...I need to store, retrieve, edit and store again, storing isn't much problem as edit in place and storing again.

Azure Over a year ago

That is pretty easy. Keep the $cart array in the session. When you alter something, simply do an update query. Use $_SESSION['cart'] = array(); instead of $cart = array();. Then you can work with your cart without the need to retrieve it again and again. When making a change to it, just do a json_encode and do an update.

Milan and Friends Over a year ago

I dont use $cart = array(); I'm pulling cart from database row so when user close the browser, go and come again the cart is intact. That's my problem with $_SESSION['cart']. I could put cart in session variable but I must pull cart data from database so session doesn't help much in my case. If you could provide an example for my exact situation with store, retrieve, edit...that would be helpfull.

|

Collectives™ on Stack Overflow

PHP store, retrieve and edit an array from database

2 Answers 2

To retrieve a cart:

To add an item:

To edit items:

1 Comment

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

To retrieve a cart:

To add an item:

To edit items:

1 Comment

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related