Insert Update and Delete records from MySQL with Vue.js

on
Here are the some file and code that can be helpful for you.
Let's start:

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.

Comments

Post a Comment