How to avoid duplicate content stored in MySQL database submitted by user

Question

I am creating a site that lets users list up to 5 companies they are associated with. When other users search these companies, all users associated with that company will show up in the search results.

The companies will be submitted by the users through a text input field.

How do I avoid users submitting duplicate companies? E.g. if UserA submits a company called stackoverflow, then UserB comes and also submits stackoverflow, there will be 2 stackoverflows in my database.

I have 3 tables:

Users Table

id|username|email

Company Table

id|company name

UsersCompany Table

id|userID|companyID

I'm using Laravel 5

Answer 1

You should allow user to select company, and then simply give reference of UserID, CompanyID tables in UsersCompany Table. So you will always have a unique record if UserA insert StackOverFlow and UserB also insert StackOverFlow, in your database. It will be like:

• 1-UserA-StackOverflow
• 2-UserB-StackOverFlow.

Or, if you want user to enter the company, check if the same company exists or not:

var checkcompany= "select * from Company Table where company name=company name"

Than check

if(checkcompany ! =null)

Than insert the record or else ignore it.

Answer 2

I hope this code helps you. This is a sql code to insert unique data

INSERT INTO UsersCompanyTable (userID, companyID)
SELECT * FROM (SELECT $_POST("user_id"), $_POST("company_id")) AS tmp
WHERE NOT EXISTS (
    SELECT companyID FROM UsersCompanyTable WHERE companyID = $_POST("company_id")
) LIMIT 1;

Answer 3

Try to build schema as following to get optimum performance.This structure helps you to avoid duplicate data and also do code validations in Laravel to avoid malicious inputs.

CREATE TABLE IF NOT EXISTS `Users Table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `Company Table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `company name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `company name` (`company name`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `Users Company Table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `userID` int(11) unsigned NOT NULL,
  `companyID` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `userID` (`userID`),
  KEY `companyID` (`companyID`),
  CONSTRAINT `Users Company Table_ibfk_1` FOREIGN KEY (`userID`) REFERENCES `Users Table` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `Users Company Table_ibfk_2` FOREIGN KEY (`companyID`) REFERENCES `Company Table` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;  

Answer 4

You can do it with a simple check. If the company does not exists create a new Company or attach the Company to the user.

I assume the companies are submitted in one single text input seperated by commas and you have setup your relations correct. If not check this.

Example:

// Inside your controller
public function post_something(Request $request)
{

    // Always good to validate
    $this->validate(....);

    // Somehow get your user model
    $user = ....

    // Get companies input and loop
    $company_names = $request->input('company_names');

    $company_names = explode(',', $company_names );

    foreach ($company_names as $company_name)
    {
        $company = Company::firstOrCreate(['company_name' => $company_name]);

        $user->companies()->attach($company);        
    }

    // Your other stuff
    //
    ....

}

Answer 5

On my opinion you should use Eloquent firstOrCreate method If you will use this approach, then you even no need any "unique" validation under the companies.

Lets start

DB Schema

users
id
username

companies
id
company_name

user_company
id
user_id
company_id

Models (only relations methods)

User.php

public function companies()
{
    return $this->belongsToMany('NAMESPACE_TO_YOUR_MODEL\Company');
    /*you can set foreign keys fields if it's not canonical see docs*/
}

Company.php

public function users()
{
    return $this->belongsToMany('NAMESPACE_TO_YOUR_MODEL\User');
}

Controller

public function store(CompanyRequest $request)
{
    $companiesFromRequest = $request->get('companies'); 
    // it's just for example, you can retreive companies from request with any other approach
    $companiesId = [];
    foreach ($companiesFromRequest as $company) {
        // assumes that $company variable contains all or at least part of info,
        // that need to create or identify particuliar comapny 
        $c = Company::firstOrCreate($company);
        $companiesId[] = $c->id;
    }
    // in this case we just retreive existing user
    $user = User::findOrFail($request->get('user_id'));

    $user->companies()->sync($companiesId);

    // or you can use 
    // $user->companies()->attach($companiesId);
    // difference between this commands you can found in official laravel docs

    return redirect('any/place/you/wish')
}

Answer 6

This can achieve this by either creating PRIMARY Key or UNIQUE on company_Name

  ALTER TABLE company_Table ADD PRIMARY KEY(company_Name)

  ALTER TABLE company_Table ADD UNIQUE (company_Name)

or

    IF NOT EXISTS(QUERY) Then INSERT

or
Create BEFORE INSERT trigger .

http://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html

Answer 7

This is very easily obtainable, by using UNIQUE in MySQL.

ALTER TABLE  `company_Table` ADD UNIQUE (
   `company_Name`
   )

By putting this into MySQL, the company_Name column becomes UNIQUE, meaning there can only be one. If you attempt to insert another one, an error returns.

UNIQUE can also be used on member emails on a userbase, if you don't want somebody to log in with the same email. UNIQUE is also perfect for POST_IDs, MEMBER_IDs, COMMENT_IDs, and several others, that could become very useful on a blog, forum, or social media site.

If you would like to create the UNIQUE key upon creating this table, here is how you would do so:

   CREATE TABLE Company_Table
(
ID int NOT NULL,
Company_Name varchar(255),
UNIQUE (Company_Name)
)

W3schools has a good tutorial on this: Here

Answer 8

found the answer in larval i just use firstOrNew() Creation Method will attempt to locate a record in the database matching the given attributes. However, if a model is not found, a new model instance will be returned. Note that the model returned by firstOrNew has not yet been persisted to the database. You will need to call save manually to persist it:

as stated here

Answer 9

Application Layer: Use Laravel's Validation property 'unique' to establish that only unique company name is allowed.

public function store(Request $request)
{
    $this->validate($request, [
        'company_name' => 'required|unique:companies|max:255',
    ]);

   // The company name is valid, store in database...
}

Database Layer: add a constraint as unique to the migration of the company's table for company_name column.

$table->string('company_name')->unique();

Answer 10

You should really use Laravel Validation and keyword unique to handle this:

$this->validate($request, [
        'company' => 'required|unique:company|max:255'
]);

Also, you could use custom Request class to handle form validation:

public function rules()
{
    return [
        'company' => 'required|unique|max:255'
    ];
}

If I were you, I'd use second one.

Answer 11

you can use unique key for companies table http://dev.mysql.com/doc/refman/5.7/en/constraint-primary-key.html

Answer 12

You can add a unique index on the table. So if your column is named company_name and table is companies you could execute the following:

alter table companies add unique (company_name)

Or alternatively you can do a query in programming before you allow an insert, which checks if the entry already exists. Or a combination of both..