React CRUD operations using PHP API and MySQL
Tags: react, php, mysql
Published: 2022-01-18 13:38:34

## 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/](https://www.wampserver.com/en/)
**LAMP for Linux**
[https://bitnami.com/stack/lamp/installer](https://bitnami.com/stack/lamp/installer)
**MAMP for Mac**
[https://www.mamp.info/en/mac/](https://www.mamp.info/en/mac/)
**XAMPP: Open Source, Cross-platform**
[https://www.apachefriends.org/index.html](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](https://github.com/durgesh-sahani/react-crud-php-api-mysql)

Thank you for reading this blog.