Site icon WDB24

PHP Shopping Cart – Setup Directory Structure and MYSQL Database

Ahsan Zameer
PHP

This is my second tutorial in PHP shopping cart series. In this post we will setup directory structure and mysql database. I will also create database table and will insert records. So let’s start.

First I need to create following 3 folders:

  1. Assets Folder: This folder has javascript, css and product images
  2. Inc Folder: This folder has config and helper file
  3. Layouts: This folder has header and footer file

Once I created all of the three folders, my root folder is look something like this


Next we have to setup database and mysql tables. First let’s create a database

1
2
3
 
create database tshirt_cart
 

Now let’s create a products, product_images, order and order_details table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
 
CREATE TABLE `products` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`product_name` VARCHAR(255) NULL DEFAULT NULL,
`product_slug` VARCHAR(255) NULL DEFAULT NULL,
`short_description` VARCHAR(255) NULL DEFAULT NULL,
`full_description` TEXT NULL,
`price` DOUBLE(4,2) NULL DEFAULT NULL,
`is_featured` TINYINT(1) NULL DEFAULT '0',
`is_active` TINYINT(1) NULL DEFAULT '0',
`created_at` DATETIME NULL DEFAULT NULL,
`updated_at` DATETIME NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=6
;
 
CREATE TABLE `product_images` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`product_id` INT(11) NULL DEFAULT NULL,
`img` VARCHAR(255) NULL DEFAULT NULL,
`display_order` INT(11) NULL DEFAULT NULL,
`is_featured` TINYINT(1) NULL DEFAULT '0',
PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;
 
#Insert Products
INSERT INTO `products` (`id`, `product_name`, `product_slug`, `short_description`, `full_description`, `price`, `is_featured`, `is_active`, `created_at`, `updated_at`) VALUES (1, 'Black T-shirt', 'black-tshirt', 'Lorem ipsum dolor sit amet consectetur adipisicing elit. Perspiciatis omnis suscipit esse ipsam officia. Quis sint nihil magnam explicabo veniam hic. Vitae nam iusto reiciendis ratione sed suscipit, aspernatur repudiandae.', 'Lorem ipsum dolor sit amet consectetur adipisicing elit. Perspiciatis omnis suscipit esse ipsam officia. Quis sint nihil magnam explicabo veniam hic. Vitae nam iusto reiciendis ratione sed suscipit, aspernatur repudiandae.\r\n\r\nLorem ipsum dolor sit amet consectetur adipisicing elit. Perspiciatis omnis suscipit esse ipsam officia. Quis sint nihil magnam explicabo veniam hic. Vitae nam iusto reiciendis ratione sed suscipit, aspernatur repudiandae.\r\n\r\nLorem ipsum dolor sit amet consectetur adipisicing elit. Perspiciatis omnis suscipit esse ipsam officia. Quis sint nihil magnam explicabo veniam hic. Vitae nam iusto reiciendis ratione sed suscipit, aspernatur repudiandae.', 9.50, 0, 1, '2021-02-11 22:02:17', '2021-02-11 22:02:21');
INSERT INTO `products` (`id`, `product_name`, `product_slug`, `short_description`, `full_description`, `price`, `is_featured`, `is_active`, `created_at`, `updated_at`) VALUES (2, 'Blue T-shirt', 'blue-tshirt', 'Lorem ipsum dolor sit amet consectetur adipisicing elit. Perspiciatis omnis suscipit esse ipsam officia. Quis sint nihil magnam explicabo veniam hic. Vitae nam iusto reiciendis ratione sed suscipit, aspernatur repudiandae.', 'Lorem ipsum dolor sit amet consectetur adipisicing elit. Perspiciatis omnis suscipit esse ipsam officia. Quis sint nihil magnam explicabo veniam hic. Vitae nam iusto reiciendis ratione sed suscipit, aspernatur repudiandae.', 9.50, 0, 1, '2021-02-11 22:02:50', '2021-02-11 22:02:53');
INSERT INTO `products` (`id`, `product_name`, `product_slug`, `short_description`, `full_description`, `price`, `is_featured`, `is_active`, `created_at`, `updated_at`) VALUES (3, 'Maroon T-shirt', 'maroon-tshirt', 'Lorem ipsum dolor sit amet consectetur adipisicing elit. Perspiciatis omnis suscipit esse ipsam officia. Quis sint nihil magnam explicabo veniam hic. Vitae nam iusto reiciendis ratione sed suscipit, aspernatur repudiandae.', 'Lorem ipsum dolor sit amet consectetur adipisicing elit. Perspiciatis omnis suscipit esse ipsam officia. Quis sint nihil magnam explicabo veniam hic. Vitae nam iusto reiciendis ratione sed suscipit, aspernatur repudiandae.', 9.50, 0, 1, '2021-02-11 22:03:21', '2021-02-11 22:03:24');
INSERT INTO `products` (`id`, `product_name`, `product_slug`, `short_description`, `full_description`, `price`, `is_featured`, `is_active`, `created_at`, `updated_at`) VALUES (4, 'Orange T-shirt', 'orange-tshirt', 'Lorem ipsum dolor sit amet consectetur adipisicing elit. Perspiciatis omnis suscipit esse ipsam officia. Quis sint nihil magnam explicabo veniam hic. Vitae nam iusto reiciendis ratione sed suscipit, aspernatur repudiandae.', 'Lorem ipsum dolor sit amet consectetur adipisicing elit. Perspiciatis omnis suscipit esse ipsam officia. Quis sint nihil magnam explicabo veniam hic. Vitae nam iusto reiciendis ratione sed suscipit, aspernatur repudiandae.', 9.50, 0, 1, '2021-02-11 22:03:50', '2021-02-11 22:03:53');
 
