Shopping cart tutorial with php and MySQL

In this shopping cart tutorial, we will design a database to store the data from the shopping cart application. This database will store product data, products purchase data, products sales data, consumer data and admin data. All the data will be stored in related or relational tables in one database. We will use the PHP programming language and MySQL database. For the local server, we will use Xampp, for how to install it, you can see here.

1. Create a Database in MySQL

Open MySQL phpmyadmin and create a database with the name “db_bukawarung” or you can use the command:

CREATE DATABASE `db_bukawarung` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */

2. Create Tables in MySQL

After we have created the database, now we will create the tables needed to hold the shopping cart application data later.

2.1 Create table tbl_produk

The “tbl_produk” table functions to save our product data, run the code below to create the “tbl_produk” table

CREATE TABLE `tbl_produk` (
  `id_produk` int(11) NOT NULL AUTO_INCREMENT,
  `nama_produk` varchar(60) COLLATE utf8_unicode_ci NOT NULL,
  `kategori` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `keterangan` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `harga` double NOT NULL,
  `photo` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `stok` tinyint(4) NOT NULL,
  PRIMARY KEY (`id_produk`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

2.2 Create table tbl_kategori

The tbl_kategori table functions to save category data from products and tbl_kategori relates to tbl_produk, so the tbl_produk table has a relationship with tbl_kategori. Run the code below to create tbl_kategori

CREATE TABLE `tbl_kategori` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `photo` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

2.3 Create table tbl_pembelian

The tbl_pembelian table functions to save product purchase data, run the code below to create a tbl_pembelian

CREATE TABLE `tbl_pembelian` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `no_nota` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `tbl_beli` date NOT NULL,
  `id_toko` int(11) NOT NULL,
  `total` double NOT NULL,
  `keterangan` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

2.4 Create table tbl_toko

The tbl_toko table functions to save shop or supplier data, the code is as below

CREATE TABLE `tbl_toko` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nama` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `alamat` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `telphone` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

2.5 Create table tbl_pesanan

The tbl_pesanan table functions to save sales data, run the code below to create the tbl_pesanan table

CREATE TABLE `tbl_pesanan` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_pelanggan` int(11) NOT NULL,
  `nama_pelanggan` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `alamat_pelanggan` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `email_pelanggan` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `tlp_pelanggan` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `total_belanja` double NOT NULL,
  `metode_bayar` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `statusnya` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `tanggal` date NOT NULL,
  `keterangan` varchar(300) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

2.6 Create table tbl_detail_pesanan

The tbl_detail_pesanan table functions to save sales details, run the code below to create the tbl_detail_pesanan table

CREATE TABLE `tbl_detail_pesanan` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_pesanan` int(11) NOT NULL,
  `id_produk` int(11) NOT NULL,
  `nama_produk` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `detail_produk` varchar(300) COLLATE utf8_unicode_ci NOT NULL,
  `qty_pesanan` int(11) NOT NULL,
  `hrg_produk` double NOT NULL,
  `sub_total` double NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

2.7 Create table tbl_member

The tbl_member table functions to save the data of member, run the code below to create the tbl_member table

CREATE TABLE `tbl_member` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nama` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `alamat` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `phone` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `kata_sandi` varchar(300) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

2.8 Create table users

The users table functions to save the data of user, run the code below to create the users table

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci