CODEWITHSUNDEEP
phpmysqldatabase
opraz17
opraz17

Reputation: 85

Prevent inserting data to database if data is exist

How to prevent inserting data to database if data is exist in database

ex : I have data 

for ($i = 1; $i <= 10; $i++) {
    $text = "Orange";
    mysql_query("INSERT INTO fruit (text) VALUES ('$text')");
}

I want the text "orange" is inserted once to the "fruit" table
and if there is text "Orange" again its not inserted to the "fruit" table
because the data is exist and have been inserted before

Upvotes: 0

Views: 1597

Answers (6)

leonardo_assumpcao
leonardo_assumpcao

Reputation: 686

Most things in SQL are just like MySQL Unique Indexes, we learn just once.
Solving your example, we shall run something near

CREATE  TABLE IF NOT EXISTS `stackoverflow2`.`fruit` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `text` VARCHAR(50) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `text_UNIQUE` (`text` ASC) )
ENGINE = InnoDB;

From MySQL doc,

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. [...]

Upvotes: 0

502_Geek
502_Geek

Reputation: 2126

for ($i = 1; $i <= 10; $i++) {
$text = "Orange";
$result = mysql_query("SELECT text FROM fruit WHERE text = '$text');
    if (mysql_num_rows($result) == 0){
        mysql_query("INSERT INTO fruit (text) VALUES ('$text')");
    }else{
       echo "Duplicate";
       break;
     }
}

Upvotes: 0

mirkobrankovic
mirkobrankovic

Reputation: 2347

Another way is to use:

REPLACE INTO fruit (text) VALUES ('$text')

will insert new ones and for old ones, delete and insert again :)
But adding unique index on text field is the best way :D

Upvotes: 1

Andrew
Andrew

Reputation: 7768

Couple ways:

  • You would have to perform a select First and compare the results and then do the insert if no records found
  • Add Unique to your datbase fields and insers won't be performed if there is a data
  • Construct your query to check before insert
  • Use Insert Ignore

Upvotes: 0

Fracsi
Fracsi

Reputation: 2314

Use INSERT IGNORE with unique key on text.

INSERT IGNORE INTO fruit (text) VALUES ('$text')

Upvotes: 1

asifsid88
asifsid88

Reputation: 4701

Make the column as Unique key...
This will prevent the duplicates

References:
Primary Key and Unique Key constraints

Upvotes: 3

Related Questions