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


Contents

  1. Table structure
  2. Configuration
  3. Download & Include
  4. HTML
  5. PHP
  6. Script
  7. 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.
Are you want to get implementation help, or modify or extend the functionality of this script? Submit paid service request.

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

  1. 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.

    Reply

Leave a Comment