In the previous lesson, we created our first PHP REST API. Now we will create a proper GET API that retrieves all student records from the MySQL students table and returns them as JSON.
A Get All API retrieves all available records from a database table.
For our project, the endpoint will retrieve all students.
GET /api/students.phpThe response will contain multiple student records.
The HTTP GET method is commonly used to retrieve data from a server.
GET /api/students.phpThe client sends a request and the server returns the requested data.
Our student API endpoint can be:
http://localhost/rest_api/api/students.phpWhen a GET request is sent to this URL, the PHP file will retrieve the student records.
The API needs a database connection before it can execute a SQL query.
require_once "../config/database.php";The included file provides the PDO connection stored in $pdo.
Our API returns JSON, so we should set the response content type.
header("Content-Type: application/json");This tells the client that the response is JSON data.
The SQL SELECT statement is used to retrieve records.
SELECT * FROM studentsThe * means that all columns should be selected.
Using PDO, we can execute the SELECT query.
$stmt = $pdo->query(
"SELECT * FROM students"
);The result is stored in the $stmt variable.
The fetchAll() method can retrieve all rows returned by the query.
$students = $stmt->fetchAll(
PDO::FETCH_ASSOC
);Each row is returned as an associative array.
PDO::FETCH_ASSOC tells PDO to return each database row as an associative array using column names as keys.
[
"id" => 1,
"name" => "Rahul",
"email" => "rahul@example.com"
]After fetching the records, convert the PHP array into JSON.
echo json_encode($students);The JSON response can then be consumed by Postman, React Native, or another client.
<?php
header("Content-Type: application/json");
require_once "../config/database.php";
$stmt = $pdo->query(
"SELECT * FROM students"
);
$students = $stmt->fetchAll(
PDO::FETCH_ASSOC
);
echo json_encode($students);
?>This API retrieves all student records.
A better API response can contain a success status and data.
$response = [
"success" => true,
"data" => $students
];
echo json_encode($response);If the database contains two students, the API may return:
{
"success": true,
"data": [
{
"id": "1",
"name": "Rahul",
"email": "rahul@example.com",
"mobile": "9876543210",
"course": "PHP"
},
{
"id": "2",
"name": "Amit",
"email": "amit@example.com",
"mobile": "9876501234",
"course": "React Native"
}
]
}A successful GET request can return HTTP status code 200 OK.
http_response_code(200);The client can use the status code to determine whether the request was successful.
http_response_code(200);
echo json_encode([
"success" => true,
"data" => $students
]);This explicitly tells the client that the request was successful.
If there are no students in the table, fetchAll() returns an empty array.
[]The API can still return a successful response because the request itself was processed successfully.
We can count the number of retrieved records using PHP's count() function.
$total = count($students);The total can be included in the API response.
$response = [
"success" => true,
"total" => count($students),
"data" => $students
];
echo json_encode($response);This response tells the client how many records were returned.
A database query can fail. We should handle possible PDO exceptions.
try {
$stmt = $pdo->query(
"SELECT * FROM students"
);
} catch (PDOException $e) {
http_response_code(500);
}When the database operation fails, the API can return a JSON error.
http_response_code(500);
echo json_encode([
"success" => false,
"message" => "Failed to fetch students"
]);The client can use this response to display an appropriate message.
<?php
header("Content-Type: application/json");
require_once "../config/database.php";
try {
$stmt = $pdo->query(
"SELECT * FROM students"
);
$students = $stmt->fetchAll(
PDO::FETCH_ASSOC
);
http_response_code(200);
echo json_encode([
"success" => true,
"total" => count($students),
"data" => $students
]);
} catch (PDOException $e) {
http_response_code(500);
echo json_encode([
"success" => false,
"message" => "Failed to fetch students"
]);
}
?>Open Postman and create a GET request.
Method:
GET
URL:
http://localhost/rest_api/api/students.phpClick Send to execute the request.
After sending the request, check the HTTP status displayed by Postman.
200 OKA 200 status indicates that the request was successfully processed.
The response body should contain the student data.
{
"success": true,
"total": 2,
"data": [
{
"id": "1",
"name": "Rahul"
},
{
"id": "2",
"name": "Amit"
}
]
}GET Request
↓
students.php
↓
PDO Connection
↓
SELECT * FROM students
↓
fetchAll()
↓
PHP Array
↓
json_encode()
↓
JSON ResponseA React Native application can request the same API using Fetch.
fetch("http://localhost/rest_api/api/students.php")
.then(response => response.json())
.then(data => {
console.log(data);
});Later we will learn how to display this data inside React Native components.
A Get All API is useful for displaying lists of records in applications.
Examples include:
Do not automatically expose sensitive database columns through a public GET API.
For example, passwords should never be returned in a normal student list response.
SELECT id, name, email, mobile, course
FROM students;Selecting only the required columns is often better than exposing every column.
rest_api/
│
├── config/
│ └── database.php
│
└── api/
└── students.phpThe database connection is reusable while the students endpoint contains the API-specific logic.
The Get All Records API retrieves multiple student records from MySQL and returns them as JSON. The API uses a GET request, PDO for database access, a SELECT query for retrieving records, and json_encode() for creating the JSON response.
GET
↓
SELECT
↓
fetchAll()
↓
json_encode()
↓
JSON ResponseQuestion: Which SQL query is used to retrieve all records from the students table?