CRUD Operations using PHP, MySQL, APACHE on LINUX

 

 Creating a REST API with PHP and MySQL

In this blog, we will guide you through the process of creating a simple REST API using PHP and MySQL. We'll be using a student management database.

Prerequisites

• 1. PHP, MySQL installed on your server. If not click here to see installation process.
• 2. MySQL database with a 'student_management' database. if not click here for MySQL database configuration.

Step 1: Database Configuration (db_config.php)

Create a file named db_config.php in the /var/www/html/rest/ directory to handle the database connection.

Database Connection Parameters:

                $host = "localhost";

                $username = "username";

                $password = "password";

                $database = "student_management";

`$host`: The hostname or IP address of the MySQL server. In this case, its set to "localhost," meaning the MySQL server is on the same machine as the PHP script. `$username`: The MySQL users username used to connect to the database. `$password`: The password for the MySQL user. `$database`: The name of the MySQL database to connect to.

Creating a Database Connection:                 $conn = new mysqli($host, $username, $password, $database); This line creates a new MySQLi object (`$conn`) representing the database connection. The `mysqli` class is part of the MySQLi extension in PHP, providing an object-oriented interface to interact with MySQL databases.

Checking for Connection Errors:

                if ($conn->connect_error) {

                   die("Connection failed: " . $conn->connect_error);

                   }

These lines check if there was an error during the database connection. If an error occurs, the `die` function is called, and the script execution is terminated. The error message is displayed, indicating the reason for the connection failure.

Replace username and password with your MySQL credentials. click to download db_config.php

Step 2: CRUD Operations (functions.php)

Create a file named functions.php in the same directory to handle CRUD operations.

1. Including Database Configuration                         require_once 'db_config.php';

This line includes the `db_config.php` file, which contains the configuration details for connecting to the MySQL database.

2. Function: `getAllStudents()` :

                    function getAllStudents() {

                                                                global $conn; $result = $conn->query("SELECT id,                                                                     name, age, grade FROM students");

                                                                $students = [];

                                                                  while ($row = $result->fetch_assoc()) {

                                                                            $students[] = $row;

                                                                    }

                                      return $students;

                        }

Purpose: Retrieves all student records from the database.

Steps: 1. Executes a SQL query to select all columns (`id`, `name`, `age`, `grade`) from the                     `students` table.

            2. Fetches each row from the result set and appends it to the `$students` array.

            3. Returns the array containing all student records.

3. Function: `getStudentById($id):

                    function getStudentById($id) {

                                                                    global $conn; $stmt = $conn->prepare("SELECT                                                                     id, name, age, grade FROM students WHERE id= ?");

                                                                    $stmt->bind_param('i', $id);

                                                                    $stmt->execute();

                                                                    $result = $stmt->get_result();

                                return $result->fetch_assoc();

                    } Purpose: Retrieves a specific student record by ID from the database.

Steps:

1. Prepares a SQL statement with a parameterized query to select a student by ID.

2. Binds the ID parameter using `$stmt->bind_param`.

3. Executes the prepared statement and retrieves the result set.

4. Returns the associative array representing the student record.

4. Function: `createStudent($name, $age, $grade):                     function createStudent($name, $age, $grade) {                                               global $conn;                                               $stmt = $conn->prepare("INSERT INTO students (name,                                                      age, grade) VALUES (?, ?, ?)");                                               $stmt->bind_param('sis', $name, $age, $grade);                                 return $stmt->execute();                             } Purpose: Creates a new student record in the database. Steps: 1. Prepares a SQL statement with a parameterized query to insert a new student. 2. Binds the parameters (`$name`, `$age`, `$grade`) using `$stmt->bind_param`. 3. Executes the prepared statement and returns `true` if successful, `false` otherwise. 5. Function: `updateStudent($id, $name, $age, $grade)`                       function updateStudent($id, $name, $age, $grade) {                                                      global $conn;                                                      $stmt = $conn->prepare("UPDATE students SET name                                                           = ?, age = ?, grade = ? WHERE id = ?");                                                       $stmt- >bind_param('sisi', $name, $age, $grade, $id);                                   return $stmt->execute();                          }

