CRUD in Vue JS and PHP
In this tutorial, we are going to perform a complete CRUD (create, read, update, delete) operation using Vue JS and PHP. We will be using Vue JS for the frontend and PHP for backend processing. We will also be using PDO prepared statements for preventing SQL injection.
First, you need to download Vue JS from here and Bootstrap from here. You will also need to download jQuery from here as well. After that, you need to copy-paste the JS file from Vue JS in your project. You also need to copy-paste the CSS and JS files from Bootstrap too.
After downloading and placing in your project, you need to include these files as well.
<script src="vue.min.js"></script>
<link rel="stylesheet" type="text/css" href="css/bootstrap.css" />
<script src="js/jquery-3.3.1.min.js"></script>
<script src="js/bootstrap.js"></script>
Create
First thing is that you need to insert the data into the database. For the sake of this tutorial, we have created a simple table in our MySQL database. The table name is users and it has the following columns:
- id (auto increment)
- name
- password
Then you need to create a form from which you can enter the details to save in the database.
<div id="myApp">
<div class="container">
<h1 class="text-center">Create</h1>
<div class="row">
<div class="offset-md-3 col-md-6">
<form method="POST" action="create.php" v-on:submit.prevent="doCreate">
<div class="form-group">
<label>Name</label>
<input type="text" name="name" class="form-control" />
</div>
<div class="form-group">
<label>Email</label>
<input type="email" name="email" class="form-control" />
</div>
<div class="form-group">
<label>Password</label>
<input type="password" name="password" class="form-control" />
</div>
<input type="submit" value="Create User" class="btn btn-primary" />
</form>
</div>
</div>
</div>
</div>
After that, we need to create a Vue JS app and a method that will call an AJAX request to the server.
<script>
// initialize Vue JS
const myApp = new Vue({
el: "#myApp",
methods: {
doCreate: function () {
const self = this;
const form = event.target;
const ajax = new XMLHttpRequest();
ajax.open("POST", form.getAttribute("action"), true);
ajax.onreadystatechange = function () {
if (this.readyState == 4) {
if (this.status == 200) {
console.log(this.responseText);
}
}
};
const formData = new FormData(form);
ajax.send(formData);
}
},
});
</script>
Now create a file named create.php that will handle the request.
<?php
// connect database
$conn = new PDO("mysql:host=localhost:8889;dbname=test", "root", "root");
// prepare insert statement
$sql = "INSERT INTO users (name, email, password) VALUES (:name, :email, :password)";
$result = $conn->prepare($sql);
// execute the query
$result->execute([
":name" => $_POST["name"],
":email" => $_POST["email"],
// encrypt password in hash
":password" => password_hash($_POST["password"], PASSWORD_DEFAULT),
]);
echo "Done";
Refresh the page now and enter new user details and hit submit. Then go to your phpMyAdmin and refresh the users table and you will see a new row inserted in the database.
Read
Now the data is being inserted in the database but you should all the inserted data when the page loads. To create an HTML table:
<h1 class="text-center">Read</h1>
<table class="table">
<tr>
<th>ID</th>
<th>Name</th>
<th>Email</th>
</tr>
<tr v-for="(user, index) in users">
<td v-text="user.id"></td>
<td v-text="user.name"></td>
<td v-text="user.email"></td>
</tr>
</table>
In your Vue JS instance, create a data object, and inside that object create a users array.
data: {
users: []
},
And when the Vue JS instance is mounted, we need to call a method to call an AJAX request to get the data.
// call an AJAX to fetch data when Vue JS is mounted
mounted: function () {
this.getData();
}
After that, create a method in your methods object in the Vue JS instance:
// get all users from database
getData: function () {
const self = this;
const ajax = new XMLHttpRequest();
ajax.open("POST", "read.php", true);
ajax.onreadystatechange = function () {
if (this.readyState == 4) {
if (this.status == 200) {
const users = JSON.parse(this.responseText);
self.users = users;
}
}
};
const formData = new FormData();
ajax.send(formData);
},
This will send an AJAX request but we need to create a server file that will handle this request. So create a file named read.php and the following will be the code of this file:
<?php
// connect database
$conn = new PDO("mysql:host=localhost:8889;dbname=test", "root", "root");
// get all users from database sorted by latest first
$sql = "SELECT * FROM users ORDER BY id DESC";
$result = $conn->prepare($sql);
$result->execute([]);
$data = $result->fetchAll();
// send all records fetched back to AJAX
echo json_encode($data);
Refresh the page now and you will be able to view all the records added to the database. But if you insert the new record again, you again have to refresh the page to see this new entry. However, newly inserted records should automatically be prepended at the top of the table.
So you need to modify your create.php and first return the newly inserted record from the database.
// get the latest record inserted
$sql = "SELECT * FROM users WHERE id = :id";
$result = $conn->prepare($sql);
$result->execute(array(
":id" => $conn->lastInsertId()
));
$data = $result->fetch();
// send the newly inserted record back to AJAX
echo json_encode($data);
Then in your Vue JS instance inside the doCreate method when the response is successfully received, prepend the new user in the users array.
const user = JSON.parse(this.responseText);
// prepend in local array
self.users.unshift(user);
Refresh the page now and try to insert a new user again. Now you will see that it will be prepended at the top automatically.
Update
To update the user we first must show a button to edit the user. To create a new column in your table:
<th>Actions</th>
And inside the v-for loop create a button for edit.
<td>
<button type="button" v-bind:data-id="user.id" v-on:click="showEditUserModal" class="btn btn-primary">Edit</button>
</td>
Then you need to create a method in the methods object of your Vue JS instance that will be called when this button is clicked.
showEditUserModal: function () {
const id = event.target.getAttribute("data-id");
// get user from local array and save in current object
for (var a = 0; a < this.users.length; a++) {
if (this.users[a].id == id) {
this.user = this.users[a];
break;
}
}
$("#editUserModal").modal("show");
},
Then you need to create another variable in your data object that will hold the information of the selected user.
user: null
The above-created function will display a Bootstrap modal to edit the user. Now we need to create that model in our HTML.
<!-- Modal -->
<div class="modal" id="editUserModal">
<div class="modal-dialog" role="document">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title">Edit User</h5>
<button type="button" class="close" data-dismiss="modal" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
</div>
<div class="modal-body">
<form method="POST" action="update.php" v-on:submit.prevent="doUpdate" id="form-edit-user" v-if="user != null">
<input type="hidden" name="id" v-bind:value="user.id" />
<div class="form-group">
<label>Name</label>
<input type="text" name="name" v-bind:value="user.name" class="form-control" />
</div>
<div class="form-group">
<label>Email</label>
<input type="email" name="email" v-bind:value="user.email" class="form-control" />
</div>
</form>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
<button type="submit" name="submit" class="btn btn-primary" form="form-edit-user">Save changes</button>
</div>
</div>
</div>
</div>
When the edit form is submitted, it called a Javascript function to call an AJAX request to the server. So we need to create that method in our Vue JS instance methods object.
// update the user
doUpdate: function () {
const self = this;
const form = event.target;
const ajax = new XMLHttpRequest();
ajax.open("POST", form.getAttribute("action"), true);
ajax.onreadystatechange = function () {
if (this.readyState == 4) {
if (this.status == 200) {
const user = JSON.parse(this.responseText);
// update in local array
// get index from local array
var index = -1;
for (var a = 0; a < self.users.length; a++) {
if (self.users[a].id == user.id) {
index = a;
break;
}
}
// create temporary array
const tempUsers = self.users;
// update in local temporary array
tempUsers[index] = user;
// update the local array by removing all old elements and inserting the updated users
self.users = [];
self.users = tempUsers;
}
}
};
const formData = new FormData(form);
ajax.send(formData);
// hide the modal
$("#editUserModal").modal("hide");
},
Now we need to create a new file called update.php that will handle this AJAX request and will update the data in database.
<?php
// connect database
$conn = new PDO("mysql:host=localhost:8889;dbname=test", "root", "root");
// update user name and email using his unique ID
$sql = "UPDATE users SET name = :name, email = :email WHERE id = :id";
$result = $conn->prepare($sql);
$result->execute([
":name" => $_POST["name"],
":email" => $_POST["email"],
":id" => $_POST["id"],
]);
// get the updated record
$sql = "SELECT * FROM users WHERE id = :id";
$result = $conn->prepare($sql);
$result->execute(array(
":id" => $_POST["id"]
));
$data = $result->fetch();
// send the updated record back to AJAX
echo json_encode($data);
Refresh the page now, and you will see an “Edit” button with each row. On click, you will see a modal to update the data. Once submitted, the data will be updated in the database, the bootstrap modal will be closed and you will also see the data updated in the HTML table too.
Delete
To complete the CRUD operation in Vue JS and PHP. The final step is to create a “Delete” button. In front of each edit button, we need to create another button that will delete the user from the database and from the local array as well.
<form method="POST" action="delete.php" v-on:submit.prevent="doDelete" style="display: contents;">
<input type="hidden" name="id" v-bind:value="user.id" />
<input type="submit" name="submit" class="btn btn-danger" value="Delete" />
</form>
Then we need to create a method in Vue JS that will call an AJAX request to delete the user.
// delete user
doDelete: function () {
const self = this;
const form = event.target;
const ajax = new XMLHttpRequest();
ajax.open("POST", form.getAttribute("action"), true);
ajax.onreadystatechange = function () {
if (this.readyState == 4) {
if (this.status == 200) {
// remove from local array
for (var a = 0; a < self.users.length; a++) {
if (self.users[a].id == form.id.value) {
self.users.splice(a, 1);
break;
}
}
}
}
};
const formData = new FormData(form);
ajax.send(formData);
},
Lastly, we need to create a file named delete.php that will handle this request and will actually delete the user from the database.
<?php
// connect database
$conn = new PDO("mysql:host=localhost:8889;dbname=test", "root", "root");
// delete the user from database
$sql = "DELETE FROM users WHERE id = :id";
$result = $conn->prepare($sql);
$result->execute(array(
":id" => $_POST["id"]
));
// send the response back to AJAX
echo "Done";
Refresh the page now and you will see a “Delete” button too with each row. On clicking, will delete that record from the database, and also it will delete the HTML table row as well.
Congratulations! You just completed your CRUD operation in Vue JS and PHP.
Download CRUD in Vue JS and PHP source code:
[wpdm_package id=’1511′]
Create a single page application in MEVN stack
