Data not persisting to MySQL database with axios in Vue

Question

I am having trouble getting a form to update a mysql database. I am not getting any errors at this point, and the Vuetify datatable is functioning correctly (adding, updating, and deleting users). The data is just not persisting to the database and (obviously) goes away when the page is refreshed.

I am using Vue, Vuetify, axios, MySQL, and am only on a local server at this point (Wamp).

Here is the main.js:

import Vue from 'vue';
import App from './App.vue';
import vuetify from './plugins/vuetify';
import router from './router';
import GoogleSignInButton from 'vue-google-signin-button-directive';

Vue.config.productionTip = false

new Vue({
  GoogleSignInButton,
  vuetify,
  router,
  render: h => h(App)
}).$mount('#app')

The only thing in my app.vue is the navigation component, a router tag and some global styles:

<template>
  <v-app>
    <Nav />

    <v-main class="ml-14 mt-14 px-5 pt-0">
      <v-container fluid app class="full-height">
        <v-row justify="start" align="start" class="full-height">
          <router-view></router-view>
        </v-row>
      </v-container>
    </v-main>
  </v-app>
</template>

<script>
  import Nav from "./components/Nav";

  export default {
    name: "App",

    components: {
      Nav,
    },

    data: () => ({

    }),
  };
</script>

<style lang="scss">
  #inspire,
  .full-width {
    width: 100%;
  }
  .full-height {
    height: 100%;
  }
  .capitalize {
    text-transform: capitalize;
  }
</style>

Here is the script from /components/Users.vue:

  import axios from "axios";
  export default {
    data: () => ({
      dialog: false,
      headers: [
        {
          text: "First Name",
          align: "start", //Aligns the items to the beginning of the column. Can change to center or end.
          value: "firstname",
        },
        { text: "Last Name", value: "lastname" },
        { text: "School", value: "school" },
        { text: "Email", value: "email" },
        { text: "Phone Number", value: "phone" },
        { text: "Primary View", value: "primary_view" },
        { text: "Tutor Role", value: "tutor_role" },
        { text: "Principal Role", value: "principal_role" },
        { text: "Admin Role", value: "admin_role" },
        { text: "Actions", value: "actions", sortable: false },
      ],
      users: [],
      schools: [
        "School 1",
        "School 2",
        "School 3",
      ],
      primary_view: ["Tutor", "Principal", "Admin"],
      tutor_role: true,
      principal_role: false,
      admin_role: false,
      editedIndex: -1,
      editedItem: {
        firstname: "",
        lastName: "",
        school: "",
        email: "",
        phone: "",
        primary_view: "Tutor",
        tutor_role: "true",
        principal_role: "false",
        admin_role: "false",
      },
      defaultItem: {
        firstname: "",
        lastName: "",
        school: "",
        email: "",
        phone: "",
        primary_view: "Tutor",
        tutor_role: "true",
        principal_role: "false",
        admin_role: "false",
      },
    }),

    computed: {
      formTitle() {
        return this.editedIndex === -1 ? "New Item" : "Edit Item";
      },
    },

    watch: {
      dialog(val) {
        val || this.close();
      },
    },

    created() {
      this.initialize();
    },

    methods: {
      allRecords: function() {
        axios
          .post("db/users.php", {
            request: 1,
          })
          .then((response) => {
            console.log(response.data);
            this.users = response.data;
          })
          .catch((error) => {
            console.log(error);
          });
      },

      initialize() {
        this.allRecords();
      },
      editItem(item) {
        this.editedIndex = this.users.indexOf(item);
        this.editedItem = Object.assign({}, item);
        this.dialog = true;
      },

      deleteItem(item) {
        const index = this.users.indexOf(item);
        confirm("Are you sure you want to delete this item?") &&
          this.users.splice(index, 1);
        console.log("You have deleted a row.");

        axios
          .delete("db/ajaxfile.php", {
            request: 4,
          })
          .then(function(response) {
            console.log(response.data);
            this.users = response.data;
          })
          .catch(function(error) {
            console.log(error);
          });
      },

      close() {
        this.dialog = false;
        this.$nextTick(() => {
          this.editedItem = Object.assign({}, this.defaultItem);
          this.editedIndex = -1;
        });
      },

      save() {
        if (this.editedIndex > -1) {
          console.log("You have edited the data.");

          axios
            .put("db/ajaxfile.php", {
              request: 3,
              firstname: this.editedItem.firstname,
              lastname: this.editedItem.lastname,
              school: this.editedItem.school,
              email: this.editedItem.email,
              phone: this.editedItem.phone,
              primary_view: this.editedItem.primary_view,
              tutor_role: this.editedItem.tutor_role,
              principal_role: this.editedItem.principal_role,
              admin_role: this.editedItem.admin_role,
            })
            .then(function(response) {
              console.log(response);
            })
            .catch(function(error) {
              console.log(error);
            });

          Object.assign(this.users[this.editedIndex], this.editedItem);
        } else {
          console.log("You have created a new row.");
          axios
            .post("db/ajaxfile.php", {
              request: 2,
              firstname: this.editedItem.firstname,
              lastname: this.editedItem.lastname,
              school: this.editedItem.school,
              email: this.editedItem.email,
              phone: this.editedItem.phone,
              primary_view: this.editedItem.primary_view,
              tutor_role: this.editedItem.tutor_role,
              principal_role: this.editedItem.principal_role,
              admin_role: this.editedItem.admin_role,
            })
            .then(function(response) {
              console.log(response);
            })
            .catch(function(error) {
              console.log(error);
            });
          this.users.push(this.editedItem);
        }
        this.close();
      },
    },
  };

