How to prevent duplicate records in CodeIgniter

问题:

The person I would have selected will then appears as twice in my friends database. How do I prevent a duplicate entry going in here? I have tried using exists sql command but no luck

friends model:

   function addFriend($username, $friendname)
 {
$record = array('username'=> $username,
                 'friend' => $friendname);

$this->db->insert('friends', $record);


 }


 function getFollowing($username)
{
$following = array();
$this->db->select('*')->from('friends')->where('username', $username);
$followingSet = $this->db->get();
foreach ($followingSet->result() as $row)
{
    if(isset($username)){

        $following[] = $row->friend;
    }
    else 
    {
        return false;


    }

}

return $following;
}

view:

 <?php foreach($friends['following'] as $name):?>
        <li>  <?=anchor("profile/view/$name", $name)?>, (<?=anchor("home/drop/$name", 'drop')?>)</li>
      <?php endforeach?>=

The thing I want to do is stop duplicate entries going in my database - how would I use the exists keyword in my sql statement?

回答1:

When you insert you can call your getFollowing method and go like this:

function add_follower($username, $friend_username)
{
    $current_followers = $this->getFollowing($username);

    if(!in_array($friend_username, $current_followers)
    {
        // Add to followers
    }
 }

For this you need to change your getFollowing to this:

function getFollowing($username)
{
    $following = array();

    $this->db->select('*')->from('friends')->where('username', $username);

    $followingSet = $this->db->get();

    foreach ($followingSet->result() as $row)
    {
        $following[] = $row->friend;
    }
    return $following;
}

This will not work 100% right probably, because I have no idea how your database / models are configured. But it should give you a general idea on how to do this

回答2:

Just perform a check to see if the username/friendname exists before inserting:

$q =  $this->db->select('username')
      ->from('friends')
      ->where(array('username' => $username, 'friend' => $friendname))->get();
if($q->num_rows() == 0){
    //insert goes here
}

回答3:

I think your didn't set up your tables for better performance.

CREATE TABLE friends (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
userId INT UNSIGNED NOT NULL,
friendId INT UNSIGNED NOT NULL,
FOREIGN KEY (userId)
    REFERENCES users(userId)
    ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (friendId)
    REFERENCES users(userId)
    ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE friends ADD UNIQUE INDEX (userId, friendId);

This clear and simple solution to store friends by id and will prevent duplicating. InnoDB engine maintains your data integrity, and when you delete somebody from users table - all rows in friends table with wriends of this users will be deleted automatically.

回答4:

You have to make a check in database before inserting the new record that is there any entry already in database. Below is the code...

function addFriend($username, $friendname)
{
    $user_name = $this->getFollowing($username);

    if( empty($user_name) )
    {
         $record = array('username'=> $username,
             'friend' => $friendname);

         $this->db->insert('friends', $record);
    }
}

function getFollowing($username)
{
    $following = array();
    $this->db->select('*')->from('friends')->where('username', $username);
    $followingSet = $this->db->get();

    if( !empty($followingSet) )
    {
         foreach ($followingSet->result() as $row)
         {
              $following[] = $row->friend;
         }
    }
    return $following;
 }

Also i would like you to suggest, always check that an array is not empty before using it. Like you used it in foreach loop, the loop will generate an error.

回答5:

Try This where you want to apply query for reading something, Apply distinct in db query

   $this->db->distinct();

回答6:

In your Model try something like this:

/**
 * Return true if don't find row in DB.
 * @param array $ay_value
 * @param array $ay_column
 * @param string $table
 * @throws Exception
 * @return boolean
 */

public function check_double_content($ay_value, $ay_column, $table) {

    if ((is_array($ay_value)) AND (is_array($ay_column))) {

        if (count($ay_value) == count($ay_column)) {

            foreach ($ay_column AS $key => $column) {
                $this->db->where($column, $ay_value[$key]);
            }

            $this->db->from($table);
            $query = $this->db->get();

            if ($query->num_rows() > 0) {
                return false;
            } else {
                return true;
            }



        } else {
            throw new Exception("\$ay_value and \$ay_coulm must have same rows");
        }

    } else {
        throw new Exception("\$ay_value and \$ay_column must by array");
    }

}