Durgesh Sahani

Posted on Jan 18, 2022

React CRUD operations using PHP API and MySQL

#react #php #mysql

What we are going to learn?

ReactJs introduction, Create React APP, NodeJs NPM and Homebrew installations
Create Link and Handle Routing in React | react-router-dom package installation
Create database in PHPMyAdmin and design form in React Component
Create PHP API and consume using axios | handle CORS issue on localhost
Connect MySQL database using PDO | Access React form data and Save in Database
Create GET PHP API and consume in React using axios | List in react table component
Load user details for edit form and create PHP PUT API to update user data
Create DELETE PHP API, consume it using axios and delete user from database

What is ReactJs?

React is a free and open-source front-end JavaScript library for building user interfaces based on UI components. It is maintained by Meta and a community of individual developers and companies. React can be used as a base in the development of single-page or mobile applications. Here is the official website of ReactJs https://reactjs.org/.

To create react app you’ll need to have Node >= 14.0.0 and npm >= 5.6 on your machine.

What is NodeJs and NPM?

Node.js is an open-source, cross-platform, back-end JavaScript runtime environment that runs on the V8 engine and executes JavaScript code outside a web browser. You can download from official NodeJs website: https://nodejs.org/en/. NPM will get install along with NodeJs. NPM is the default package manager for the JavaScript runtime environment NodeJs.

What is Homebrew?

For mac you can also use Homebrew to install it on your machine. Homebrew is a free and open-source software package management system that simplifies the installation of software on Apple’s operating system, macOS, as well as Linux. The name is intended to suggest the idea of building software on the Mac depending on the user’s taste. here is the official website https://brew.sh/

Install Homebrew
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

Install NodeJs
brew install nodejs

Create ReactJS APP
npx create-react-app react-crud

npx is not a typo — it’s a package runner tool that comes with npm 5.2+.

Create React App doesn’t handle backend logic or databases; it just creates a frontend build pipeline, so you can use it with any backend you want. Under the hood, it uses Babel and webpack, but you don’t need to know anything about them.

Run react app

cd react-crud
npm start

Now visit to following link you should be able to see ReactApp running:
http://localhost:3000/

To create RestFull API, we’ll be needing PHP, MYSQL and Apache. You are free to install each software separately and configure them or use stacked app. In my case I’m going to use MAMP(Mac Apache MySQL PHP) which is design for mac and comes with all required softwares

What is PHP?

PHP is a open-source general-purpose server side scripting language that is especially suited to web development. It was originally created by Danish-Canadian programmer Rasmus Lerdorf in 1994. The PHP reference implementation is now produced by The PHP Group.

What is MySQL?

MySQL is an open-source relational database management system. Its name is a combination of “My”, the name of co-founder Michael Widenius’s daughter, and “SQL”, the abbreviation for Structured Query Language.

What is Apache?

The Apache HTTP Server is a free and open-source cross-platform web server software, released under the terms of Apache License 2.0. Apache is developed and maintained by an open community of developers under the auspices of the Apache Software Foundation.

Stacked Application

WAMP for Windows
https://www.wampserver.com/en/
LAMP for Linux
https://bitnami.com/stack/lamp/installer
MAMP for Mac
https://www.mamp.info/en/mac/
XAMPP: Open Source, Cross-platform
https://www.apachefriends.org/index.html

Awesome now we are all set to start. Open you project to your favourite code editor in my case I’m using Visual Studio Code.

Create Link and Handle Routing in React | react-router-dom package installation

Create new directory components under your src/ directory and create 3 new components which we’ll update shortly:
src/components/ListUser.js

export default function ListUser() {
  return (
      <h1>List Users</h1>
  );
}

src/components/CreateUser.js

export default function CreateUser() {
  return (
      <h1>Create User</h1>
  );
}

src/components/UpdateUser.js

export default function UpdateUser() {
  return (
      <h1>Update User</h1>
  );
}

Now let’s Install react router for creating link and configuring routes
npm install react-router-dom

Update App.js for handling routing

