Inserting Duplicate Records

[eluser]zorka[/eluser]
Hello,

I'm relatively new to Code igniter and PHP in general. I'm working on an app that does alot of inserts into a databases. If at all possible, I'd like just to try an insert, then check for a return value to see whether the insert worked OR whether it did not (due to a constraint being violated, such as duplicate UNIQUE or PRIMARY key)... This makes for simpler code and I believe, more efficient design than doing a SELECT STATEMENT (i.e. checking to see if a given UNIQUE field exists), then the INSERT statement if successful.

Something like:

$returnVal=$db->insert('users',$user);
if ($returnVal) {
echo "success";
}

How do I do this? Am I correct in my assumption that this is more efficient than doing a select (traversing the dbase searching for the record) then inserting (traversing again to insert it)?

ThanX,
Ben

[eluser]manilodisan[/eluser]
Wrong! you're performing another SQL just to see if a record exists when you should use INSERT IGNORE which ignores if a unique key or primary key already exists on that field:

Code:

function add_keyword ( $keyword )
{
    return ( $this->db->query
    (
        'INSERT IGNORE  
        INTO
            keywords
            (keyword)
        VALUES
            (' . qstr ( $keyword ) . ')'
    ) ) ? $this->db->call_function ( 'insert_id' ) : FALSE;
}

..where 'qstr' is the function we use in webber to escape fields.

[eluser]zorka[/eluser]
<quote>
Wrong! you’re performing another SQL just to see if a record exists</quote>

Don't you mean RIGHT? I asked if 'this' (attempting insert WITHOUT checking via select) is faster than doing the select and THEN an insert... I think omitting the word "and" in the last sentence may have nixed it...

In any case - your solution is exactly what I was looking for, I can now attempt insert without having to traverse the database twice due to a pre-insert-select-check...

ThanX