PHP MySQL INSERT fails due to unique constraint

Question 1

On insert I am catching the unique constraint mysql_errno() 1062.

This works fine but I want to find the existing row to re-instate or modify it.

Is there are method to obtain the row id on insert fail? I tried mysql_insert_id() but realised that would only return the row I'm inserting (or failed to insert) therefore, I get 0.

Is there no option but to issue another mysql_query and simply perform a select on the duplicate value?

I just want to make sure there is no better, quicker, more economical way to do this.

Question 2

If you are attempting to insert a row if new or update existing values then REPLACE INTO is what you need. Also consider INSERT ... ON DUPLICATE KEY UPDATE Syntax if there are constraints involved as REPLACE INTO will DELETE and then INSERT.

Question 3

showdev's comment is correct that the PDO DSN does not allow host:port syntax.

If your CMS is defining DB_HOST outside of your control, you can't use that constant directly. But you can pull information out of it.

$host_port = preg_replace('/:(d+)/', ';port=${1}', DB_HOST);
$db = new PDO("mysql:host={$host_port};dbname=".DB_NAME.";charset=utf8", 
    DB_USER, DB_PW, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

Question 4

Escape your encrypted string

mysql-real-escape-string

mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: x00, n, r, , ', " and x1a.

See StripSlashes

Question 5

When you create an index that only uses a prefix (by specifying the length of the index), then the prefix can be up to 1000 bytes (see 7.5.1 Column Indexes). Use SHOW CREATE TABLE to find out the actual length of the index.

Question 6

The issue is because when you migrated your database, mysql helpfully restarts the id columns that are auto-increment and Magento has some hardcoded values that assume that IDs start from zero. You need to disable foreign_key_checks when you import the data.

PHP MySQL INSERT fails due to unique constraint

Answers

php,mysql,unique-constraint,mysql-error-1062