0

I am trying to check the database tables for data before entering new data and avoiding the dublicates.

  $sqlQuery = "INSERT INTO " . $table . " ( " . $columns . ") VALUES( '" . $columnData . "') 
                        SELECT " . $columnData .  " FROM " . $columns . " 
                        WHERE NOT EXISTS (SELECT '" .$columnData . "' 
                        FROM " . $table . " WHERE '" . $columnData . "' = '" . $columnData . "')";

The query does not throw any errors, although the query is not executed as expected.

Thanks in advance

tony
  • 1,147
  • 2
  • 7
  • 10
  • To avoid duplicates use an unique index. – Jens Apr 16 '15 at 12:41
  • possible duplicate of [Use multiple columns as unique identifier for mysql](http://stackoverflow.com/questions/3798555/use-multiple-columns-as-unique-identifier-for-mysql) – IROEGBU Apr 16 '15 at 13:19

3 Answers3

1

to avoid duplicate entrys just use INSERT IGNORE

If you want to update when it's a duplicate use Insert ... ON DUPLICATE KEY UPDATE...

DocRattie
  • 1,392
  • 2
  • 13
  • 27
  • I understand database development skill, although for this example, database is not in considered, therefore there is not primary key, it is a programming example, which wont be going live. Is there anyway i can check INSERT IGNORE on a text tuple please? @DocRattie ? Thanks – tony Apr 16 '15 at 12:45
  • You should add that to your question. I would make a `SELECT` to check if the Data is in the DB allready. Afaik there is no `INSERT .... WHERE` – DocRattie Apr 16 '15 at 12:52
  • 1
    @tony - you were told by several people how it's done. It's done using the database. You use the database. Why would it matter if it goes live or not? There is no need that you do any part of code during your life as a programmer using wrong principles. Either do it right or just don't do it - smart and talented people developed systems for us to use them to shorten our coding time. Also, "not working as expected" is **NOT** the way to describe what went wrong - no one knows whether you have an error in your query or if it executes but for some reason it doesn't insert. – N.B. Apr 16 '15 at 13:19
  • is there any way i can add it into here please? for a auto increament uid $columns = "`".str_replace(" ", "` UID int NOT NULL AUTO_INCREMENT PRIMARY KEY, `", "` TEXT(60), `", $answer)."` TEXT(60)"; @N.B – tony Apr 16 '15 at 14:40
1

I am giving you example with all conditions please check it

First step would be to set a unique key on the table:

ALTER TABLE thetable ADD UNIQUE INDEX(pageid, name);

Then you have to decide what you want to do when there's a duplicate. Should you:

ignore it?

INSERT IGNORE INTO thetable (pageid, name) VALUES (1, "foo"), (1, "foo");
Overwrite the previously entered record?

INSERT INTO thetable (pageid, name, somefield)
VALUES (1, "foo", "first")
ON DUPLICATE KEY UPDATE (somefield = 'first')


INSERT INTO thetable (pageid, name, somefield)
VALUES (1, "foo", "second")
ON DUPLICATE KEY UPDATE (somefield = 'second')
Update some counter?

INSERT INTO thetable (pageid, name)
VALUES (1, "foo"), (1, "foo")
ON DUPLICATE KEY UPDATE (pagecount = pagecount + 1)
Saty
  • 22,443
  • 7
  • 33
  • 51
Anil Singh
  • 100
  • 1
  • 9
1

If you want to avoid duplicates, then create a unique constraint or index on the columns you want to be unique:

create unique index idx_table_cols on table(col1, col2, . . .);

Then the database will prevent duplicates. If you want the insert to fail silently instead of generating an error, you can use insert ignore, but I would recommend insert on duplicate key update:

insert into table(col1, col2, . . .)
    select <values>
    from . . .
    on duplicate key update col1 = values(col1);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • So would i add that "create unique idx_table_cols" on this line of code please:- $sqlCreateTable = "CREATE TABLE " . $message . "( " . $columns . " )"; – tony Apr 16 '15 at 12:53
  • @tony . . . Or you would add a unique constraint in the `create table` statement. – Gordon Linoff Apr 16 '15 at 23:12