php.sqlstate()

Modern web applications have become extremely flexible and adaptive depending on the required needs and stack changes. However, one stack still remains prevalent even as web development has changed and that is relational databases; particularly MySQL.

One of the most influential and easiest language to use when working with MySQL databases is PHP. This is because it provides a very compatible layer and an extensive set of functions that allows us to work with MySQL databases.

One of those functions is the sqlstate() function. This function allows to return the SQLSTATE error from a previous MySQL operation.

Let us discuss how we can use this function to gather the SQLSTATE error code for the last MySQLi function call.

PHP SQLSTATE() Function

Let us start at the basics and explore the function definition for the sqlstate() function. The following shows the method definition as:

Objected-Oriented

string $mysqli->sqlstate;

Procedural Style

mysqli_sqlstate(mysqli $mysql): string

Function Return Value

The function returns a string containing the SQLSTATE code for the last error. The error code consists of five character with each sequence denoting the category of the error.

What is SQLSTATE Code?

SQLSTATE is a five-character code that represents the status or outcome of an SQL operation. It is used by database management systems (DBMS) to signal errors or warnings to the calling program or application.

The SQLSTATE code is composed of two parts: a two-character class code, which specifies the general category of the error, and a three-character subclass code, which provides more specific information about the error.

For example, the SQLSTATE code ‘42S02’ indicates an error in the SQL statement syntax, where ‘42’ represents the class code for syntax errors, and ‘S02’ represents the subclass code for tables not found.

SQLSTATE codes are standardized by the SQL standard and are implemented by most relational database management systems, including MySQL, Oracle, SQL Server, PostgreSQL, and others.

Example Code

The following examples demonstrates how to use the sqlstate() function in PHP using both procedural and objected-oriented method.

Object-Oriented Example

The example code is as shown:

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("localhost", "root", "mysql", "sakila");

// Check for connection errors
if ($mysqli->connect_errno) {
  echo "Failed to connect to MySQL: " . $mysqli->connect_error;
  exit();
}

// Execute an SQL statement that may cause an error
if (!$mysqli->query("SELECT * FROM no_table;")) {
  // Print the SQLSTATE code and error message
  echo "SQLSTATE error code: " . $mysqli->sqlstate . "\n";
  echo "Error message: " . $mysqli->error . "\n";
}

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

?>

In the example above, we use the SQLSTATE() function of the mysqli object to retrieve the SQLSTATE code and the error message.

Procedural Style Example

<?php

// Establish a connection to the MySQL database using default credentials
$mysqli = mysqli_connect("localhost", "root", "mysql", "sakila");

// Check for connection errors
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  exit();
}

// Execute an SQL statement that may cause an error
if (!mysqli_query($mysqli, "SELECT * FROM no_table")) {
  // Print the SQLSTATE code and error message
  echo "SQLSTATE error code: " . mysqli_sqlstate($mysqli) . "\n";
  echo "Error message: " . mysqli_error($mysqli) . "\n";
}

// Close the database connection
mysqli_close($mysqli);

?>

This should work in a similar manner.

End

This post covered the fundamentals of using the PHP sqlstate() function to fetch the SQLSTATE code for a previously executed MySQLi command.