Here is the ajaxfile.php file that handles the database requests (there is a config.php file that connects to the database):

<?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 lastname asc");

  $response = array();
  while($row = mysqli_fetch_assoc($userData)){
    $response[] = $row;
  }

  echo json_encode($response);
  exit;
}

// Add record
if($request == 2){
  $firstname = $data->firstname;
  $lastname = $data->lastname;
  $school = $data->school;
  $email = $data->email;
  $phone = $data->phone;
  $primary_view = $data->primary_view;
  $tutor_role = $data->tutor_role;
  $principal_role = $data->principal_role;
  $admin_role = $data->admin_role;

  $userData = mysqli_query($con,"SELECT * FROM users WHERE email='".$email."'");
  if(mysqli_num_rows($userData) == 0){
    mysqli_query($con,"INSERT INTO users(firstname,lastname,school,email,phone,primary_view,tutor_role,principal_role,admin_role) VALUES('".$firstname."','".$lastname."','".$school."','".$email."','".$phone."','".$primary_view."','".$tutor_role."','".$principal_role."','".$admin_role."')");
    echo "Insert was successful.";
  }else{
    echo "Email already exists.";
  }

  exit;
}

// Update record
if($request == 3){
  //$id = $data->id;
  $firstname = $data->firstname;
  $lastname = $data->lastname;
  $school = $data->school;
  $email = $data->email;
  $phone = $data->phone;
  $primary_view = $data->primary_view;
  $tutor_role = $data->tutor_role;
  $principal_role = $data->principal_role;
  $admin_role = $data->admin_role;

  mysqli_query($con,"UPDATE users SET firstname='".$firstname."',lastname='".$lastname."',school='".$school."',email='".$email."',phone='".$phone."',primary_view='".$primary_view."',tutor_role='".$tutor_role."',principal_role='".$principal_role."',admin_role='".$admin_role."' WHERE id=".$id);
 
  echo "Update was successful.";
  exit;
}

// Delete record
if($request == 4){
  //$id = $data->id;

  mysqli_query($con,"DELETE FROM users WHERE id=".$id);

  echo "Delete was successful.";
  exit;
}

Here is the config.php:

<?php

$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "project_db"; /* Database name */

$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
  die("Connection failed: " . mysqli_connect_error());
}

When I try to add a dummy user, I do not get any errors in the console. The Headers under Network read:

General:
  Request URL: http://vbsd/db/ajaxfile.php
  Request Method: POST
  Status Code: 200 OK
  Remote Address: [::1]:80
  Referrer Policy: no-referrer-when-downgrade
Response Headers:  
  Connection: Keep-Alive
  Content-Length: 22
  Content-Type: text/html; charset=UTF-8
  Date: Sat, 18 Jul 2020 18:54:50 GMT
  Keep-Alive: timeout=5, max=100
  Server: Apache/2.4.39 (Win64) PHP/7.3.5
  X-Powered-By: PHP/7.3.5
Request Headers:
  Accept: application/json, text/plain, */*
  Accept-Encoding: gzip, deflate
  Accept-Language: en-US,en;q=0.9
  Connection: keep-alive
  Content-Length: 184
  Content-Type: application/json;charset=UTF-8
  Host: vbsd
  Origin: http://vbsd
  Referer: http://vbsd/stats
  User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/83.0.4103.116 Safari/537.36
Request Payload
  {request: 2, firstname: "aaa", lastname: "aaa", school: "Central", email: "aaa", phone: "222",…}
  admin_role: "false"
  email: "aaa"
  firstname: "aaa"
  lastname: "aaa"
  phone: "222"
  primary_view: "Tutor"
  principal_role: "false"
  request: 2
  school: "Central"
  tutor_role: "true"

I feel like I am missing something obvious here, but I have been unable to figure out what. Any help or direction would be appreciated!

I understand that. I'm just trying to learn the bare bones basics of connecting and manipulating mysql. Why won't it update the database? — codeartistgirl, Jul 18 '20 at 21:47
First of all you have put too much code in this question. Please try to narrow it down. We do not need to see both axios and PHP code. Then enable mysqli exceptions [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439). Switch to prepared statements (as I said it's a major bug and likely the source of your problem). If this doesn't help please update the question with the debugging details you performed — Dharman, Jul 18 '20 at 21:55

SC Kim · Accepted Answer · 2020-07-18T22:26:28.577

I see you used PUT,DELETE,POST requests but no GET to fetch database? In other words you're lacking fetchData method in every change database has been made.

Supposedly every time you received response in .then you should allow your app fetchData() via GET from database mysql in every operation, similarly how things done in AJAX, if you don't want to refresh page.

P/S the console.logs like console.log("You have edited the data."); doesn't prove anything before you execute axios methods, the console should be put under .then

.then(function(response) {
console.log("You have REALLY updated the data in database.");
              console.log(response);
            })

Lastly, if you wished to use POST to get all updated records, you just do:

.then(function(response) {
              console.log(response);
    this.allRecords(); //fetchTask again
            })

Data not persisting to MySQL database with axios in Vue

1 Answers1