Fetch records from MySQL Database with Vue.js and PHP

Data selection is one of the basic requirements when creating a dynamic website.

Mainly data is fetched from the database on page refresh but without page refresh require to send AJAX request.

I am using the Axios package to send the AJAX request.

In this tutorial, I show how you can fetch records from MySQL database using Vue.js and PHP with the Axios package.

Fetch records from MySQL Database with Vue.js and PHP

Demo Download

Table structure
Configuration
Download & Include
HTML
PHP
Script
Demo
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 two buttons and a textbox.

On the first button define @click='allRecords()' and on the second define @click='recordByID()' events.

Add v-model='userid' in the textbox.

Use v-for='user in users' on <tr> to list records.

The first button is used to send a GET request without a parameter.
The second button is used to send a GET request with parameters.

Completed Code

<div id='myapp'>
 
  <!-- Select All records -->
  <input type='button' @click='allRecords()' value='Select All users'>
  <br><br>

  <!-- Select record by ID -->
  <input type='text' v-model='userid' placeholder="Enter Userid between 1 - 24">
  <input type='button' @click='recordByID()' value='Select user by ID'>
  <br><br>

  <!-- List records -->
  <table border='1' width='80%' style='border-collapse: collapse;'>
    <tr>
      <th>Username</th>
      <th>Name</th>
      <th>Email</th>
    </tr>

    <tr v-for='user in users'>
      <td>{{ user.username }}</td>
      <td>{{ user.name }}</td>
      <td>{{ user.email }}</td>
    </tr>
  </table>
 
</div>

5. PHP

Create a new ajaxfile.php.

Fetch records from the users table and initialize $response Array.

Assign " id =".$_GET['userid'] to $where if $_GET['userid'] is passed.

Return $response in JSON response.

Completed Code

<?php
include "config.php";

$condition = "1";
if(isset($_GET['userid'])){
   $condition = " id=".$_GET['userid'];
}
$userData = mysqli_query($con,"select * from users WHERE ".$condition);

$response = array();

while($row = mysqli_fetch_assoc($userData)){

   $response[] = $row;
}

echo json_encode($response);
exit;

6. Script

Syntax – Axios GET request without parameter –

axios.get('path')
.then(function(response){
   // Handle response
}).catch(function(error){
   // Error handling
});

Syntax – Axios GET request with parameter –

axios.get('path',{
  params:{
     parameter1: value,
     parameter2: value,
     .... 
  }
})
.then(function(response){
   // Handle response
}).catch(function(error){
   // Error handling
});

axios.get('path?variable1=value1&variable2=value2&...')
.then(function(response){
   // Handle response
}).catch(function(error){
   // Error handling
});

Define two variable users and userid.

Define two methods –

allRecords – Send AJAX request to fetch all records and assign response.data to app.users. Here, app.users pointing to defined data variable.
recordByID – Pass the textbox value this.userid in params: { userid: this.userid }. Assign response.data in app.users on successful callback.

Completed Code

var app = new Vue({
  el: '#myapp',
  data: {
    users: "",
    userid: 0
  },
  methods: {
    allRecords: function(){

      axios.get('ajaxfile.php')
      .then(function (response) {
         app.users = response.data;
      })
      .catch(function (error) {
         console.log(error);
      });
    },
    recordByID: function(){
      if(this.userid > 0){
 
        axios.get('ajaxfile.php', {
           params: {
             userid: this.userid
           }
        })
        .then(function (response) {
           app.users = response.data;
        })
        .catch(function (error) {
           console.log(error);
        });
      }
    }
  }
})

7. Demo

View Demo

8. Conclusion

Use params option to pass data to AJAX request or you can directly pass it with the path.

If you found this tutorial helpful then don't forget to share.

Contents

1. Table structure

2. Configuration

3. Download & Include

4. HTML

5. PHP

6. Script

7. Demo

8. Conclusion