PHP Shopping Cart – Setup Directory Structure and MYSQL Database

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:

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

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

php shopping cart basic folder structure

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


create database tshirt_cart

create database tshirt_cart

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


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
;

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


<?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/');

?>

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


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

<!DOCTYPE html>

<head>

</head>

<body>

<h1>

Cool T-Shirt Shop

</h1>

</div>

<a class="nav-link" href="index.php">Home <span class="sr-only">(current)</span></a>

</li>

</ul>

<?php

echo (isset($_SESSION['cart_items']) && count($_SESSION['cart_items'])) > 0 ? count($_SESSION['cart_items']):'';

</a>

</div>

</nav>

Footer.php


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

</div>

</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.

PHP Shopping Cart – Setup Directory Structure and MYSQL Database

Config.php

Header.php

Footer.php

More from WDB24.com

Published by Ahsan Zameer

Leave a Reply Cancel reply