wCart Tutorial part 2. Database Preparation

ben · November 2, 2019, 12:40am

For the wCart example we will need a database with tables for Products, Users, Orders and Order Details.

To make it easier for you, you can copy the following SQL for your database. I used it to create the database within my Docker project.

-- MySQL dump 10.13  Distrib 8.0.17, for Linux (x86_64)
--
-- Host: localhost    Database: wapplercart
-- ------------------------------------------------------
-- Server version	8.0.17

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `orderdetails`
--

DROP TABLE IF EXISTS `orderdetails`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `orderdetails` (
  `DetailID` int(11) NOT NULL AUTO_INCREMENT,
  `DetailOrderID` int(11) NOT NULL,
  `DetailProductID` int(11) NOT NULL,
  `DetailName` varchar(250) CHARACTER SET latin1 COLLATE latin1_german2_ci NOT NULL,
  `DetailPrice` float NOT NULL,
  `DetailSKU` varchar(50) CHARACTER SET latin1 COLLATE latin1_german2_ci NOT NULL,
  `DetailQuantity` int(11) NOT NULL,
  PRIMARY KEY (`DetailID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `orderdetails`
--

LOCK TABLES `orderdetails` WRITE;
/*!40000 ALTER TABLE `orderdetails` DISABLE KEYS */;
/*!40000 ALTER TABLE `orderdetails` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `orders`
--

DROP TABLE IF EXISTS `orders`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `orders` (
  `OrderID` int(11) NOT NULL AUTO_INCREMENT,
  `OrderUserID` int(11) NOT NULL,
  `OrderAmount` float NOT NULL,
  `OrderShipName` varchar(100) CHARACTER SET latin1 COLLATE latin1_german2_ci NOT NULL,
  `OrderShipAddress` varchar(100) CHARACTER SET latin1 COLLATE latin1_german2_ci NOT NULL,
  `OrderShipAddress2` varchar(100) CHARACTER SET latin1 COLLATE latin1_german2_ci NOT NULL,
  `OrderCity` varchar(50) CHARACTER SET latin1 COLLATE latin1_german2_ci NOT NULL,
  `OrderState` varchar(50) CHARACTER SET latin1 COLLATE latin1_german2_ci NOT NULL,
  `OrderZip` varchar(20) CHARACTER SET latin1 COLLATE latin1_german2_ci NOT NULL,
  `OrderCountry` varchar(50) CHARACTER SET latin1 COLLATE latin1_german2_ci NOT NULL,
  `OrderPhone` varchar(20) CHARACTER SET latin1 COLLATE latin1_german2_ci NOT NULL,
  `OrderFax` varchar(20) CHARACTER SET latin1 COLLATE latin1_german2_ci NOT NULL,
  `OrderShipping` float NOT NULL,
  `OrderTax` float NOT NULL,
  `OrderEmail` varchar(100) CHARACTER SET latin1 COLLATE latin1_german2_ci NOT NULL,
  `OrderDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `OrderShipped` tinyint(1) NOT NULL DEFAULT '0',
  `OrderTrackingNumber` varchar(80) CHARACTER SET latin1 COLLATE latin1_german2_ci DEFAULT NULL,
  PRIMARY KEY (`OrderID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `orders`
--

LOCK TABLES `orders` WRITE;
/*!40000 ALTER TABLE `orders` DISABLE KEYS */;
/*!40000 ALTER TABLE `orders` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `products`
--

DROP TABLE IF EXISTS `products`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `products` (
  `ProductID` int(12) NOT NULL AUTO_INCREMENT,
  `ProductSKU` varchar(50) CHARACTER SET latin1 COLLATE latin1_german2_ci NOT NULL,
  `ProductName` varchar(100) CHARACTER SET latin1 COLLATE latin1_german2_ci NOT NULL,
  `ProductPrice` float NOT NULL,
  `ProductWeight` float NOT NULL,
  `ProductCartDesc` varchar(250) CHARACTER SET latin1 COLLATE latin1_german2_ci NOT NULL,
  `ProductShortDesc` varchar(1000) CHARACTER SET latin1 COLLATE latin1_german2_ci NOT NULL,
  `ProductLongDesc` text CHARACTER SET latin1 COLLATE latin1_german2_ci NOT NULL,
  `ProductThumb` varchar(100) CHARACTER SET latin1 COLLATE latin1_german2_ci NOT NULL,
  `ProductImage` varchar(100) CHARACTER SET latin1 COLLATE latin1_german2_ci NOT NULL,
  `ProductCategoryID` int(11) DEFAULT NULL,
  `ProductUpdateDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ProductStock` float DEFAULT NULL,
  `ProductLive` tinyint(1) DEFAULT '0',
  `ProductUnlimited` tinyint(1) DEFAULT '1',
  `ProductLocation` varchar(250) CHARACTER SET latin1 COLLATE latin1_german2_ci DEFAULT NULL,
  PRIMARY KEY (`ProductID`)
) ENGINE=MyISAM AUTO_INCREMENT=991 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `products`
--

LOCK TABLES `products` WRITE;
/*!40000 ALTER TABLE `products` DISABLE KEYS */;
INSERT INTO `products` VALUES (1,'000-0001','Cotton T-Shirt',9.99,3,'Light Cotton T-Shirt','A light cotton T-Shirt made with 100% real cotton.','A light cotton T-Shirt made with 100% real cotton.\r\n\r\nMade right here in the USA for over 15 years, this t-shirt is lightweight and durable.','','',5,'2019-10-12 00:00:50',100,1,0,NULL),(2,'000-0004','Los Angeles',179.99,8,'Track and Trail','A rugged track and trail athletic shoe','A rugged track and trail athletic shoe','','',4,'2019-10-12 00:00:08',10,1,0,NULL);
/*!40000 ALTER TABLE `products` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `users` (
  `UserID` int(11) NOT NULL AUTO_INCREMENT,
  `UserEmail` varchar(500) CHARACTER SET latin1 COLLATE latin1_german2_ci DEFAULT NULL,
  `UserPassword` varchar(500) CHARACTER SET latin1 COLLATE latin1_german2_ci DEFAULT NULL,
  `UserFirstName` varchar(50) CHARACTER SET latin1 COLLATE latin1_german2_ci DEFAULT NULL,
  `UserLastName` varchar(50) CHARACTER SET latin1 COLLATE latin1_german2_ci DEFAULT NULL,
  `UserCity` varchar(90) CHARACTER SET latin1 COLLATE latin1_german2_ci DEFAULT NULL,
  `UserState` varchar(20) CHARACTER SET latin1 COLLATE latin1_german2_ci DEFAULT NULL,
  `UserZip` varchar(12) CHARACTER SET latin1 COLLATE latin1_german2_ci DEFAULT NULL,
  `UserEmailVerified` tinyint(1) DEFAULT '0',
  `UserRegistrationDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `UserVerificationCode` varchar(20) CHARACTER SET latin1 COLLATE latin1_german2_ci DEFAULT NULL,
  `UserIP` varchar(50) CHARACTER SET latin1 COLLATE latin1_german2_ci DEFAULT NULL,
  `UserPhone` varchar(20) CHARACTER SET latin1 COLLATE latin1_german2_ci DEFAULT NULL,
  `UserFax` varchar(20) CHARACTER SET latin1 COLLATE latin1_german2_ci DEFAULT NULL,
  `UserCountry` varchar(20) CHARACTER SET latin1 COLLATE latin1_german2_ci DEFAULT NULL,
  `UserAddress` varchar(100) CHARACTER SET latin1 COLLATE latin1_german2_ci DEFAULT NULL,
  `UserAddress2` varchar(50) CHARACTER SET latin1 COLLATE latin1_german2_ci DEFAULT NULL,
  PRIMARY KEY (`UserID`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `users`
--

LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
INSERT INTO `users` VALUES (1,'user@wappler.io','userwappler','John','Smith','Enschede','Overijssel','7521 PL',0,'2019-10-11 16:02:05',NULL,NULL,NULL,NULL,'Netherlands','Capitool 50',NULL);
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2019-10-26 22:10:16

Next Part

kfawcett · October 20, 2019, 5:16pm

Thank you @ben! I’m just diving into setting up a cart and payment system, so these tutorials are extremely helpful.

One comment on this tutorial though. If someone uses the SQL you provided they may be setting a character set/collation that is not the best choice for their users. Usually utf8mb4_unicode_ci (MySQL 5.7) or utf8mb4_0900_ai_ci (MySQL 8) is a more preferred choice for most users/databases.

ben · October 20, 2019, 10:00pm

Thank you Keith (@kfawcett) for your feedback. You are dead right of course and I will take note.

In this case, I did an SQL dump without thinking about the character set. Not an excuse!

Logan · October 31, 2019, 3:01pm

How do you do this in PHP my admin? Do I need to create a .sql file?
Edit: Nevermind, you can copy the commands into the console.

Quy · April 16, 2021, 7:06pm

Thank you so much this! Can you please confirm that this step is a manual process? I assumed that the database step would be created so it would be ready to go out of the box.

ben · April 16, 2021, 9:16pm

You are right, I could have coded a procedure that automatically created the tables and content. Instead, I tended to concentrate on the application rather than the database.