From 1bb67296c43b662cf00882971fcb3df655d7302b Mon Sep 17 00:00:00 2001 From: lonkaars Date: Sun, 21 May 2023 16:18:05 +0200 Subject: add/remove from cart working --- db/data.sql | 16 +++++++----- db/functions.sql | 30 +++++++++++++++++++++ db/init.sql | 21 +++++++++++---- db/makefile | 2 +- lib/login.php | 7 ++--- public/cart.php | 77 ++++++++++++++++++++++++++++++++---------------------- public/product.php | 5 ++-- 7 files changed, 109 insertions(+), 49 deletions(-) create mode 100644 db/functions.sql diff --git a/db/data.sql b/db/data.sql index 01f18aa..e6ac757 100644 --- a/db/data.sql +++ b/db/data.sql @@ -27,14 +27,16 @@ insert into webs.user (`name`, `hash`) values ("willem", "$2b$12$vCDpn5fnGBL7dv3Ty1cgZegDKOguoRIgHNrUFYOCWoensgI4HnJde"); -- biege update webs.user set `privileges` = 1073741824 where `name` = "loek"; -insert into webs.cart (`product`, `user`) values - (4, 1), - (5, 1), - (6, 1), - (7, 1), - (8, 1); - insert into webs.promotion (`product`, `price_buff`) values (1, 0.80), -- 20% korting (2, 0.80), (3, 0.80); + +set @order_id = webs.cart(1); -- cart id voor loek +insert into webs.orderproduct (`product`, `count`, `order`) values + (4, 1, @order_id), + (5, 1, @order_id), + (6, 1, @order_id), + (7, 1, @order_id), + (8, 1, @order_id); + diff --git a/db/functions.sql b/db/functions.sql new file mode 100644 index 0000000..995e7e9 --- /dev/null +++ b/db/functions.sql @@ -0,0 +1,30 @@ +drop function if exists webs.cart; +drop function if exists webs.add_to_cart; + +delimiter $$ +create function webs.cart(user_id int) -- get current order for user_id (cart order id) +returns int +begin + set @order_id = (select id from webs.order where status = 1 and user = user_id); + if @order_id is not null then + return @order_id; + end if; + insert into webs.order (`user`) values (user_id); + set @order_id = (select id from webs.order where status = 1 and user = user_id); + return @order_id; +end$$ + +create function webs.add_to_cart(product_id int, user_id int) +returns boolean +begin + set @orderproduct_id = (select id from webs.orderproduct where product = product_id and `order` = cart(user_id)); + if @orderproduct_id is not null then + update orderproduct set count = count + 1 where id = @orderproduct_id; + return true; + end if; + insert into webs.orderproduct (`product`, `count`, `order`) values (product_id, 1, cart(user_id)); + return false; +end$$ + +delimiter ; + diff --git a/db/init.sql b/db/init.sql index b7b3310..df7aa30 100644 --- a/db/init.sql +++ b/db/init.sql @@ -33,19 +33,30 @@ create table if not exists webs.user ( primary key (`id`) ); -create table if not exists webs.cart ( +create table if not exists webs.order ( `id` int not null auto_increment, - `product` int not null, + `status` int not null default 1, `user` int not null, + primary key (`id`), + constraint `order_user_fk` + foreign key (`user`) + references webs.user (`id`) + on update cascade +); + +create table if not exists webs.orderproduct ( + `id` int not null auto_increment, + `product` int not null, `count` int not null default 1, + `order` int not null, primary key (`id`), constraint `cart_product_fk` foreign key (`product`) references webs.product (`id`) on update cascade, - constraint `cart_user_fk` - foreign key (`user`) - references webs.user (`id`) + constraint `cart_order_fk` + foreign key (`order`) + references webs.order (`id`) on update cascade ); diff --git a/db/makefile b/db/makefile index 0802fa6..59d9fc2 100644 --- a/db/makefile +++ b/db/makefile @@ -18,7 +18,7 @@ data: data.sql full: full.sql $(SQL) < $< -base.sql: reset.sql init.sql +base.sql: reset.sql init.sql functions.sql cat $^ > $@ full.sql: base.sql data.sql diff --git a/lib/login.php b/lib/login.php index 0683031..05caf17 100644 --- a/lib/login.php +++ b/lib/login.php @@ -39,9 +39,10 @@ function check_login($username, $password) { } function get_cart_count() { - global $username, $cursor; - $statement = $cursor->prepare("select sum(cart.count) as count from cart join user on user.id = cart.user join product on product.id = cart.product where user.name = ?"); - $statement->bind_param("s", $username); + global $user_id, $cursor; + if (!$user_id) return 0; + $statement = $cursor->prepare("select ifnull(sum(count), 0) as count from webs.orderproduct where `order` = webs.cart(?)"); + $statement->bind_param("i", $user_id); if (!$statement->execute()) return 0; $res = $statement->get_result(); if (!mysqli_num_rows($res)) return 0; diff --git a/public/cart.php b/public/cart.php index 34cccbb..d4dfcc6 100644 --- a/public/cart.php +++ b/public/cart.php @@ -2,24 +2,31 @@ -prepare("select id, image, price, name, description from webs.product where id = ?"); - // $statement->bind_param("i", $_GET['id']); - // if (!$statement->execute()) refuse(); - // $res = $statement->get_result(); - // if (!mysqli_num_rows($res)) refuse(); - // $product = $res->fetch_object(); - - - // if all guards passed, successful login occurred - cookie_redir($_POST['username'], $_POST['password']); -} while (false); -?> + switch($_POST['type']) { + case "delete": { + $statement = $cursor->prepare("delete from orderproduct where product = ? and `order` = cart(?)"); + $statement->bind_param("ii", $_POST['product_id'], $user_id); + $statement->execute(); + break; + } + case "add": { + // ik wou deze functie eigenlijk in een stored procedure doen maar het + // schijnt dat de knappe koppen bij mysql het geen goed idee vonden om + // gewoon 'return' toe te staan binnen de body van een stored + // procedure??? + $statement = $cursor->prepare("select add_to_cart(?, ?)"); + $statement->bind_param("ii", $_POST['product_id'], $user_id); + $statement->execute(); + $statement->get_result()/*->fetch_object()*/; + break; + } + } +} while (false); ?> image ? "/img/product/$item->id-thumb.jpg" : "/img/placeholder.png"; @@ -28,8 +35,8 @@ function item_template($item) { productafbeelding $item->name - - + + $item->price EOF; @@ -45,20 +52,28 @@ EOF;

dingen in de mand van

-
- prepare("select product.id, product.name, product.price, product.image, cart.count from cart join user on user.id = cart.user join product on product.id = cart.product where user.name = ?"); - $statement->bind_param("s", $username); - if (!$statement->execute()) break; - $res = $statement->get_result(); - if (!mysqli_num_rows($res)) { - echo "mandje leeg"; - break; - } - while ($product = $res->fetch_object()) item_template($product); - } while (false); ?> -
+ prepare("select product.id, product.name, product.price, product.image, orderproduct.count from orderproduct join product on product.id = orderproduct.product where `order` = cart(?)"); + $statement->bind_param("i", $user_id); + if (!$statement->execute()) break; + $res = $statement->get_result(); + if (!mysqli_num_rows($res)) { + echo "mandje leeg"; + break; + } + echo <<<"EOF" +
+ + EOF; + while ($product = $res->fetch_object()) item_template($product); + echo <<<"EOF" +
+
+ +
+ EOF; + } while (false); ?>
diff --git a/public/product.php b/public/product.php index 70c511d..18cc936 100644 --- a/public/product.php +++ b/public/product.php @@ -38,8 +38,9 @@ $product = $res->fetch_object(); price ?>

description ?>

- - + + +
-- cgit v1.2.3