CODEWITHSUNDEEP
phpcodeigniter
StormShadow
StormShadow

Reputation: 1627

CodeIgniter: how do I check DB table for duplicates before entry?

I'm writing a small application that will write the contents of a csv file to a database in CodeIgniter, but I'm having difficulty checking for dupes before write. Since this is a fairly common task, I was wondering how some of the professionals (you) do it. For context and comparison, here is my (currently non-working) code:

        $sql = "SELECT * FROM salesperson WHERE salesperson_name='" . $csvarray['salesperson_name'][$i] . "'";
        $already_exists = $this->db->query($sql); 

        if (sizeof($already_exists) > 0) {

            //add to database

        }

Any input at all short of "You suck!" (I already hear that from my own brain twice an hour at this point) will be greatly appreciated. Thanks.

Upvotes: 0

Views: 6931

Answers (2)

Christian
Christian

Reputation: 19740

What you're doing will work, but as above the comparison should be "== 0" not "> 0".

You can also use CodeIgniters built in num_rows method instead of using sizeof, like so:

$sql = "your query";
$query = $this->db->query($sql);

if ($query->num_rows() == 0) {
  // no duplicates found, add new record
}

As a side note, if you're using CodeIgniter then it's worthwhile to use Active Record to build your queries instead of writing SQL. You can learn more about it here: CodeIgniter - Active Record

Upvotes: 2

Manny
Manny

Reputation: 11

Firstly you probably want (rather than >0) 

if (sizeof($already_exists) == 0) {
    //add to database
}

Also could just push all your data to a temporary staging table, which you could then clean out with some spiffy sql- and then push back to your staging table.

Taking a punt on you using mysql (either that or postgres if you are phping) You could try this (taken from http://www.cyberciti.biz/faq/howto-removing-eliminating-duplicates-from-a-mysql-table/) You will have to change your .ID to your primary key

delete from salesperson_temptable 
    USING salesperson_temptable, salesperson_temptable as vtable
    WHERE (NOT salesperson_temptable.ID=vtable.ID)
        AND (salesperson_temptable.salesperson_name=vtable.salesperson_name)

then when you review the contents of salesperson_temptable you can then insert it back to the salesperson table

Upvotes: 1

Related Questions