#Insert Product Images
INSERT INTO `product_images` (`id`, `product_id`, `img`, `display_order`, `is_featured`) VALUES (1, 1, 'black-tshirt-1.jpg', 1, 1);
INSERT INTO `product_images` (`id`, `product_id`, `img`, `display_order`, `is_featured`) VALUES (2, 1, 'black-tshirt-2.jpg', 2, 0);
INSERT INTO `product_images` (`id`, `product_id`, `img`, `display_order`, `is_featured`) VALUES (3, 1, 'black-tshirt-3.jpg', 3, 0);
INSERT INTO `product_images` (`id`, `product_id`, `img`, `display_order`, `is_featured`) VALUES (4, 1, 'black-tshirt-4.jpg', 4, 0);
INSERT INTO `product_images` (`id`, `product_id`, `img`, `display_order`, `is_featured`) VALUES (5, 2, 'blue-tshirt-1.jpg', 1, 1);
INSERT INTO `product_images` (`id`, `product_id`, `img`, `display_order`, `is_featured`) VALUES (6, 2, 'blue-tshirt-2.jpg', 2, 0);
INSERT INTO `product_images` (`id`, `product_id`, `img`, `display_order`, `is_featured`) VALUES (7, 3, 'maroon-tshirt.jpg', 1, 1);
INSERT INTO `product_images` (`id`, `product_id`, `img`, `display_order`, `is_featured`) VALUES (8, 4, 'orange-tshirt-1.jpg', 1, 1);
 
CREATE TABLE `orders` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`first_name` VARCHAR(255) NOT NULL,
`last_name` VARCHAR(255) NOT NULL,
`email` VARCHAR(255) NOT NULL,
`address` VARCHAR(255) NOT NULL,
`address2` VARCHAR(255) NULL DEFAULT NULL,
`country` VARCHAR(255) NOT NULL,
`state` VARCHAR(255) NOT NULL,
`zipcode` VARCHAR(255) NULL DEFAULT NULL,
`total_price` FLOAT(6,2) NOT NULL DEFAULT '0.00',
`order_status` VARCHAR(255) NOT NULL,
`created_at` DATETIME NULL DEFAULT NULL,
`updated_at` DATETIME NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;
 
CREATE TABLE `order_details` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`order_id` INT(11) NULL DEFAULT NULL,
`product_id` INT(11) NULL DEFAULT NULL,
`product_name` VARCHAR(50) NULL DEFAULT NULL,
`product_price` FLOAT(6,2) NULL DEFAULT NULL,
`qty` INT(11) NULL DEFAULT NULL,
`total_price` DOUBLE(6,2) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;
 

We have successfully created database and mysql tables. Now let’s create a database connection in php file. Go to inc folder and create config.php and add PHP PDO connection.

Config.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
<?php
$dsn = 'mysql:dbname=tshirt_cart;host=localhost';
$user = 'root';
$password = '';
 
try
{
$db = new PDO($dsn,$user,$password);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e)
{
echo "PDO error".$e->getMessage();
die();
}
 
define('PRODUCT_IMG_URL','assets/product-images/');
 
?>
 

I have createed config.php. Now I will be creating helpers.php file in inc folder. Helpers.php file will have custom php functions which I will use later.

Now create a header.php and footer.php file in layouts folder.

Header.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title><?php echo (isset($pageTitle)?$pageTitle:'PHP Shopping Cart')?></title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css" integrity="sha384-MCw98/SFnGE8fJT3GXwEOngsV7Zt27NXFoaoApmYm81iuXoPkFOJwJ8ERdknLPMO" crossorigin="anonymous">
    
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.3.0/font/bootstrap-icons.css">
</head>
<body>
   <div class="container">
        <div class="row mt-2 mb-2">
            <div class="col-md-12 col-xs-12">
                <h1>
                    Cool T-Shirt Shop
                </h1>
            </div>
        </div>
        
        <nav class="navbar navbar-expand-lg navbar-dark bg-primary">
            <ul class="navbar-nav mr-auto mt-2 mt-lg-0"">
                <li class="nav-item active">
                    <a class="nav-link" href="index.php">Home <span class="sr-only">(current)</span></a>
                </li>
            </ul>
            <div class="form-inline my-2 my-lg-0">
                <a href="cart.php" style="color:#ffffff">
                    <i class="bi bi-cart4" style="font-size:30px;"></i>
                    <?php
                        echo (isset($_SESSION['cart_items']) && count($_SESSION['cart_items'])) > 0 ? count($_SESSION['cart_items']):'';
                    ?>
                </a>
            </div>
            
        </nav>
 

Footer.php

1
2
3
4
5
6
7
8
9
 
</div>
<script src="https://code.jquery.com/jquery-3.5.1.min.js" integrity="sha256-9/aliU8dGd2tb6OSsuzixeV4y/faTqgFtohetphbbj0=" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.3/umd/popper.min.js" integrity="sha384-ZMP7rVo3mIykV+2+9J3UJ46jBk0WLaUAdn689aCwoqbBJiSnjAK/l8WvCWPIPm49" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/js/bootstrap.min.js" integrity="sha384-ChfqqxuZUCnJSK3+MXmPNIyE6ZbWh2IMqE241rYiqJxyMiZ6OW/JmZQ5stwEULTy" crossorigin="anonymous"></script>    
</body>
 
</html>
 

So now, We have setup our basic shopping cart structure and setup database. In the next tutorial I will show you how get product from database and display.


Exit mobile version