import {BrowserRouter, Routes, Route, Link} from 'react-router-dom';
import './App.css';
import CreateUser from './components/CreateUser';
import EditUser from './components/EditUser';
import ListUser from './components/ListUser';
function App() {
  return (
    <div className="App">
      <h5>React CRUD operations using PHP API and MySQL</h5>
      <BrowserRouter>
        <nav>
          <ul>
            <li>
              <Link to="/">List Users</Link>
            </li>
            <li>
              <Link to="user/create">Create User</Link>
            </li>
          </ul>
        </nav>
        <Routes>
          <Route index element={<ListUser />} />
          <Route path="user/create" element={<CreateUser />} />
          <Route path="user/:id/edit" element={<EditUser />} />
        </Routes>
      </BrowserRouter>
    </div>
  );
}
export default App;

Create database in PHPMyAdmin and design form in React Component

CREATE DTAABASE react_crud;

CREATE TABLE `react_crud`.`users` 
(
`id` int NOT NULL auto_increment,
`name` varchar(50),
`email` varchar(60),
`mobile` bigint(10),
`created_at` timestamp,
`updated_at` timestamp, PRIMARY KEY (id)
);

Update src/components/CreateUser.js

import { useState } from "react";
export default function ListUser() {
    const [inputs, setInputs] = useState([]);
    const handleChange = (event) => {
        const name = event.target.name;
        const value = event.target.value;
        setInputs(values => ({...values, [name]: value}));
    }
    const handleSubmit = (event) => {
        event.preventDefault();
        console.log(inputs);
    }
    return (
        <div>
            <h1>Create user</h1>
            <form onSubmit={handleSubmit}>
                <table cellSpacing="10">
                    <tbody>
                        <tr>
                            <th>
                                <label>Name: </label>
                            </th>
                            <td>
                                <input type="text" name="name" onChange={handleChange} />
                            </td>
                        </tr>
                        <tr>
                            <th>
                                <label>Email: </label>
                            </th>
                            <td> 
                                <input type="text" name="email" onChange={handleChange} />
                            </td>
                        </tr>
                        <tr>
                            <th>
                                <label>Mobile: </label>
                            </th>
                            <td>
                                <input type="text" name="mobile" onChange={handleChange} />
                            </td>
                        </tr>
                        <tr>
                            <td colSpan="2" align ="right">
                                <button>Save</button>
                            </td>
                        </tr>
                    </tbody>
                </table>
            </form>
        </div>
    )
}

Create PHP API and consume using axios | handle CORS issue on localhost

Install axios for making API calls from ReactJs
npm install axios

Now update your src/components/CreateUser.js file again for making POST API call

import axios from "axios";
const handleSubmit = (event) => {
    event.preventDefault();
    axios.post('http://localhost:8888/api/user/save', inputs).then(function(response){
        console.log(response.data);
    });
}

Create PHP file for writing API and name it index.php

<?php
    header('Access-Control-Allow-Origin: *');
    header("Access-Control-Allow-Headers: *");
    echo "Testing";
?>

Notice at the top we have added headers to solved CORS issue.
Also, Let’s create .htaccess file to handle pretty URLs like PHP lumen framework, and add following line inside.

RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-d
RewriteCond %{REQUEST_FILENAME} !-f
RewriteRule ^ index.php [L]

Hoola, Finally our API file is ready.

Connect MySQL database using PDO | Access React form data and Save in Database

Now, Let’s create a database connect file and name it DbConnect.php and add following code snippet inside. Update credentials as per yours and it will connect to your database using PDO(PHP Data Object).

<?php
    /**
    * Database Connection
    */
    class DbConnect {
        private $server = 'localhost';
        private $dbname = 'react_crud';
        private $user = 'root';
        private $pass = '';
        public function connect() {
            try {
                $conn = new PDO('mysql:host=' .$this->server .';dbname=' . $this->dbname, $this->user, $this->pass);
                $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                return $conn;
            } catch (\Exception $e) {
                echo "Database Error: " . $e->getMessage();
            }
        }

    }
?>

Create POST API

