Handling Duplicate Records Without Double Writes
Using INSERT IGNORE handles duplicate keys by silently discarding the insert operation. While this prevents the database from throwing a fatal error, it leaves your application logic in an undefined state. You have no way of knowing whether the row was actually inserted or ignored unless you explicitly check the result. This is why your script throws an "Error Querying Database" message when combined with a custom error handler that assumes every query must succeed.
To satisfy your requirementâdisplaying "Customer added" for new records and "Client already listed" for duplicatesâyou need to inspect the outcome of the INSERT operation. In MySQL, the function mysqli_insert_id() returns the generated auto-increment ID if the insert succeeded, and 0 if it failed (either due to a duplicate key or other error). However, since your primary key is a composite index on (firstName, lastName) rather than an auto-increment integer column, checking the return value of the query function itself is often more reliable for this specific scenario.
The most robust approach involves checking the status of the query using mysqli_error(). If you switch from INSERT IGNORE to a standard INSERT, the database will stop execution immediately upon encountering a duplicate key violation and return an error code. You can catch this specific error code (1062) in your PHP script to distinguish between a successful insertion and a duplicate entry.
Here is how you can modify frm_script.php to handle both cases correctly:
<?php
if(isset($_POST['submit']))
{
// Sanitize inputs to prevent SQL injection
$firstName = mysqli_real_escape_string($dbc, $_POST['firstName']);
$lastName = mysqli_real_escape_string($dbc, $_POST['lastName']);
// Connect to the database
if(!mysqli_connect('host', 'username', 'password', 'dbname')) {
die('Error connecting to MySQL server');
}
// Attempt a standard INSERT instead of IGNORE
// This will trigger an error on duplicate keys, allowing us to catch it
$query = "INSERT INTO clients(firstName, lastName) VALUES('$firstName', '$lastName')";
$result = mysqli_query($dbc, $query);
if (!$result) {
// Check the specific error code returned by MySQL
// 1062 indicates a duplicate entry for a PRIMARY KEY or UNIQUE constraint
$error_code = mysqli_errno($dbc);
if ($error_code == 1062) {
echo "Client already listed";
} else {
die('Database Error: ' . mysqli_error($dbc));
}
} else {
// If we reach here, the insert was successful
echo "Customer added";
}
mysqli_close($dbc);
}
?>
Why Check for Error Code 1062?
Relying solely on mysqli_insert_id() returning 0 is ambiguous. A return value of 0 means either no row was inserted (because it was ignored) or the query failed entirely. By switching to a standard INSERT and catching the specific error code, you gain precise control over your application flow.
The error code 1062 is the industry-standard signal for "Duplicate Entry." This allows your script to differentiate between a user trying to register an existing client (which should show "Client already listed") and a generic system failure (which should likely alert the admin).
Trade-offs and Best Practices
While checking error codes works effectively, it requires handling exceptions in your code. Another common pattern is using INSERT ... ON DUPLICATE KEY UPDATE. This command attempts to insert the row; if a duplicate key exists, it updates the existing row instead of failing. However, since your table lacks an UPDATE target (like a timestamp or email), this approach might not be semantically correct for your use case unless you plan to update metadata later.
For strict data integrity where duplicates should never exist in the first place, relying on the database constraint (the composite primary key) is superior to checking application logic. If you want to avoid error handling entirely and simply accept that duplicates are ignored without showing a message, INSERT IGNORE remains validâbut only if your UI acknowledges that nothing happened. Since your UI requires feedback, detecting the 1062 error is the correct architectural choice.
If you are moving away from raw MySQLi to a modern framework like Laravel, these concerns are handled automatically via Eloquent's create() or firstOrCreate() methods, which abstract away the SQL injection risks and duplicate logic. For more details on integrating PHP with modern stacks, see https://laravelcompany.com.