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.

Insert Update and Delete records from MySQL with Vue.js

Download

Table structure
Configuration
Download & Include
HTML
PHP
Script
Conclusion

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

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.

If you found this tutorial helpful then don't forget to share.

8 thoughts on “Insert Update and Delete records from MySQL with Vue.js”

Josef Vančura

April 16, 2019 at 12:16 pm

Hi, thanks. There is mistake in sql file – it will create table “users” but code is asking for “users5”.
Rasta KP

March 2, 2020 at 2:52 am

Hi, could you please tell me, how to make add, update and delete with only one submit button using axios? right now im trying to make transaction form with laravel + vueJS + axios, and I still cant figure it out, how to using axios to add and update at the same time with one click button. I already asked this problem in stackoverflow, but it seems nobody could understand my intention, so I would glady sent my project files to you if it can help you to understand my problem. This is the link that I had created. https://stackoverflow.com/questions/60448688/how-to-update-and-insert-multiple-row-data-at-the-same-time-with-button-click-a
I Thank You for your help kind sir.
Cross

May 17, 2020 at 4:35 pm

use this link to link the axios cdn:
https://cdn.jsdelivr.net/npm/axios/dist/axios.min.js
Gilberto

July 23, 2020 at 2:59 pm

good job!
Manz

September 22, 2020 at 7:07 am

Hi .. great tutorial.. but, can we use cdn link for vue component style ?
KinJRyth

November 13, 2020 at 2:12 am

I followed the Update function 100% but it only alert successful but PHPmyAdmin did not update? How to fix it? Im new.