Distinguishing Success from Failure in MySQL Inserts
Your current approach relies on executing an INSERT statement and blindly echoing "Inserted" regardless of the outcome, or dying immediately if a critical error occurs. To achieve the goal of returning specific messages based on success or failure without terminating the script for every minor issue, you must decouple the query execution from the immediate output logic.
The fundamental problem lies in how mysql_query() interacts with your flow. When using the deprecated mysql_* extension (which is removed as of PHP 7.0), if a syntax error or connection failure occurs, mysql_query() returns false. However, if the query executes successfully, it returns true. Your current code checks for errors only via die(), which halts execution on any error before you can even print your custom message.
To fix this, you need to check the return value of the query function explicitly and handle the logic within a conditional block. This allows the script to continue running and print a "fail" message if the insertion did not succeed, while only printing "Inserted" when the operation completes without errors.
Here is how you should structure the code using the mysql_* functions for compatibility with your existing snippet:
<?php
// Establish connection
$conn = mysql_connect("localhost", "root", "123");
if (!$conn) {
die('Connection failed: ' . mysql_error());
}
// Select database
mysql_select_db("swm_database") or die(mysql_error());
// Execute the insert query
$result = mysql_query("INSERT INTO PEOPLE (NAME) VALUES ('COLE')");
// Check if the result is true/false
if ($result === true) {
echo "Inserted";
} else {
// This block executes only if the insertion failed
echo "fail: " . mysql_error();
}
mysql_close($conn);
?>
In this revised structure, $result captures the boolean return value of the query. If mysql_query() encounters a constraint violation (like a duplicate key), a syntax error, or an empty result set where one is expected, it returns false. The if statement then evaluates this truthiness. If it is false, the script enters the else block, allowing you to echo "fail" along with the specific error message retrieved from mysql_error(). This satisfies your requirement to distinguish between a successful write and an unsuccessful one without crashing the application immediately for every potential edge case.
It is critical to note that the code above uses the mysql_* extension, which is considered insecure and obsolete due to its lack of prepared statements and susceptibility to SQL injection attacks. In a production environment, you should migrate this logic to mysqli or PDO. These modern extensions provide better error handling mechanisms and security features. For instance, using mysqli with prepared statements would look like this:
<?php
$host = "localhost";
$username = "root";
$password = "123";
$dbname = "swm_database";
$conn = new mysqli($host, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$stmt = $conn->prepare("INSERT INTO PEOPLE (NAME) VALUES (?)");
$stmt->bind_param("s", $name);
$name = "COLE";
if ($stmt->execute()) {
echo "Inserted";
} else {
echo "fail: " . $stmt->error;
}
$stmt->close();
$conn->close();
?>
This approach offers a more robust way to handle the insertion logic, ensuring that your application provides clear feedback to the user or downstream systems. If you are building a larger application and want to standardize these database interactions across your project, exploring modern PHP frameworks can streamline this process further. Resources like https://laravelcompany.com offer insights into how such patterns are handled in enterprise-grade applications, often abstracting these low-level checks into Eloquent models or service classes for cleaner code architecture.