include("DbConnect.php");
    $conn = new DbConnect();
    $db = $conn->connect();
    $method = $_SERVER['REQUEST_METHOD'];
    switch($method) {
        case 'POST':
            $user = json_decode(file_get_contents('php://input'));
            $sql = "INSERT INTO users(id, name, email, mobile, created_at) values(null, :name, :email, :mobile, :created_at)";
            $stmt = $db->prepare($sql);
            $date = date('Y-m-d');
            $stmt->bindParam(':name', $user->name);
            $stmt->bindParam(':email', $user->email);
            $stmt->bindParam(':mobile', $user->mobile);
            $stmt->bindParam(':created_at', $date);
            if($stmt->execute()) {
                $data = ['status' => 1, 'message' => "Record successfully created"];
            } else {
                $data = ['status' => 0, 'message' => "Failed to create record."];
            }
            echo json_encode($data);
            break;
}

// Reference database connection class file and connect to MySQL Database
include("DbConnect.php");
$conn = new DbConnect();
$db = $conn->connect();

// Give you Method used to hit API
$method = $_SERVER['REQUEST_METHOD'];

// Read the POST JSON data and convert it into PHP Object
$user = json_decode(file_get_contents('php://input'));

Create GET PHP API and consume in React using axios | List in react table component

Update src/components/ListUser.js

import axios from "axios"
import { useEffect, useState } from "react";
import { Link } from "react-router-dom";
export default function ListUser() {
    const [users, setUsers] = useState([]);
    useEffect(() => {
        getUsers();
    }, []);
    function getUsers() {
        axios.get('http://localhost:8888/api/users/').then(function(response) {
            console.log(response.data);
            setUsers(response.data);
        });
    }
    return (
        <div>
            <h1>List Users</h1>
            <table>
                <thead>
                    <tr>
                        <th>#</th>
                        <th>Name</th>
                        <th>Email</th>
                        <th>Mobile</th>
                        <th>Actions</th>
                    </tr>
                </thead>
                <tbody>
                    {users.map((user, key) =>
                        <tr key={key}>
                            <td>{user.id}</td>
                            <td>{user.name}</td>
                            <td>{user.email}</td>
                            <td>{user.mobile}</td>
                            <td>
                                <Link to={`user/${user.id}/edit`} style={{marginRight: "10px"}}>Edit</Link>
                                <button>Delete</button>
                            </td>
                        </tr>
                    )}

                </tbody>
            </table>
        </div>
    )
}

Update index.php file for adding new GET API to get all users

case 'GET':
    $sql = "SELECT * FROM users";
    $stmt = $db->prepare($sql);
    $stmt->execute();
    $users = $stmt->fetchAll(PDO::FETCH_ASSOC);        
    echo json_encode($users);
    break;

Load user details for edit form and create PHP PUT API to update user data

Update src/components/UpdateUser.js

import { useState, useEffect } from "react";
import axios from "axios";
import { useNavigate, useParams } from "react-router-dom";
export default function ListUser() {
    const navigate = useNavigate();
    const [inputs, setInputs] = useState([]);
    const {id} = useParams();
    useEffect(() => {
        getUser();
    }, []);
    function getUser() {
        axios.get(`http://localhost:8888/api/user/${id}`).then(function(response) {
            console.log(response.data);
            setInputs(response.data);
        });
    }
    const handleChange = (event) => {
        const name = event.target.name;
        const value = event.target.value;
        setInputs(values => ({...values, [name]: value}));
    }
    const handleSubmit = (event) => {
        event.preventDefault();
        axios.put(`http://localhost:8888/api/user/${id}/edit`, inputs).then(function(response){
            console.log(response.data);
            navigate('/');
        });

    }
    return (
        <div>
            <h1>Edit user</h1>
            <form onSubmit={handleSubmit}>
                <table cellSpacing="10">
                    <tbody>
                        <tr>
                            <th>
                                <label>Name: </label>
                            </th>
                            <td>
                                <input value={inputs.name} type="text" name="name" onChange={handleChange} />
                            </td>
                        </tr>
                        <tr>
                            <th>
                                <label>Email: </label>
                            </th>
                            <td> 
                                <input value={inputs.email} type="text" name="email" onChange={handleChange} />
                            </td>
                        </tr>
                        <tr>
                            <th>
                                <label>Mobile: </label>
                            </th>
                            <td>
                                <input value={inputs.mobile} type="text" name="mobile" onChange={handleChange} />
                            </td>
                        </tr>
                        <tr>
                            <td colSpan="2" align ="right">
                                <button>Save</button>
                            </td>
                        </tr>
                    </tbody>
                </table>
            </form>
        </div>
    )
}

