How to fetch the records in PHP OOP and Vue.js?

Question

I am working on this project right now, but I'm stacked with this little problem. How can I fetch the records using PHP OOP with vue.js? It seems like there's no other way to pass a variable in the url in a class function. I am using the Axios.js to send the AJAX request. How can I improve this and be able to fetch data from axios.js?

Index.php

<div id="admin">
<div class="col-md-12">
      <table class='table table-bordered table-striped'>
        <thead>
          <th>#</th>
          <th>First Name</th>
          <th>Last Name</th>
          <th>Username</th>
          <th>User Type</th>
          <th>Delete</th>
        </thead>
        <tbody>
         <?php
         require_once'function.php';
         $show_users=new DB_con();
         $sql1=$show_users->show_users();
         $cnt=1;
         while($row=mysqli_fetch_array($sql1))
         {
          ?>
          <tr v-for="show_user in show_users">
           <td><?php echo htmlentities($cnt);?></td>
           <td><?php echo htmlentities($row['fname']);?> {{ show_user.fname }}</td>
           <td><?php echo htmlentities($row['lname']);?>{{ show_user.lname }}</td>
           <td><?php echo htmlentities($row['username']);?>{{ show_user.username }}</td>
           <td><?php echo htmlentities($row['user_type']);?>{{ show_user.usertype }}</td>
           <td><a href="index.php?del=<?php echo htmlentities($row['id']);?>"><button class="btn btn-danger btn-xs" onClick="return confirm('Do you really want to delete');"><span class="glyphicon glyphicon-trash"></span>Delete</button></a></td>
         </tr>
         <?php
         $cnt++;
       } ?>
     </tbody>
   </table>
 </div>
</div>

<script src="js/vue.js"></script>
<script src="js/axios.js"></script>
<script src="js/app.js"></script>

app.js

var app = new Vue({
    el: '#admin',
data:{
        show_users: []
    },
    mounted: function(){
        this.getAllUsers();
    },
    methods:{
        getAllUsers: function(){
            axios.get("function.php")
                .then(function(response){
                    //console.log(response);
                    app.show_users = response.data.show_users;
                });
        }
    }
});

function.php

<?php 
session_start();
define('DB_SERVER','localhost');
define('DB_USER','root');
define('DB_PASS' ,'');
define('DB_NAME', 'e4');

class DB_con
{
function __construct()
{
$con = mysqli_connect(DB_SERVER,DB_USER,DB_PASS,DB_NAME);
$this->dbh=$con;
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
 }
}
    public function show_users()
    {
    $reg=mysqli_query($this->dbh,"SELECT id, fname, lname, username, remark, user_type FROM e4_users as a join e4_user_type as b on b.userId=a.userId WHERE a.userId='2' AND remark='1' ");
    return $reg;
    }
}
?>

Answer 1

Your function.php right now is responsible for starting a session, defining the MySQL connection variables and also holds a class responsible for connecting to MySQL.

You don't want to do this, but rather follow a principle called Separation of concerns (click here).

In the not-ajax way, as it is now in your index.php, you are including the function.php file, you are instantiating your class and calling the function that returns the results from the database and then loops through them.

If you want your axios ajax call to get those results, you need to call a file that actually delivers those results.

What you can do is make a new file, let's say show_users.php, and put in something like this:

<?php
require_once'function.php';
$show_users=new DB_con();
$sql1=$show_users->show_users();

$usersArray = mysqli_fetch_assoc($sql1);
header('Content-type: application/json');
echo json_encode($usersArray);
?>

Then, make sure you change your axios call from axios.get("function.php") to axios.get("show_users.php").

Although I have to say, doing all this manually in PHP is a lot of unnecessary work. You would be easier and safer off by choosing a Framework that handles a lot of the work for you, e.g. Laravel

Edit:

You have very confusing naming. For example, you have the class "DB_con", which by the way should be named "DBCon" instead. I would even call it DBConnection to make its purpose more obvious. See PSR-1 for generally accepted coding style standards.

You have a variable called $show_users which instantiates your class DB_con, so what the variable holds is actually an instance of DB_con. That has nothing to do with the function show_users. That's why it also reads very weird to see later $show_users->show_users(). It would make more sense to name your connection object for example $connection, making it $connection = new DB_con();.

Next, you name your query $sql1, although this is NOT SQL. SQL is a query language and a variable with the name $sql should probably hold a string. Actually, what the function returns is a mysqli_result. And that result holds user data. So name it, e.g. $usersResult or $usersMysqliResult or something similar. Then your code would get a lot easier to understand, making it

$connection = new DB_con();
$usersMysqlResult = $connection->show_users();
$usersArray = mysqli_fetch_assoc($usersMysqlResult);
...