Insert Update and Delete records from MySQL with Vue.js

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.



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.js with vue.js in 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);'>&nbsp; <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 users table and initialize $response array. Return JSON response.
  • $request == 2 – Check username already exists or not. If not then insert record.
  • $request == 3 – Update record in the users table according to id.
  • $request == 4 – Delete record from the users table 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 assign response.data to app.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 call allRecords methods.
  • updateRecord – Read name and email from users according to index and send a POST request to update record where pass request: 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 from users using splice().

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(); }
})

Related Posts