Update get method to return specific user details by ID

case "GET": 
        $sql = "SELECT * FROM users";
        $path = explode('/', $_SERVER['REQUEST_URI']);
        if(isset($path[3]) && is_numeric($path[3])) {
            $sql .= " WHERE id = :id";
            $stmt = $conn->prepare($sql);
            $stmt->bindParam(':id', $path[3]);
            $stmt->execute();
            $users = $stmt->fetch(PDO::FETCH_ASSOC);
        } else {
            $stmt = $conn->prepare($sql);
            $stmt->execute();
            $users = $stmt->fetchAll(PDO::FETCH_ASSOC);
        }

        echo json_encode($users);
        break;

Now create update API to Handle PUT request

case "PUT": 
            $user = json_decode( file_get_contents('php://input') );
            $sql = "UPDATE users SET name= :name, email =:email, mobile =:mobile, updated_at =:updated_at WHERE id = :id";
            $stmt = $conn->prepare($sql);
            $updated_at = date('Y-m-d');
            $stmt->bindParam(':id', $user->id);
            $stmt->bindParam(':name', $user->name);
            $stmt->bindParam(':email', $user->email);
            $stmt->bindParam(':mobile', $user->mobile);
            $stmt->bindParam(':updated_at', $updated_at);

            if($stmt->execute()) {
                $response = ['status' => 1, 'message' => 'Record updated successfully.'];
            } else {
                $response = ['status' => 0, 'message' => 'Failed to update record.'];
            }
            echo json_encode($response);
            break;

Create DELETE PHP API, consume it using axios and delete user from database

Update src/components/ListUser.js

const deleteUser = (id) => {
        axios.delete(`http://localhost:8888/api/user/${id}/delete`).then(function(response){
        console.log(response.data);
        getUsers();
    });
}
<button onClick={() => deleteUser(user.id)}>Delete</button>

Update index.php again for writing delete API

case "DELETE": 
        $sql = "DELETE FROM users WHERE id = :id";
        $path = explode('/', $_SERVER['REQUEST_URI']);

        $stmt = $conn->prepare($sql);
        $stmt->bindParam(':id', $path[3]);
        if($stmt->execute()) {
            $response = ['status' => 1, 'message' => 'Record deleted successfully.'];
        } else {
            $response = ['status' => 0, 'message' => 'Failed to delete record.'];
        }
        echo json_encode($response);
        break;

Awesome, you have just completed REACT-CRUD using PHP and MySQL, Now run you application and build something beautiful.

You can download complete source code from GitHub:
https://github.com/durgesh-sahani/react-crud-php-api-mysql

Thank you for reading this blog.

Top comments (2)

chischte • Dec 8 '22

Hi there, ... thanks a lot for the tutorial!
I am working through it right now and will hopfully get everything to work :)

What confuses me a bit is that in the repository:
... the file EditUser.js has an export default function ListUser
... the file ListUser.js has also an export default function ListUser

On this tutorial page you have a heading Update src/components/UpdateUser.js
(this file does not exist in the repo) and in the codebox below is an export Default function ListUser.

Thomas Hansen • Oct 19 '22

I actually found an interesting use case for traits. It's actually possible to define abstract methods inside a trait, and define both public and protected (and now, with php 8.1, private) methods and properties. If you implement it correctly, you can use it to define a pseudo/partial abstract class to emulate multiple inheritance.

DEV Community