Avoiding duplicates Database records in a datamapper code igniter

Question

My Question is simply how to insert data into the join table(user_book) without inserting a duplicate entry into the main table(book).

Mainly consider we have tree table below

 id  Users id Books  id Users Books 
  1   Sam   1   B1     1   1    1
  2   Ben   2   B2     2   1    3  
  3   Mike  3   B3     3   3    3
                       4   2    2

But the problem is when I am inserting a new book(ex Mike like B3) that exist in the Books table. The duplicate will appear in the books table and the above table will be like:

  id  Users id Books  id Users Books 
  1   Sam   1   B1     1   1    1
  2   Ben   2   B2     2   1    3  
  3   Mike  3   B3     3   3    4
            4   B3     4   2    2

Is that make sense now what I am trying to resolve? Or maybe I can't have a unique list of books at all?

if I am going to insert Given the database users and books users<--->books are related by users_books we need to make sure that when we insert a book record to the db it is not a duplicate. Further, if the book exist, a relation will be inserted and not the book record . How can one do that? One approach is to say

$m=new Book();  
$m->where('id_book', $data['user_booklist'][$i]['id'])->get();
$u = new User();
$u -> where('id_user', $data['user_profile']['id'])-> get();
if(!$u->where_related($m))
{
$m -> name = $data['user_booklist'][$i]['name'];
$m -> id_book = $data['user_booklist'][$i]['id'];                                               
$m -> save($u);  
}

if (book->exist) in the "books" table then check the relation and see if there is a relationship between users and books and if there is then don't insert it. Further do I need to change any thing in the mysql db structure to avoid this.

The above code is not working but should give an idea what I am talking about.

Update:

In summary, if two users liked the same book, I just want to insert a record(book) into the join table (users_books) and while not to creating a new record in the table. Using the unique sql tag did not work since it keeps unique list but it prevents from inserting the multiple relationships into the join table users_books.

Why is the above code not working? Looks the right way to go. — Zombaya, May 02 '12 at 09:16

score 1 · Accepted Answer · answered May 02 '12 at 09:37

You could do:

$b = new book();  
$b->where('id_book', $data['user_booklist'][$i]['id'])->get();
// I believe you can do this as well, but I'm not sure:
// $b->where('id', $data['user_booklist'][$i]['id'])->get();

$u = new user();
$u -> where('id_user', $data['user_profile']['id'])-> get();

if( $b->exists() && $u->exists() )
{
   // This saves the relationship between the book and the user
   $b->save($u);
}
else  
{
   // Could not find the book or the user
}

You should check out the datamapper-manual on saving relationships.

Avoiding duplicates Database records in a datamapper code igniter

1 Answers1