Purpose: Updates an existing student record in the database. Steps: 1. Prepares a SQL statement with a parameterized query to update a student by ID. 2. Binds the parameters (`$name`, `$age`, `$grade`, `$id`) using `$stmt->bind_param`. 3. Executes the prepared statement and returns `true` if successful, `false` otherwise. 6. Function: `deleteStudent($id):

                    function deleteStudent($id) {                                         global $conn;                                         $stmt = $conn->prepare("DELETE FROM students WHERE id = ?");                                          $stmt->bind_param('i', $id);                                 return $stmt->execute();                        } Purpose: Deletes a student record from the database by ID. Steps: 1. Prepares a SQL statement with a parameterized query to delete a student by ID. 2. Binds the ID parameter using `$stmt->bind_param`. 3. Executes the prepared statement and returns `true` if successful, `false` otherwise.

These functions use MySQLi prepared statements to perform secure and efficient CRUD operations on the `students` table in the database.

Click here to download functions.php

Step 3: REST API (api.php)

Now, create the api.php file to handle REST API requests.

1. Retrieving HTTP Method and URI Segments:                 $method = $_SERVER['REQUEST_METHOD'];                 $uriSegments = explode('/', trim($_SERVER['REQUEST_URI'], '/'));                 $endpoint = isset($uriSegments[2]) ? $uriSegments[2] : null;                 $id = isset($uriSegments[3]) ? $uriSegments[3] : null; - Purpose: Extracts information about the HTTP method, endpoint, and ID from the request URI. - $method: Retrieves the HTTP method used in the request (GET, POST, PUT, DELETE). - $uriSegments: Breaks down the request URI into an array of segments. - $endpoint: Identifies the endpoint from the URI (e.g., 'students'). - $id: Retrieves the ID if present in the URI. 2. Setting Response Headers:

                header('Content-Type: application/json');

- Purpose: Sets the response headers to indicate that the content type is JSON.

3. Handling the Request Based on HTTP Method:                 switch ($method) {                  case 'GET':                  // ... (explained in the next section)                  break;                  case 'POST':                  // ... (explained in the next section)                  break;                  case 'PUT':                  // ... (explained in the next section)                  break;                  case 'DELETE':                  // ... (explained in the next section)                  break;                  default:                  http_response_code(405);                  echo json_encode(['error' => 'Method not allowed']);                  break;                 } - Purpose: Routes the request to the appropriate action based on the HTTP method. - For each method (GET, POST, PUT, DELETE), specific actions are taken. If the method is not recognized, a "Method not allowed" error is returned. 4. Handling GET Requests:                 if ($endpoint === 'students' && empty($id)) {                  // Retrieve all students                  $students = getAllStudents();                  echo json_encode($students);                 } elseif ($endpoint === 'students' && is_numeric($id)) {                  // Retrieve a specific student by ID                  // ... (explained in the next section)                 } else {                  http_response_code(404);                  echo json_encode(['error' => 'Endpoint not found']);                 } - Purpose: Handles GET requests for retrieving student information. - If the endpoint is 'students' and no ID is provided, it retrieves all students and returns them as JSON. - If the endpoint is 'students' and a numeric ID is provided, it retrieves the student with that ID and returns it as JSON.

- If the endpoint is not recognized, a "Endpoint not found" error is returned.

5. Handling POST Requests:

                if ($endpoint === 'students' && empty($id)) {                  // Create a new student                  // ... (explained in the next section)                 } else {                  http_response_code(404);                  echo json_encode(['error' => 'Endpoint not found']);                 }

                 // Create a new student                  // ... (explained in the next section)                 } else {                  http_response_code(404);                  echo json_encode(['error' => 'Endpoint not found']);                 } - Purpose: Handles POST requests for creating a new student. - If the endpoint is 'students' and no ID is provided, it attempts to create a new student using the data from the request body.

- If the endpoint is not recognized, a "Endpoint not found" error is returned.

6. Handling PUT Requests:                 if ($endpoint === 'students' && is_numeric($id)) {                  // Update an existing student                  // ... (explained in the next section)                 } else {                  http_response_code(404);                  echo json_encode(['error' => 'Endpoint not found']);                 } - Purpose: Handles PUT requests for updating an existing student. - If the endpoint is 'students' and a numeric ID is provided, it attempts to update the student with that ID using the data from the request body.

- If the endpoint is not recognized, a "Endpoint not found" error is returned.

7. Handling DELETE Requests:

                if ($endpoint === 'students' && is_numeric($id)) {                  // Delete a student                  // ... (explained in the next section)                 } else {                  http_response_code(404);                  echo json_encode(['error' => 'Endpoint not found']);                 } - Purpose: Handles DELETE requests for deleting an existing student. - If the endpoint is 'students' and a numeric ID is provided, it attempts to delete the student with that ID.

- If the endpoint is not recognized, a "Endpoint not found" error is returned.

8. Handling Database Operations (GET, POST, PUT, DELETE):

a. Handling GET Requests for Specific Student:

                if ($endpoint === 'students' && is_numeric($id)) {                  // Retrieve a specific student by ID                  $student = getStudentById($id);                  if ($student) {                  echo json_encode($student);                  } else {                  http_response_code(404);                  echo json_encode(['error' => 'Student not found']);                  }                 } - Purpose: Retrieves a specific student by ID for GET requests. - Calls getStudentById($id) to fetch the student record. - If the student is found, it is returned as JSON; otherwise, a "Student not found" error is returned. b. Handling POST Requests for Creating a New Student                 if ($endpoint === 'students' && empty($id)) {                  // Create a new student                  $data = json_decode(file_get_contents('php://input'), true);                  $name = $data['name'] ?? '';                  $age = $data['age'] ?? '';                  $grade = $data['grade'] ?? '';                  if (createStudent($name, $age, $grade)) {                  echo json_encode(['message' => 'Student created successfully']);                  } else {                  http_response_code(500);                  echo json_encode(['error' => 'Failed to create student']);                  }                 } - Purpose: Creates a new student for POST requests. - Reads the JSON data from the request body using json decode. - Calls createStudent($name, $age, $grade) to insert a new student record. - Returns a success message or an error if the creation fails. c. Handling PUT Requests for Updating an Existing Student                 if ($endpoint === 'students' && is_numeric($id)) {                  // Update an existing student                  $data = json_decode(file_get_contents('php://input'), true);                  $name = $data['name'] ?? '';                  $age = $data['age'] ?? '';                  $grade = $data['grade'] ?? '';                  if (updateStudent($id, $name, $age, $grade)) {                  echo json_encode(['message' => 'Student updated successfully']);                  } else {                  http_response_code(500);                  echo json_encode(['error' => 'Failed to update student']);                  }                 }

click here to download api.php

Step 4: Testing the REST API (api.php)

            1. Retrieve All Students (GET Request):

                Open your web browser and navigate to http:/localhost/api/students

                You will get all students details in JSON.

        2. Retrieve a specific student by ID (GET Request):

                Open your web browser and navigate to http:/localhost/api/students/<ID>

                You will get all details of a student corresponding to ID in JSON.

      

Make sure to replace http://localhost/rest with the actual base URL where your API is hosted.

Blog by

KHUSHI