Add, edit, and delete functionality mostly requires on every dynamic based web project.
With AJAX you can improve user experience and do these operations without page reload.
For this, I am using the Axios package.
In this tutorial, I show how you can select, insert, update, and delete records from MySQL database with Vue.js and PHP.
Contents
1. Table structure
I am using users table in the example.
CREATE TABLE `users` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `username` varchar(100) NOT NULL, `name` varchar(100) NOT NULL, `email` varchar(100) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
2. Configuration
Create a new config.php file for the database configuration.
Completed Code
<?php
$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */
$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
die("Connection failed: " . mysqli_connect_error());
}
3. Download & Include
- Download Axios package from GitHub. or you can also use CDN (https://unpkg.com/axios/dist/axios.min.js).
- Now, include
axios.min.jswithvue.jsin the<head>section.
<script src="vue.js"></script> <script src="https://unpkg.com/axios/dist/axios.min.js"></script>
4. HTML
Create <table> to list and add records.
Add –
Created 3 textboxes and a button in the columns. Added @click='addRecord()' in the button.
Update/Delete –
Use v-for='(user,index) in users' to add new rows. Create text boxes to edit values and with v-model display value.
Create two buttons for Update and Delete record.
In the update button added @click='updateRecord(index,user.id)' and in the delete button added @click='deleteRecord(index,user.id)'.
Completed Code
<div id='myapp'>
<table border='1' width='80%' style='border-collapse: collapse;'>
<tr>
<th>Username</th>
<th>Name</th>
<th>Email</th>
<th></th>
</tr>
<!-- Add -->
<tr>
<td><input type='text' v-model='username'></td>
<td><input type='text' v-model='name'></td>
<td><input type='text' v-model='email'></td>
<td><input type='button' value='Add' @click='addRecord();'></td>
</tr>
<!-- Update/Delete -->
<tr v-for='(user,index) in users'>
<td><input type='text' v-model='user.username' ></td>
<td><input type='text' v-model='user.name' ></td>
<td><input type='text' v-model='user.email' ></td>
<td><input type='button' value='Update' @click='updateRecord(index,user.id);'>
<input type='button' value='Delete' @click='deleteRecord(index,user.id)'></td>
</tr>
</table>
</div>
5. PHP
Create a new ajaxfile.php.
From this page handle 4 requests –
- $request == 1 – Fetch records from the
userstable and initialize$responsearray. Return JSON response. - $request == 2 – Check username already exists or not. If not then insert record.
- $request == 3 – Update record in the
userstable according to id. - $request == 4 – Delete record from the
userstable according to id.
Completed Code
<?php
include "config.php";
$data = json_decode(file_get_contents("php://input"));
$request = $data->request;
// Fetch All records
if($request == 1){
$userData = mysqli_query($con,"select * from users order by id desc");
$response = array();
while($row = mysqli_fetch_assoc($userData)){
$response[] = $row;
}
echo json_encode($response);
exit;
}
// Add record
if($request == 2){
$username = $data->username;
$name = $data->name;
$email = $data->email;
$userData = mysqli_query($con,"SELECT * FROM users WHERE username='".$username."'");
if(mysqli_num_rows($userData) == 0){
mysqli_query($con,"INSERT INTO users(username,name,email) VALUES('".$username."','".$name."','".$email."')");
echo "Insert successfully";
}else{
echo "Username already exists.";
}
exit;
}
// Update record
if($request == 3){
$id = $data->id;
$name = $data->name;
$email = $data->email;
mysqli_query($con,"UPDATE users SET name='".$name."',email='".$email."' WHERE id=".$id);
echo "Update successfully";
exit;
}
// Delete record
if($request == 4){
$id = $data->id;
mysqli_query($con,"DELETE FROM users WHERE id=".$id);
echo "Delete successfully";
exit;
}
6. Script
Define 5 variable – users, username, name, and email.
Create 4 methods –
- allRecords – Send POST request to fetch records where pass
request: 1. On successful callback assignresponse.datatoapp.users. - addRecord – Send POST request to add new record where pass
request: 2, username: this.username, name: this.name, email: this.email. On successful callback Empty the data values and callallRecordsmethods. - updateRecord – Read name and email from
usersaccording to index and send a POST request to update record where passrequest: 3, id: id, name: name, email: email. - deleteRecord – Send POST request to delete record where pass
request: 4, id: id. On successful callback remove an index fromusersusingsplice().
Also, define created option to call allRecords() method on after instance is been created.
Completed Code
var app = new Vue({
el: '#myapp',
data: {
users: "",
username: "",
name: "",
email: ""
},
methods: {
allRecords: function(){
axios.post('ajaxfile.php', {
request: 1
})
.then(function (response) {
app.users = response.data;
})
.catch(function (error) {
console.log(error);
});
},
addRecord: function(){
if(this.username != '' && this.name != '' && this.email != ''){
axios.post('ajaxfile.php', {
request: 2,
username: this.username,
name: this.name,
email: this.email
})
.then(function (response) {
// Fetch records
app.allRecords();
// Empty values
app.username = '';
app.name = '';
app.email = '';
alert(response.data);
})
.catch(function (error) {
console.log(error);
});
}else{
alert('Fill all fields.');
}
},
updateRecord: function(index,id){
// Read value from Textbox
var name = this.users[index].name;
var email = this.users[index].email;
if(name !='' && email !=''){
axios.post('ajaxfile.php', {
request: 3,
id: id,
name: name,
email: email
})
.then(function (response) {
alert(response.data);
})
.catch(function (error) {
console.log(error);
});
}
},
deleteRecord: function(index,id){
axios.post('ajaxfile.php', {
request: 4,
id: id
})
.then(function (response) {
// Remove index from users
app.users.splice(index, 1);
alert(response.data);
})
.catch(function (error) {
console.log(error);
});
}
},
created: function(){
this.allRecords();
}
})
7. Conclusion
Use the index to select the specific record from an Array for the update or delete and with created option load records when the instance initialized.
