visual77

While working on some code recently, I realized that mysql_insert_id fails when using insert ignore. When using insert, mysql_insert_id returns the primary key of the newly inserted row. However, nothing is returned with insert ignore if a key conflict prevents a record from being inserted. If you are wanting to get the key that a conflict was just hit against, in as dynamic of a way as possible, you can use this script to find the primary key when insert ignore does not enter a record. This is useful if you are setting up a many-to-many pivot table and don’t want duplicate data on either side. When you attempt to insert a new record, it either gives you the new key or the key of the one that already existed.

$db->query($sql)

// if an insert id exists, use it
if ($db->insert_id != 0) {
    $id = $db->insert_id;
// if there is no insert id and there was no error and insert ignore was ued
} elseif($db->insert_id == 0 &&
    empty($db->error) &&
    preg_match('/^\s*insert\s+ignore/si', $sql)) {

    // find the table that was queried
    preg_match('/^\s*insert\s+ignore\s+into\s+([-`a-zA-Z0-9_]+)/si', $sql, $extract);
    $table = trim($extract[1], '`');

    // change insert ignore  to insert
    $Sql = preg_replace('/^\s*insert\s+ignore/si', 'insert', $sql);

    // query and scan the error for the key conflict
    $db->query($sql);
    $error = $db->error;
    preg_match('/^Duplicate entry \'(.*)\' for key (\d+)$/', $error, $extract);

    $value = $extract[1];
    $key = $extract[2];

    // in the case of multi column keys, figure out what the keys actually are
    if(strstr($value, '-') && !strstr($sql, $value)) {
        $values = explode('-', $value);
        $finished = false;
        while(!$finished) {
            foreach($values as $k=>&$v) {
                if(strstr($Sql, $v.'-'.$values[$k+1])) {
                    $values[$k] = $v.'-'.$values[$k+1];
                    unset($values[$k+1]);
                    $values = array_values($values);
                    break;
                }
                $finished = true;
            }
        }
        $value = $values;
    }

    // look up all keys on the table, isolating the primary key
    $keySql = "show keys from `$table`";
    $keyResult = $db->query($keySql);
    $keys = array();
    while($row = $keyResult->fetch_assoc()) {
        if(strtolower($row['Key_name']) == 'primary') {
            $primary = $row['Column_name'];
        }
        $keys[$row['Key_name']][] = $row;
    }

    // build a where clause to find the primary based on key conflicts
    $keys = array_values($keys);
    if(!is_array($keys[$key-1])) {
        $unique = $keys[$key-1]['Column_name'];
        $where = "`$unique` = '".$db->real_escape_string($value)."'";
    } else {
        foreach($keys[$key-1] as $key) {
            $whereParts[] = "`{$key['Column_name']}` = '".$db->real_escape_string($value[$key['Seq_in_index']-1])."'";
        }
        $where = implode(' and ', $whereParts);
    }

    // get the primary key that conflicted with the insert
    $sql = "select `$primary` from `$table` where $where";
    if(is_object($result)) {
        $result = $result->fetch_assoc();
        $id = $result[$primary];
    }
}

echo $id;