Dynamic pricing table – PHP, MySQL, and Vue JS
A pricing table on a website is used to give your users an idea of how much you charge for your services. In this article, we are going to teach you, how you can create a dynamic pricing table using PHP, MySQL, and Vue JS. The pricing table is created in bootstrap so it will be mobile responsive.
From the admin panel, we will create multiple pricing tables and their data will be stored in the MySQL database. On the user side, the data will be fetched from the database and displayed to the user using Vue JS.
Pricing tables are basically packages offered at different prices so users can pick the one they find convenient. We will be using PHP as a server-side language, and MySQL to store the pricing table data in the database. And Vue JS to render the HTML.
Show form to add pricing table
To make our pricing table dynamic, we must first add it in the database. First, download Bootstrap from here and Vue JS from here. Paste the CSS and JS files in your project, we will be using them in a moment. After that, we need to create a form from which we can create a pricing table.
Each pricing table will have a title, a short description, an amount, and some features. The feature means the services you will be provided in that package.
The following code goes in your admin panel from where you want to add pricing tables.
<!-- include bootstrap -->
<link rel="stylesheet" type="text/css" href="bootstrap.min.css" />
<!-- include vue js -->
<script src="vue.min.js"></script>
<!-- container for vue js app -->
<div class="container" style="margin-top: 50px; margin-bottom: 50px;" id="addPricingTableApp">
<div class="row">
<!-- center align form -->
<div class="offset-md-3 col-md-6">
<h2 style="margin-bottom: 30px;">Create Pricing Table</h2>
<!-- prevent the form from redirecting, and call JS function 'store' -->
<form v-on:submit.prevent="store">
<!-- get title of pricing table -->
<div class="form-group">
<label>Title</label>
<input type="text" v-model="title" class="form-control" />
</div>
<!-- get description -->
<div class="form-group">
<label>Description</label>
<input type="text" v-model="description" class="form-control" />
</div>
<!-- get amount -->
<div class="form-group">
<label>Amount</label>
<input type="number" v-model="amount" class="form-control" />
</div>
<!-- list of features in this pricing table -->
<h2 style="margin-top: 50px; margin-bottom: 50px;">Features</h2>
<div class="row" style="margin-bottom: 50px;">
<div class="col-md-12">
<!-- loop through an array in vue js app -->
<div v-for="(feature, index) in features" style="border: 1px solid black; padding: 10px; margin-bottom: 10px;">
<!-- show input field to get the feature value -->
<div class="form-group">
<label>Feature</label>
<input type="text" class="form-control" v-model="features[index].value" placeholder="Can have HTML" />
</div>
<!-- button to delete the feature from pricing table -->
<button type="button" class="btn btn-danger btn-sm" v-on:click="deleteFeature" v-bind:data-index="index">Delete Feature</button>
</div>
</div>
</div>
<!-- button to add new feature -->
<div class="row" style="margin-bottom: 20px;">
<div class="col-md-12">
<button type="button" class="btn btn-success" v-on:click="addFeature">Add Feature</button>
</div>
</div>
<!-- submit button -->
<input type="submit" class="btn btn-info" value="Add Pricing" />
</form>
</div>
</div>
[show all pricing tables]
</div>
This will show a form with input fields. But when you click on the “Add Feature” button, nothing happens. This is because we need to render it using Vue JS.
<script>
// initialize vue js app
var addPricingTableApp = new Vue({
el: "#addPricingTableApp", // id of container div
data: {
// all values used in this app
title: "",
description: "",
amount: 0,
features: [],
pricings: []
},
// all methods
methods: {
// [store method]
// delete feature box
deleteFeature: function () {
var index = event.target.getAttribute("data-index");
this.features.splice(index, 1);
},
// add new feature box
addFeature: function() {
this.features.push({
value: ""
});
}
}
});
</script>
Refresh the page now and you will be able to add and remove features, this is because of Vue JS. Now we need to call an AJAX request to save this pricing table data in the MySQL database so it can be fetched later.
Save pricing table in MySQL using PHP
First, we need to create a “store” method in Vue JS “methods” object. The following code goes in the [store method] section:
// called when form is submitted
store: function () {
// get this app instance
var self = this;
// call an AJAX to create a new pricing table
var ajax = new XMLHttpRequest();
ajax.open("POST", "store.php", true);
ajax.onreadystatechange = function () {
if (this.readyState == 4) { // response received
if (this.status == 200) { // response is successfull
// console.log(this.responseText);
// parse the response from JSON string to JS arrays and objects
var response = JSON.parse(this.responseText);
console.log(response);
// if added in database, then prepend in local array too
if (response.status == "success") {
self.pricings.unshift(response.pricing);
} else {
// display an error message
alert(response.message);
}
}
if (this.status == 500) {
console.log(this.responseText);
}
}
};
// create form data object and append all the values in it
var formData = new FormData();
formData.append("title", this.title);
formData.append("description", this.description);
formData.append("amount", this.amount);
formData.append("features", JSON.stringify(this.features));
// actually sending the request
ajax.send(formData);
},
Then we need to create a file named “store.php” that will handle this request. Following will be the code of the store.php file:
<?php
// connect with database
$conn = new PDO("mysql:host=localhost;dbname=test", "root", "");
// create tables if not exists
$sql = "CREATE TABLE IF NOT EXISTS pricings (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
description TEXT NULL,
amount DOUBLE NOT NULL
)";
$result = $conn->prepare($sql);
$result->execute();
$sql = "CREATE TABLE IF NOT EXISTS pricing_features (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
pricing_id INTEGER,
value TEXT NOT NULL,
FOREIGN KEY (pricing_id) REFERENCES pricings(id) ON UPDATE CASCADE ON DELETE CASCADE
)";
$result = $conn->prepare($sql);
$result->execute();
// insert in pricings table
$sql = "INSERT INTO pricings (title, description, amount) VALUES (:title, :description, :amount)";
$result = $conn->prepare($sql);
$result->execute([
":title" => $_POST["title"],
":description" => $_POST["description"],
":amount" => $_POST["amount"]
]);
$pricing_id = $conn->lastInsertId();
// save all features in a separate table
$features = json_decode($_POST["features"]);
foreach ($features as $feature)
{
$sql = "INSERT INTO pricing_features(pricing_id, value) VALUES (:pricing_id, :value)";
$result = $conn->prepare($sql);
$result->execute([
":pricing_id" => $pricing_id,
":value" => $feature->value
]);
}
// get the pricing table just inserted in database
$sql = "SELECT * FROM pricings WHERE id = :id";
$result = $conn->prepare($sql);
$result->execute([
":id" => $pricing_id
]);
$pricing = $result->fetch();
// send the response back to client with new pricing table
echo json_encode([
"status" => "success",
"message" => "Pricing table has been added.",
"pricing" => $pricing
]);
exit();
This assumes that you have a database named “test”. You can change it as per yours if you already have one. This will create the pricings and pricing_features table if you do not already have one.
Refresh the page now and fill out the pricing table form and hit submit. Then refresh your phpMyAdmin page and you will see a new row in the “pricings” table. You will also see multiple rows in the “pricing_features” table.
Show pricing table on the user side
Now the pricing table and its features have been saved in the MySQL database, you can easily display them on your user side. Paste the following code anywhere you want to show the pricing table to your users:
<link rel="stylesheet" type="text/css" href="bootstrap.min.css" />
<script src="vue.min.js"></script>
<style>
.box-shadow {
box-shadow: 0 .25rem .75rem rgba(0, 0, 0, .05);
}
</style>
<div class="container" style="margin-top: 50px; margin-bottom: 50px;" id="pricingTableApp">
<div class="row">
<div class="col-md-12">
<h2 class="text-center" style="margin-bottom: 30px;">Pricing Table</h2>
</div>
</div>
<div class="row">
<div class="col-md-3" v-for="(pricing, index) in pricings">
<div class="card mb-4 box-shadow">
<div class="card-header">
<h4 class="my-0 font-weight-normal text-center" v-text="pricing.title"></h4>
</div>
<div class="card-body">
<h1 class="card-title pricing-card-title text-center" v-text="'$' + pricing.amount"></h1>
<ul class="list-unstyled mt-3 mb-4">
<li v-for="(feature, featureIndex) in pricing.features" class="text-center">
<span v-html="feature.value"></span>
</li>
</ul>
<button type="button" class="btn btn-lg btn-block btn-outline-primary">Buy now</button>
</div>
</div>
</div>
</div>
</div>
We need to call an AJAX request to get the pricing tables from the server. So following will be your Javascript code on same file:
<script>
var pricingTableApp = new Vue({
el: "#pricingTableApp",
data: {
pricings: []
},
methods: {
getData: function () {
var self = this;
var ajax = new XMLHttpRequest();
ajax.open("POST", "get-data.php", true);
ajax.onreadystatechange = function () {
if (this.readyState == 4) {
if (this.status == 200) {
// console.log(this.responseText);
var response = JSON.parse(this.responseText);
console.log(response);
if (response.status == "success") {
self.pricings = response.pricings;
}
}
if (this.status == 500) {
console.log(this.responseText);
}
}
};
var formData = new FormData();
ajax.send(formData);
}
},
mounted: function () {
this.getData();
}
});
</script>
Now create a file named “get-data.php” that will fetch pricing table along with their features and return to the client.
<?php
// connect with database
$conn = new PDO("mysql:host=localhost;dbname=test", "root", "");
// get all pricing tables sorting by amount from lowest to highest
$sql = "SELECT * FROM pricings ORDER BY amount ASC";
$result = $conn->prepare($sql);
$result->execute();
$pricings = $result->fetchAll();
// get all the features of each pricing table too
$data = [];
foreach ($pricings as $pricing)
{
$sql = "SELECT * FROM pricing_features WHERE pricing_id = :pricing_id";
$result = $conn->prepare($sql);
$result->execute([
":pricing_id" => $pricing["id"]
]);
$pricing_features = $result->fetchAll();
$pricing["features"] = $pricing_features;
array_push($data, $pricing);
}
// send the response back to client with the data
echo json_encode([
"status" => "success",
"message" => "Data has been fetched.",
"pricings" => $data
]);
exit();
Refresh the page now and you will be able to see a beautifully designed pricing table on your website.
Edit & delete the pricing table
We will have a pure dynamic pricing table if we were able to modify or delete the features from it from admin panel.
At this point, your desired work is work. But you can go ahead and allow an admin to edit or delete the pricing table. Suppose you want to add more features to some package, or you want to delete a specific pricing table altogether.
Display all pricing tables
First, we need to display a list of all pricing tables in an HTML table. The following code goes in the [show all pricing tables] section:
<!-- show all pricing tables -->
<div class="row" style="margin-top: 50px;">
<div class="col-md-12">
<table class="table table-bordered">
<!-- table heading -->
<tr>
<th>ID</th>
<th>Title</th>
<th>Description</th>
<th>Actions</th>
</tr>
<!-- loop through an array of pricing tables -->
<tr v-for="(pricing, index) in pricings">
<td v-text="pricing.id"></td>
<td v-text="pricing.title"></td>
<td v-text="pricing.description"></td>
<td>
<!-- edit pricing table link -->
<a class="btn btn-link" v-bind:href="'edit.php?id=' + pricing.id">
Edit
</a>
<!-- form to delete pricing table -->
<form v-on:submit.prevent="deletePricing">
<input type="hidden" name="id" v-model="pricing.id" required />
<input type="submit" class="btn btn-danger btn-sm" value="Delete" />
</form>
</td>
</tr>
</table>
</div>
</div>
We already have a Vue JS instance named addPricingTableApp. When this instance is mounted, we will call an AJAX request to get the pricing tables from MySQL database using PHP.
var addPricingTableApp = new Vue({
el: "#addPricingTableApp",
data: {
// other variables
},
methods: {
// other methods
// method to get all pricing tables from server
getData: function () {
// get this app instance
var self = this;
// call an AJAX to fetch all pricing tables
var ajax = new XMLHttpRequest();
ajax.open("POST", "get-data.php", true);
ajax.onreadystatechange = function () {
if (this.readyState == 4) { // response received
if (this.status == 200) { // response is successfull
// console.log(this.responseText);
// parse the response from JSON string to JS arrays and objects
var response = JSON.parse(this.responseText);
console.log(response);
// render the data in table
if (response.status == "success") {
self.pricings = response.pricings;
}
}
if (this.status == 500) {
console.log(this.responseText);
}
}
};
// send an AJAX request to the server
var formData = new FormData();
ajax.send(formData);
},
// method to delete pricing table
deletePricing: function () {
//
},
// other methods
},
mounted: function () { // called when the DOM is mounted
this.getData();
}
});
Refresh the admin side now and you will be able to view all the pricing tables in an HTML table. We already have a separate file for fetching data from the database “get-data.php“, so it will fetch easily.
Update
The table above created will also display edit and a delete button. When you click on the edit button, it will take you to the “edit.php” file with a parameter having the value of that pricing table. Now we need to create a file named “edit.php” which will show a form with an auto-populated value of that pricing table.
The edit page will also have the functionality to add or delete features from the pricing table. We will also be using Vue JS here to populate the values in input fields.
<link rel="stylesheet" type="text/css" href="bootstrap.min.css" />
<script src="vue.min.js"></script>
<div class="container" style="margin-top: 50px; margin-bottom: 50px;" id="editPricingTableApp">
<div class="row">
<div class="offset-md-3 col-md-6">
<h2 style="margin-bottom: 30px;">Edit Pricing Table</h2>
<form v-on:submit.prevent="update" v-if="pricing != null">
<div class="form-group">
<label>Title</label>
<input type="text" v-model="pricing.title" class="form-control" />
</div>
<div class="form-group">
<label>Description</label>
<input type="text" v-model="pricing.description" class="form-control" />
</div>
<div class="form-group">
<label>Amount</label>
<input type="number" v-model="pricing.amount" class="form-control" />
</div>
<h2 style="margin-top: 50px; margin-bottom: 50px;">Features</h2>
<div class="row" style="margin-bottom: 50px;">
<div class="col-md-12">
<div v-for="(feature, index) in pricing.features" style="border: 1px solid black; padding: 10px; margin-bottom: 10px;">
<div class="form-group">
<label>Feature</label>
<input type="text" class="form-control" v-model="pricing.features[index].value" placeholder="Can have HTML" />
</div>
<button type="button" class="btn btn-danger btn-sm" v-on:click="deleteFeature" v-bind:data-index="index">Delete Feature</button>
</div>
</div>
</div>
<div class="row" style="margin-bottom: 20px;">
<div class="col-md-12">
<button type="button" class="btn btn-success" v-on:click="addFeature">Add Feature</button>
</div>
</div>
<input type="submit" class="btn btn-warning" value="Edit Pricing" />
</form>
</div>
</div>
<!-- hidden input field to get the ID from URL in javascript -->
<input type="hidden" id="pricing-id" value="<?php echo $_GET['id']; ?>" />
</div>
This will be almost the same as adding a new pricing table. First, we need to fetch the values from the MySQL database by calling an AJAX request.
<script>
var editPricingTableApp = new Vue({
el: "#editPricingTableApp",
data: {
id: "",
pricing: null
},
methods: {
// [update method goes here]
getPricingTable: function () {
var self = this;
var ajax = new XMLHttpRequest();
ajax.open("POST", "get-pricing-table.php", true);
ajax.onreadystatechange = function () {
if (this.readyState == 4) {
if (this.status == 200) {
// console.log(this.responseText);
var response = JSON.parse(this.responseText);
console.log(response);
if (response.status == "success") {
self.pricing = response.pricing;
}
}
if (this.status == 500) {
console.log(this.responseText);
}
}
};
var formData = new FormData();
formData.append("id", self.id);
ajax.send(formData);
},
deleteFeature: function () {
var index = event.target.getAttribute("data-index");
this.pricing.features.splice(index, 1);
},
addFeature: function() {
this.pricing.features.push({
value: ""
});
}
},
mounted: function () {
this.id = document.getElementById("pricing-id").value;
this.getPricingTable();
}
});
</script>
Then we need to create a file named “get-pricing-table.php” to fetch the data of this pricing table only.
<?php
// connect with database
$conn = new PDO("mysql:host=localhost;dbname=test", "root", "");
// get pricing table using ID
$sql = "SELECT * FROM pricings WHERE id = :id";
$result = $conn->prepare($sql);
$result->execute([
":id" => $_POST["id"]
]);
$pricing = $result->fetch();
// get all the features too of this pricing table
$sql = "SELECT * FROM pricing_features WHERE pricing_id = :pricing_id";
$result = $conn->prepare($sql);
$result->execute([
":pricing_id" => $pricing["id"]
]);
$pricing_features = $result->fetchAll();
$pricing["features"] = $pricing_features;
// send the response back to client with the data
echo json_encode([
"status" => "success",
"message" => "Pricing table has been fetced.",
"pricing" => $pricing
]);
exit();
Refresh the edit page now and you will be able to view the data in input fields along with all the features of that pricing table.
Now when this form submits, we need to call an AJAX request to update the data. The following code goes in your [update method goes here] section:
update: function () {
var self = this;
var ajax = new XMLHttpRequest();
ajax.open("POST", "update.php", true);
ajax.onreadystatechange = function () {
if (this.readyState == 4) {
if (this.status == 200) {
// console.log(this.responseText);
var response = JSON.parse(this.responseText);
console.log(response);
alert(response.message);
}
if (this.status == 500) {
console.log(this.responseText);
}
}
};
var formData = new FormData();
formData.append("id", this.id);
formData.append("title", this.pricing.title);
formData.append("description", this.pricing.description);
formData.append("amount", this.pricing.amount);
formData.append("features", JSON.stringify(this.pricing.features));
ajax.send(formData);
},
Finally, create a file named “update.php” that will handle this request and will update the pricing table.
<?php
// connect with database
$conn = new PDO("mysql:host=localhost;dbname=test", "root", "");
// udpdate the pricing table
$sql = "UPDATE pricings SET title = :title, description = :description, amount = :amount WHERE id = :id";
$result = $conn->prepare($sql);
$result->execute([
":title" => $_POST["title"],
":description" => $_POST["description"],
":amount" => $_POST["amount"],
":id" => $_POST["id"]
]);
// delete all the old features
$sql = "DELETE FROM pricing_features WHERE pricing_id = :pricing_id";
$result = $conn->prepare($sql);
$result->execute([
":pricing_id" => $_POST["id"]
]);
// insert new features
$features = json_decode($_POST["features"]);
foreach ($features as $feature)
{
$sql = "INSERT INTO pricing_features(pricing_id, value) VALUES (:pricing_id, :value)";
$result = $conn->prepare($sql);
$result->execute([
":pricing_id" => $_POST["id"],
":value" => $feature->value
]);
}
// send the response back to client
echo json_encode([
"status" => "success",
"message" => "Pricing table has been updated."
]);
exit();
Now refresh the page and try to update any pricing table, you will see it will be changed in the database as well as on the user side. On the user side, you have to refresh the page to see the changes.
Delete
You already have a method named “deletePricing” in your Vue JS instance named “addPricingTableApp“. You just need to modify it as the following:
// method to delete pricing table
deletePricing: function () {
// get this app instance
var self = this;
// get form
var form = event.target;
// call an AJAX to delete the pricing table
var ajax = new XMLHttpRequest();
ajax.open("POST", "delete.php", true);
ajax.onreadystatechange = function () {
if (this.readyState == 4) { // response received
if (this.status == 200) { // response is successfull
// console.log(this.responseText);
// parse the response from JSON string to JS arrays and objects
var response = JSON.parse(this.responseText);
console.log(response);
// remove from local array if deleted from server
if (response.status == "success") {
for (var a = 0; a < self.pricings.length; a++) {
var pricing = self.pricings[a];
if (pricing.id == form.id.value) {
self.pricings.splice(a, 1);
break;
}
}
} else {
// display an error message
alert(response.message);
}
}
if (this.status == 500) {
console.log(this.responseText);
}
}
};
// append form in form data object
var formData = new FormData(form);
// call AJAX with form data
ajax.send(formData);
},
Now we need to create a file named “delete.php” and in this file, we will simply run the delete query.
<?php
// connect with database
$conn = new PDO("mysql:host=localhost;dbname=test", "root", "");
// create a query to delete the pricing table from database
$sql = "DELETE FROM pricings WHERE id = :id";
// prepare the query
$result = $conn->prepare($sql);
// execute the query
$result->execute([
":id" => $_POST["id"]
]);
// send the response back to client
echo json_encode([
"status" => "success",
"message" => "Pricing table has been deleted."
]);
exit();
Refresh the page now on your admin side and hit the delete button. You will see the row will be deleted from an HTML table and also from the database. If you refresh the user side, it will be removed from there too.
Also learn how to create a dynamic testimonial section on your website.
So that’s how you can create a fully responsive dynamic pricing table on your website, which can be managed from the admin panel. You do not have to go into the code to make changes. If you face any problems in following this, please do let us know in the comments section below.
[wpdm_package id=’1395′]
this code is not working. how should i run it?
What error are you having ?
Hello there! This article couldn’t be written much better!
Reading through this article reminds me of my previous roommate!
He always kept talking about this. I am going
to forward this information to him. Pretty sure he’s going to have a very good read.
Many thanks for sharing!