Fetch records from MySQL Database with Vue.js and PHP

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

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

I am using Axios package to send AJAX request.

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

Fetch records from MySQL Database with Vue.js and PHP


Contents

  1. Table structure
  2. Configuration
  3. Download & Include
  4. HTML
  5. PHP
  6. Script
  7. Demo
  8. 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 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 GET request without a parameter.
  • The second button is used to send 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 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
});

OR

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

Click on the First button to fetch all users, and enter userid (1-24) in a textbox and click the second button to fetch by ID.


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.

Related Post

Spread the love
  • 48
  •  
  •  
  •  
  •  
  •  

9 Comments

  1. Serna said:

    Hello Yogesh
    thank for this wonderful tutorial.
    I have a probleme , i copied this source but when i try the form i have not the username, name,email, are not displayed, only the rows of the table diplayed but see empty.

    Thank for your help

    June 4, 2018
    Reply
    • Yogesh Singh said:

      Hi Serna,
      Add console.log(‘success : ‘ + JSON.stringify(response.data) ); in axios then function to check any data returning from PHP file.

      Are you viewing any error in the browser console?

      June 4, 2018
      Reply
  2. Serna said:

    Hi Yogesh , thank for reply

    i add this in bottom of axios.min.js
    console.log(‘success : ‘ + JSON.stringify(response.data) );
    and in console i have this message :
    “ReferenceError: response is not defined”

    however I copy the files from your sources and copied the index.html here
    https://makitweb.com/demo/vuejs_fetch_data/
    your page works but me in localhost i have just the line of the tab but empty

    for infos i have wampserver 3.1.0 , php 7.1.9 mysql 5.7.19

    thank Yogesh for help

    Serna

    June 5, 2018
    Reply
  3. Serna said:

    Hello Yogesh,
    just to say that I tested the site online, and it does not work either

    Regards.

    June 5, 2018
    Reply
  4. serna said:

    Hi Yogesh ,
    I put an echo on the json encode and it seems to work
    i have this output from ajaxfile.php
    {“id”:”1″,”username”:”yssyogesh”,”name”:”Yogesh singh”,”email”:”yssyogesh_gmail.com”}{“id”:”2″,”username”:”sonarika”,”name”:”Sonarika”,”email”:”sonarika_gmail.com”}{“id”:”3″,”username”:”vishal”,”name”:”Vishal Sahu”,”email”:”vishal_gmail.com”} etc..

    but this data do not show in the div

    thank Yogesh to take time to explain why your code does not work in my localhost

    June 6, 2018
    Reply
    • serna said:

      Hi Yogesh,
      I modified the ajaxfile.php file a bit and it works now. ( i use PDO)
      at the end of the script before the loop :
      $result = [];
      and

      congratulations for your excellent tutorial !

      June 12, 2018
      Reply
  5. programmingGal said:

    Hi! I’m having trouble getting this to run. I keep getting a 404 error:

    spread.js:25 GET http://localhost:8080/ajaxfile.php?username=janeDoe&password=1234 404 (Not Found)

    It seems there is something wrong with the line of code where it says axios.get(….).
    Here’s my code:
    login: function(){

    axios.get(‘ajaxfile.php’ , {
    params: {
    username: this.username,
    password: this.password
    }
    })
    .then(function (response) {
    //app.users = response.data;
    // console.log(response);
    console.log(‘success : ‘ + JSON.stringify(response.data) );
    })
    .catch(function (error) {
    console.log(error);
    });

    },

    someone please help! I’ve been working on this for hours…..Thank you

    July 31, 2018
    Reply

Leave a Reply

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