Dynamic Pricing Table - PHP, MySQL and Vue JS

Dynamic Pricing Table – PHP, MySQL and Vue JS

Pricing table in a website is used to give your users an idea 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 admin panel, we will create multiple pricing tables and their data will be stored in MySQL database. On user side, the data will be fetched from database and displayed to the user using Vue JS.

Pricing tables are basically packages offered in different prices so users can pick the one they find convenient. We will be using PHP as a server side language, MySQL to store the pricing table data in database. And Vue JS to render the HTML.

Show Form to Add Pricing Table

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 where we can create pricing table. Each pricing table will have title, a short description, amount and some features. Feature means the services you will be providing in that package.

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 “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 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. 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 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 the pricing table form and hit submit. Then refresh your phpMyAdmin page and you will see a new row in “pricings” table. You will also see multiple rows in “pricing_features” table.

Show Pricing Table on User Side

Now the pricing table and their features has been saved in MySQL database, you can easily display them in your user side. Paste the following code anywhere you want to show 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 in your website.

Edit & Delete Pricing Table

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 in 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. 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 database “get-data.php“, so it will fetch easily.

Edit Pricing Table

The table above created will also display an edit and a delete button. When you click on 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” where will show a form with an auto-populated values of that pricing table.

The edit page will also have the functionality to add or delete features from 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 same as adding a new pricing table. First, we need to fetch the values from 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. 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 database as well as in user side. On user side, you have to refresh the page to see the changes.

Delete Pricing Table

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.

So that’s how you can create fully responsive dynamic pricing tables in your website, which can be managed from admin panel. You do not have to go in the code to make changes. If you face any problem in following this, please do let us know in the comments section below.

Leave a Reply

Please disable your adblocker or whitelist this site!