How do I display a MySQL error in PHP for a long query that depends on the user input?

To display a MySQL error in PHP for a long query that depends on user input, you can use the mysqli_stmt_error() function. This function returns a string description of the last error for a prepared statement.

Here's an example of how you can use it safely with prepared statements and proper error handling:

Example of displaying a MySQL error in PHP for a long query that depends on the user input

<?php
// 1. Establish database connection
$conn = mysqli_connect("localhost", "username", "password", "database");
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// 2. Prepare user input before binding
$username = $_POST['username']; // Example user input
$password = $_POST['password'];

// 3. Use a longer query with prepared statements to prevent SQL injection
$sql = "SELECT u.id, u.username, u.email, p.title, p.content 
        FROM users u 
        JOIN posts p ON u.id = p.user_id 
        WHERE u.username = ? AND u.status = ?";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "ss", $username, $password);

mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);

if (!$result) {
    // 4. Log the actual error securely
    $error = mysqli_stmt_error($stmt);
    error_log("Database query failed: " . $error);
    echo "An error occurred. Please try again later.";
} else {
    // Query was successful
    // Process data
}

mysqli_stmt_close($stmt);
mysqli_close($conn);
?>

In this example, $conn is a connection to the MySQL database created using mysqli_connect(). The query uses prepared statements (mysqli_prepare and mysqli_stmt_bind_param) to safely handle user input and prevent SQL injection. If the query fails, mysqli_stmt_error() returns a string describing the error. This detailed message is logged using error_log() for debugging, while a generic message is displayed to the user to avoid exposing sensitive information.