Viewed   561 times

I have a PHP form which enters data into my MySQL database. My primary key is one of the user-entered values. When the user enters a value that already exists in the table, the MySQL error "Duplicate entry 'entered value' for key 1" is returned. Instead of that error, I would like to alert the user that they need to enter a different value. Just an echoed message or something. I guess my question comes down to: how to turn a specific MySQL error into a PHP message Thanks

edit: nickf's answer below is nice, but is there any way to discern between specific errors?

 Answers

2

To check for this specific error, you need to find the error code. It is 1062 for duplicate key. Then use the result from errno() to compare with:

mysqli_query('INSERT INTO ...');
if (mysqli_errno() == 1062) {
    print 'no way!';
}

A note on programming style
You should always seek to avoid the use of magic numbers (I know, I was the one to introduce it in this answer). Instead, you could assign the known error code (1062) to a constant (e.g. MYSQLI_CODE_DUPLICATE_KEY). This will make your code easier to maintain as the condition in the if statement is still readable in a few months when the meaning of 1062 has faded from memory :)

Saturday, October 22, 2022
4

You can use magic constants, or debug_backtrace, or write your own error handler.

Tuesday, October 11, 2022
4

You could use a temporary table. Have the php app insert everything into the temp table and then call a query that with this sort of logic.

insert into mainTable 
(field1, field2, etc)
select field1, field2, etc
from tempTable 
where (subquery to check for existing records goes here)

Or you could use try/catch. I don't know php syntax but since many other languages can do this sort of thing, I would expect php to be able to as well.

try
code to insert record
catch
if the error code is the one for duplicate records, do nothing.
if it's something else, handle it accordingly.
Thursday, October 13, 2022
 
cfort
 
3

you can handle the mysql error in this way

 if ( ! $this->db->query('SELECT `name`,`age` FROM `example`'))
{
        $error = $this->db->error(); // Has keys 'code' and 'message'
}
Wednesday, December 14, 2022
 
4

For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement. Instead, use PDO::query() to issue a SELECT COUNT(*) statement with the same predicates as your intended SELECT statement, then use PDOStatement::fetchColumn() to retrieve the number of rows that will be returned. Your application can then perform the correct action.

Wednesday, August 17, 2022
 
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :