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
-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
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/