PHP + Vue.js + MySQL CRUD Example

Hello everyone, today we will learn how to develop a full-stack web application that is a basic User Management Application using PHP, Vue.js and MySQL.
The GitHub repository link is provided at the end of this tutorial. You can download the source code.

After completing this tutorial what will we build? 

We will build a full-stack web application that is a basic User Management Application with CRUD features:

• Create User
• List User
• Update User 
• Delete User 

-Add User:

-Retrieve all Users:

-Update User:

We divided this tutorial into two parts. 

PART 1 - Rest APIs development using PHP
PART 2 - UI development using Vue.js

PART 1 - Rest APIs development using PHP 

Project Directory



Setting Up Database

Create Database "user_db" and create table "user"

--
-- Database: `user_db`
--
-- --------------------------------------------------------
--
-- Table structure for table `user`
--

CREATE TABLE `user` (
`id` bigint(20) UNSIGNED NOT NULL,
`first_name` varchar(50) NOT NULL,
`last_name` varchar(50) NOT NULL,
`email_id` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `user`
ADD UNIQUE KEY `id` (`id`);

ALTER TABLE `user`
MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=70;
COMMIT;


Database Connection [database.php]

<?php
class DB {
private $host = "localhost";
private $db = "user_db";
private $username = "root";
private $password = "";

public $conn;

public function getConnection(){
$this->conn = null;
try{
$this->conn = new PDO("mysql:host=" . $this->host .
";dbname=" . $this->db, $this->username, $this->password);
$this->conn->exec("set names utf8");
}catch(PDOException $exception){
echo "Database not connected: " . $exception->getMessage();
}
return $this->conn;
}
}
?>


Create User Class[user.php]

<?php
class User{

// conn
private $conn;

// table
private $dbTable = "user";

// col
public $id;
public $first_name;
public $last_name;
public $email_id;
// db conn
public function __construct($db){
$this->conn = $db;
}

// GET Users
public function getUsers(){
$sqlQuery = "SELECT id, first_name, last_name, email_id
FROM " . $this->dbTable . "";
$stmt = $this->conn->prepare($sqlQuery);
$stmt->execute();
return $stmt;
}

// CREATE User
public function createUser(){
$sqlQuery = "INSERT INTO
". $this->dbTable ."
SET
first_name = :first_name,
last_name = :last_name,
email_id = :email_id";
$stmt = $this->conn->prepare($sqlQuery);
// sanitize
$this->first_name=htmlspecialchars(strip_tags($this->first_name));
$this->last_name=htmlspecialchars(strip_tags($this->last_name));
$this->email_id=htmlspecialchars(strip_tags($this->email_id));
// bind data
$stmt->bindParam(":first_name", $this->first_name);
$stmt->bindParam(":last_name", $this->last_name);
$stmt->bindParam(":email_id", $this->email_id);
if($stmt->execute()){
return true;
}
return false;
}

// GET User
public function getSingleUser(){
$sqlQuery = "SELECT
id,
first_name,
last_name,
email_id
FROM
". $this->dbTable ."
WHERE
id = ?
LIMIT 0,1";

$stmt = $this->conn->prepare($sqlQuery);
$stmt->bindParam(1, $this->id);
$stmt->execute();
$dataRow = $stmt->fetch(PDO::FETCH_ASSOC);
$this->first_name = $dataRow['first_name'];
$this->last_name = $dataRow['last_name'];
$this->email_id = $dataRow['email_id'];
}

// UPDATE User
public function updateUser(){
$sqlQuery = "UPDATE
". $this->dbTable ."
SET
first_name = :first_name,
last_name = :last_name,
email_id = :email_id
WHERE
id = :id";
$stmt = $this->conn->prepare($sqlQuery);
$this->first_name=htmlspecialchars(strip_tags($this->first_name));
$this->last_name=htmlspecialchars(strip_tags($this->last_name));
$this->email_id=htmlspecialchars(strip_tags($this->email_id));
$this->id=htmlspecialchars(strip_tags($this->id));
// bind data
$stmt->bindParam(":first_name", $this->first_name);
$stmt->bindParam(":last_name", $this->last_name);
$stmt->bindParam(":email_id", $this->email_id);
$stmt->bindParam(":id", $this->id);
if($stmt->execute()){
return true;
}
return false;
}

// DELETE User
function deleteUser(){
$sqlQuery = "DELETE FROM " . $this->dbTable . " WHERE id = ?";
$stmt = $this->conn->prepare($sqlQuery);
$this->id=htmlspecialchars(strip_tags($this->id));
$stmt->bindParam(1, $this->id);
if($stmt->execute()){
return true;
}
return false;
}

}
?>


Create User [create.php]

<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json;");
header("Access-Control-Allow-Methods: POST");
header("Access-Control-Max-Age: 3600");
header("Access-Control-Allow-Headers: Content-Type,
Access-Control-Allow-Headers, Authorization, X-Requested-With");

include_once 'config/database.php';
include_once 'class/user.php';

$database = new DB();
$db = $database->getConnection();

$item = new User($db);

$data = json_decode(file_get_contents("php://input"));

$item->first_name = $data->first_name;
$item->last_name = $data->last_name;
$item->email_id = $data->email_id;
if($item->createUser()){
echo json_encode("User created.");
} else{
echo json_encode("Failed to create user.");
}
?>



Get User Records [read.php]

<?php
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Headers: X-Requested-With,
Content-Type, Origin, Cache-Control, Pragma, Authorization,
Accept, Accept-Encoding");
header("Content-Type: application/json;");
include_once 'config/database.php';
include_once 'class/user.php';

$database = new DB();
$db = $database->getConnection();

$items = new User($db);

$stmt = $items->getUsers();
$itemCount = $stmt->rowCount();

if($itemCount > 0){
$userArr = array();

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
extract($row);
$e = array(
"id" => $id,
"first_name" => $first_name,
"last_name" => $last_name,
"email_id" => $email_id
);

array_push($userArr, $e);
}
echo json_encode($userArr);
}

?>


Get User By Id [single_user.php]

<?php
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Headers: X-Requested-With,
Content-Type, Origin, Cache-Control, Pragma, Authorization,
Accept, Accept-Encoding");
header("Content-Type: application/json;");
include_once 'config/database.php';
include_once 'class/user.php';

$database = new DB();
$db = $database->getConnection();

$item = new User($db);

$item->id = isset($_GET['id']) ? $_GET['id'] : die();
$item->getSingleUser();

if($item!= null){
$user_Arr = array(
"id" => $item->id,
"first_name" => $item->first_name,
"last_name" => $item->last_name,
"email_id" => $item->email_id
);
http_response_code(200);
echo json_encode($user_Arr);
}
else{
http_response_code(404);
echo json_encode("User record not found.");
}
?>



Update User [update.php]

<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json;");
header("Access-Control-Allow-Methods: PUT");
header("Access-Control-Max-Age: 3600");
header("Access-Control-Allow-Headers: Content-Type,
Access-Control-Allow-Headers, Authorization, X-Requested-With");
include_once 'config/database.php';
include_once 'class/user.php';
$database = new DB();
$db = $database->getConnection();
$item = new User($db);
$data = json_decode(file_get_contents("php://input"));
$item->id = $data->id;
$item->first_name = $data->first_name;
$item->last_name = $data->last_name;
$item->email_id = $data->email_id;
if($item->updateUser()){
echo json_encode("User record updated.");
} else{
echo json_encode("User record could not be updated.");
}
?>


Delete User [delete.php]

<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json;");
header("Access-Control-Allow-Methods: DELETE");
header("Access-Control-Max-Age: 3600");
header("Access-Control-Allow-Headers: Content-Type,
Access-Control-Allow-Headers, Authorization, X-Requested-With");
include_once 'config/database.php';
include_once 'class/user.php';
$database = new DB();
$db = $database->getConnection();
$item = new User($db);
$item->id = isset($_GET['id']) ? $_GET['id'] : die();
if($item->deleteUser()){
echo json_encode("User deleted.");
} else{
echo json_encode("Not deleted");
}
?>




PART 2 - UI Development using Vue.js

Project Structure



package.json

A package.json is a JSON file that subsists at the root of a Javascript/Node project. It holds metadata pertinent to the project and is utilized for managing its dependencies, scripts, version, and much more.
{
"name": "frontend",
"version": "0.1.0",
"private": true,
"scripts": {
"serve": "vue-cli-service serve",
"build": "vue-cli-service build",
"lint": "vue-cli-service lint"
},
"dependencies": {
"axios": "^0.22.0",
"vue": "^2.6.6",
"vue-router": "^3.0.2"
},
"devDependencies": {
"@vue/cli-plugin-babel": "^3.5.0",
"@vue/cli-plugin-eslint": "^3.5.0",
"@vue/cli-service": "^3.5.0",
"babel-eslint": "^10.0.1",
"eslint": "^5.8.0",
"eslint-plugin-vue": "^5.0.0",
"vue-template-compiler": "^2.5.21"
},
"eslintConfig": {
"root": true,
"env": {
"node": true
},
"extends": [
"plugin:vue/essential",
"eslint:recommended"
],
"rules": {},
"parserOptions": {
"parser": "babel-eslint"
}
},
"postcss": {
"plugins": {
"autoprefixer": {}
}
},
"browserslist": [
"> 1%",
"last 2 versions",
"not ie <= 8"
]
}



Components

Vue Components are one of the important features of VueJS that creates custom elements, which can be reused in HTML

User.vue

<template>
<div>
<h3>User</h3>
<div class="container">
<form @submit="validateAndSubmit">
<div v-if="errors.length">
<div
class="alert alert-danger"
v-bind:key="index"
v-for="(error, index) in errors"
>
{{ error }}
</div>
</div>
<fieldset class="form-group">
<label>First Name</label>
<input type="text" class="form-control" v-model="first_name" />
</fieldset>
<fieldset class="form-group">
<label>Last Name</label>
<input type="text" class="form-control" v-model="last_name" />
</fieldset>
<fieldset class="form-group">
<label>Email Id</label>
<input type="text" class="form-control" v-model="email_id" />
</fieldset>
<button class="btn btn-success" type="submit">Save</button>
</form>
</div>
</div>
</template>
<script>
import UserDataService from "../service/UserDataService";

export default {
name: "User",
data() {
return {
first_name: "",
last_name: "",
email_id: "",
errors: [],
};
},
computed: {
id() {
return this.$route.params.id;
},
},
methods: {
refreshUserDetails() {
if (this.id != -1) {
UserDataService.retrieveUser(this.id).then((res) => {
this.first_name = res.data.first_name;
this.last_name = res.data.last_name;
this.email_id = res.data.email_id;
});
}
},
validateAndSubmit(e) {
e.preventDefault();
this.errors = [];
if (!this.first_name) {
this.errors.push("Enter valid values");
} else if (this.first_name.length < 5) {
this.errors.push("Enter atleast 5 characters in First Name");
}
if (!this.last_name) {
this.errors.push("Enter valid values");
} else if (this.last_name.length < 5) {
this.errors.push("Enter atleast 5 characters in Last Name");
}
if (this.errors.length === 0) {
if (this.id == -1) {
UserDataService.createUser({
first_name: this.first_name,
last_name: this.last_name,
email_id: this.email_id,
}).then(() => {
this.$router.push("/users");
});
} else {
UserDataService.updateUser({
id: this.id,
first_name: this.first_name,
last_name: this.last_name,
email_id: this.email_id,
}).then(() => {
this.$router.push("/users");
});
}
}
},
},
created() {
this.refreshUserDetails();
},
};
</script>



Users.vue

<template>
<div class="container">
<h3>All Users</h3>
<div v-if="message" class="alert alert-success">{{ this.message }}</div>
<div class="container">
<table class="table">
<thead>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Email Id</th>
<th>Update</th>
<th>Delete</th>
</tr>
</thead>
<tbody>
<tr v-for="user in users" v-bind:key="user.id">
<td>{{ user.first_name }}</td>
<td>{{ user.last_name }}</td>
<td>{{ user.email_id }}</td>
<td>
<button class="btn btn-warning" v-on:click="updateUser(user.id)">
Update
</button>
</td>
<td>
<button class="btn btn-danger" v-on:click="deleteUser(user.id)">
Delete
</button>
</td>
</tr>
</tbody>
</table>
<div class="row">
<button class="btn btn-success" v-on:click="addUser()">Add</button>
</div>
</div>
</div>
</template>
<script>
import UserDataService from "../service/UserDataService";

export default {
name: "Users",
data() {
return {
users: [],
message: "",
};
},
methods: {
refreshUsers() {
UserDataService.retrieveAllUsers().then((res) => {
this.users = res.data;
});
},
addUser() {
this.$router.push(`/user/-1`);
},
updateUser(id) {
this.$router.push(`/user/${id}`);
},
deleteUser(id) {
UserDataService.deleteUser(id).then(() => {
this.refreshUsers();
});
},
},
created() {
this.refreshUsers();
},
};
</script>



UserDataService.js

import axios from 'axios'

const USER_API_URL = 'http://localhost:8080/php-mysql-crud-api'

class UserDataService {

retrieveAllUsers() {

return axios.get(`${USER_API_URL}/read.php`);
}

retrieveUser(id) {

return axios.get(`${USER_API_URL}/single_user.php`,
{ params: { id: id } });
}

deleteUser(id) {

return axios.delete(`${USER_API_URL}/delete.php`,
{ params: { id: id } });
}

updateUser(user) {

return axios.put(`${USER_API_URL}/update.php`, user);
}

createUser(user) {

return axios.post(`${USER_API_URL}/create.php`, user);
}

}

export default new UserDataService()



App.vue

<template>
<div class="container">
<div class="navbar-header">
<a class="navbar-brand" href="#">PHP + MYSQL + VUE CRUD Application</a><br/><br/>
</div>
<router-view/>
</div>
</template>

<script>
export default {
name: "app"
};
</script>

<style>
@import url(https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css);

</style>



routes.js

import Vue from "vue";
import Router from "vue-router";

Vue.use(Router);

const router = new Router({
mode: 'history',
routes: [
{
path: "/",
name: "Users",
component: () => import("./components/Users"),
},
{
path: "/users",
name: "Users",
component: () => import("./components/Users"),
},
{
path: "/user/:id",
name: "User",
component: () => import("./components/User"),
},
]
});

export default router;


vue.config.js

module.exports = {
devServer: {
port: 9080
}
}



main.js

import Vue from 'vue'
import App from './App.vue'
import router from './routes';

Vue.config.productionTip = false

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


index.html

<!DOCTYPE html>
<html lang="en">

<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width,initial-scale=1.0">
<link rel="icon" href="<%= BASE_URL %>favicon.ico">
<title>PHP - VUE - MYSQL - CRUD</title>
</head>

<body>
<noscript>
<strong>We're sorry but PHP - VUE - MYSQL -
CRUD doesn't work properly without JavaScript enabled. Please
enable it to continue.</strong>
</noscript>
<div id="app"></div>
<!-- built files will be auto injected -->
</body>

</html>


## Project setup

npm install

## Run the application in local

npm run serve

 App running at - Local:http://localhost:9080/ 

Popular posts from this blog

Learn Java 8 streams with an example - print odd/even numbers from Array and List

Java Stream API - How to convert List of objects to another List of objects using Java streams?

Registration and Login with Spring Boot + Spring Security + Thymeleaf

Java, Spring Boot Mini Project - Library Management System - Download

ReactJS, Spring Boot JWT Authentication Example

Top 5 Java ORM tools - 2024

Java - Blowfish Encryption and decryption Example

Spring boot video streaming example-HTML5

Google Cloud Storage + Spring Boot - File Upload, Download, and Delete