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

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, userid, 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: "",
    userid: 0,
    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.

Related Post

Spread the love
  • 1
  •  
  •  
  •  
  •  
  •  

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *