Current Location: Home> Latest Articles> How to Precisely Return Database Error Status Codes in RESTful API Development Using mysqli::$errno to Improve Error Handling

How to Precisely Return Database Error Status Codes in RESTful API Development Using mysqli::$errno to Improve Error Handling

M66 2025-07-18

Error handling is a critical part of RESTful API development, as it directly impacts the API's reliability and user experience. When the API interacts with a database, any errors from the database can jeopardize the stability of the interface. Thus, efficiently and precisely capturing these errors becomes essential. In PHP, the mysqli::$errno function is a powerful tool that allows developers to return specific database error codes, improving the robustness of API error handling.

1. Understanding mysqli::$errno

mysqli::$errno is a property provided by the MySQLi extension, used to retrieve the error code from the most recent database operation. When an error occurs, MySQL returns an error code, which mysqli::$errno captures. This is typically used in combination with the mysqli::$error property, which provides the detailed error message.

2. Application in RESTful APIs

RESTful APIs aim to provide a consistent, simple, and understandable interface for frontend clients. Database operations are one of the most common tasks during development. To improve API stability and usability, mysqli::$errno can be used to capture database errors and return appropriate status codes to frontend developers or end users.

Basic Implementation Steps

  1. Connect to the Database
    Use mysqli to establish a database connection and ensure it's successful.

  2. Perform Database Operations
    Execute operations such as insert, update, or query.

  3. Check and Capture Errors
    If a database error occurs, use mysqli::$errno to get the error code and return an appropriate status code based on it.

  4. Return Error Response
    Include the error code and message in the API response so that the frontend can handle different types of errors accordingly.

Example Code:

<?php  
// Create database connection  
$mysqli = new mysqli("localhost", "user", "password", "database");  
<p>// Check if connection is successful<br>
if ($mysqli->connect_error) {<br>
die("Connection failed: " . $mysqli->connect_error);<br>
}</p>
<p>// Execute a database operation<br>
$sql = "INSERT INTO users (name, email) VALUES ('John Doe', '<a class="cursor-pointer" rel="noopener">john@example.com</a>')";<br>
if ($mysqli->query($sql) === TRUE) {<br>
echo "New record inserted successfully";<br>
} else {<br>
// Capture and handle database error<br>
$errorCode = $mysqli->errno;<br>
$errorMessage = $mysqli->error;</p>
$response = [  
    "status" => "error",  
    "code" => $errorCode,  
    "message" => "Database error: " . $errorMessage  
];  

// Output JSON-formatted error response  
header('Content-Type: application/json');  
echo json_encode($response);

}

// Close database connection
$mysqli->close();
?>

Error Handling Explained:

  • $mysqli->errno: If the database operation fails, $mysqli->errno will return the MySQL error code. For example, a SQL syntax error returns error code 1064.

  • $mysqli->error: Used alongside errno, $mysqli->error returns a detailed error message, such as "You have an error in your SQL syntax."

3. Enhancing API Error Handling Capabilities

Using the method described above allows you to provide detailed error codes and messages. Robust error handling includes not just capturing the error correctly, but also the following aspects:

  1. Consistent Error Format: All API error responses should use a consistent structure—typically including status, code, and message fields—making it easier for frontend developers to process errors.

  2. Error-Specific Handling: Based on the error code returned by mysqli::$errno, the API can return different HTTP status codes. For example, a connection error might return a 500, while user input errors return a 400.

  3. Logging: Log detailed error messages on the API server to facilitate debugging and future system improvements. PHP’s error_log() function can be used to write errors to a log file.

  4. User-Friendly Error Messages: To prevent exposing internal system details to end users, avoid returning raw database errors. Instead, show generic but meaningful messages to the user.

4. Handling Common Database Errors with mysqli::$errno

After obtaining the database error code using mysqli::$errno, different error messages can be returned based on the specific code:

  • 1045 - Authentication Failure
    When MySQL user authentication fails, $mysqli->errno returns 1045. Return an appropriate authentication failure message.

  • 1064 - SQL Syntax Error
    If the SQL statement has syntax errors, $mysqli->errno will return 1064. The frontend can prompt the user to check their SQL input.

  • 2002 - Connection Timeout
    If the database connection times out, $mysqli->errno returns 2002. The user can be prompted to check their network or database server.

5. Summary

By using PHP’s mysqli::$errno function, developers can precisely capture and handle database errors. This is crucial for RESTful API development, as accurate error reporting enhances both API reliability and user experience. A good error-handling strategy includes standardized response formats, detailed error data, logging mechanisms, and tailored responses for specific error codes.

Horizontal Divider

  • Related Tags:

    API
Related