Insert,update and delete

Insert,update and delete

Klik hier voor een demo van onderstaande code.

Creëer de database table(s).

CREATE TABLE `vue-demo-insert-update-delete` (
  `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;

Maak een connectie in config.php

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

Download en zet de volgende 2 in de head

<script src="vue.js"></script>
<script src="https://unpkg.com/axios/dist/axios.min.js"></script>

Creëer een table met alle mogelijke data

<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);'> 
     <input type='button' value='Delete' @click='deleteRecord(index,user.id)'></td>
   </tr>
  </table>
 
</div>

Maak een ajaxfile.php aan.

Hierin worden 4 mogelijke requests afgehandeld, te weten:

  • $request == 1 – Fetch records from the userstable 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 userstable according to id.
  • $request == 4 – Delete record from the userstable according to id.
<?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;
}

Dan ga je nu het Vue script aanmaken d.m.v. het gebruik van 5 variabelen: users, userid,username,name en email. Creëer 4 methodes:

  • 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().

Maak ook een ‘created’ optie om ‘allRecords()’ aan te roepen.

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