Geek Answers Handbook

You must modify the MySQL query to prevent duplicate entries in the table.

First of all, I am very new to MySQL and I am trying to learn it when I go.

I have a script that runs on a page load that retrieves data of a syndicated type and adds it to a MySQL database table.

All I want to do is add new data found in the database table, without adding duplicates found from the previous page load.

My database table is as follows:

// Creates a Database Table only if the Table does not already exist
        mysql_query("CREATE TABLE IF NOT EXISTS $TableName(
          id INT NOT NULL AUTO_INCREMENT,
          PRIMARY KEY (id),
          Field_2    varchar(255) NOT NULL default '',
          Post_Date  int(11) NOT NULL default '0',
          Field_4    varchar(10) NOT NULL default '',
          Field_5    varchar(12) NOT NULL default '',
          Field_6    longtext NOT NULL default '',
          Field_7    longtext NOT NULL default '',
          Field_8    longtext NOT NULL default '') ") or die(mysql_error()
        );

There is one index as shown below:

Action     Keyname  Type  Unique Packed Column Cardinality Collation Null Comment
Edit Drop  PRIMARY  BTREE Yes    No     id     830         A

The Post_Date field is always unique (UNIX format), so it can be used to identify duplicates.

I am currently using the following code to enter data into a database table, and then remove duplicates:

// Enter the $sql Data into the MySQL Database Table
     mysql_query("INSERT INTO $TableName (id, Field_2, Post_Date, Field_4, Field_5, Field_6, Field_7, Field_8) VALUES ".implode(',', $sql));
// Removes Duplicates from the MySQL Database Table based on the 'Post_Date' field
     mysql_query("Alter IGNORE table $TableName add unique key (Post_Date)");
// Deletes the added index key created by the Removes Duplicates function
     mysql_query("ALTER TABLE $TableName DROP INDEX Post_Date");

, DROP INDEX , - .

, , , INSERT... ON DUPLICATE KEY UPDATE http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

, , .

-, , : http://www.tutorialspoint.com/mysql/mysql-handling-duplicates.htm

- :

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name) -> VALUES( 'Jay', 'Thomas');

, , .

IGNORE , :

mysql_query("INSERT IGNORE INTO $TableName (id, Field_2, Post_Date, Field_4, Field_5, Field_6, Field_7, Field_8) VALUES ".implode(',', $sql));

, .

+5
1

, ignore, ( ) , .

, , , .

INSERT IGNORE, INSERT. , MySQL . duplicate, IGNORE MySQL, .

Edit:

sqlfiddle:

http://sqlfiddle.com/#!2/4ad8a/3

Edit2:

CREATE TABLE IF NOT EXISTS TestTable(
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `Field_2` VARCHAR(255) NOT NULL DEFAULT '',
    `Post_Date` INT(11) NOT NULL DEFAULT '0',
    `Field_4` VARCHAR(10) NOT NULL DEFAULT '',
    `Field_5` VARCHAR(12) NOT NULL DEFAULT '',
    `Field_6` LONGTEXT NOT NULL,
    `Field_7` LONGTEXT NOT NULL,
    `Field_8` LONGTEXT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `Post_Date` (`Post_Date`)
)
+3

More articles:


All Articles