DEV Community

Cover image for React CRUD operations using PHP API and MySQL
Durgesh Sahani
Durgesh Sahani

Posted on

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
Enter fullscreen mode Exit fullscreen mode

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>
  );
}
Enter fullscreen mode Exit fullscreen mode

src/components/CreateUser.js

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

src/components/UpdateUser.js

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

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;
Enter fullscreen mode Exit fullscreen mode

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)
);
Enter fullscreen mode Exit fullscreen mode

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>
    )
}
Enter fullscreen mode Exit fullscreen mode

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);
    });
}
Enter fullscreen mode Exit fullscreen mode

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

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

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]
Enter fullscreen mode Exit fullscreen mode

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();
            }
        }

    }
?>
Enter fullscreen mode Exit fullscreen mode

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;
}
Enter fullscreen mode Exit fullscreen mode 
// Reference database connection class file and connect to MySQL Database
include("DbConnect.php");
$conn = new DbConnect();
$db = $conn->connect();
Enter fullscreen mode Exit fullscreen mode 
// Give you Method used to hit API
$method = $_SERVER['REQUEST_METHOD'];
Enter fullscreen mode Exit fullscreen mode 
// Read the POST JSON data and convert it into PHP Object
$user = json_decode(file_get_contents('php://input'));
Enter fullscreen mode Exit fullscreen mode

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>
    )
}
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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>
    )
}
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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>
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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)

Collapse
 
chischte profile image
chischte •

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.

Collapse
 
thomasphansen profile image
Thomas Hansen •

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.

Read next

arichy profile image

A common TypeScript error with useRef

Arc -

mvaja13 profile image

Is Waku the new NextJS?

Gautam Vaja -

kigazon profile image

React Memo Mayhem: Optimizing Functional Components

Josef Held -

temaweb10 profile image

How I made a file downloader on React and posted it on npm

temaweb10 -