0
+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+
| Destina |  Sender |    StartTime   |    EndTime     |        Created        |
+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+
| 0877222 |   2100  | 10132014010456 | 10132014010459 |  2014-10-13 10:35:46  |
+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+
| 0877222 |   2100  | 10132014010456 | 10132014010459 |  2014-10-13 10:35:46  |
+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+
| 0888333 |   4100  | 10132014010433 | 10132014010443 |  2014-10-13 10:35:46  |
+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+
| 0888333 |   4100  | 10132014010433 | 10132014010443 |  2014-10-13 10:35:46  |
+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+
| 0566666 |   3400  | 10132014010432 | 10132014010452 |  2014-10-13 10:35:46  |
+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+
| 0566666 |   3400  | 10132014010432 | 10132014010452 |  2014-10-13 10:35:46  |
+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+--+

I insert above table from data I retrieved from some API, to prevent duplication in "Destina", "Sender", "StartTime", "EndTime" columns I use MySQL INSERT IGNORE query

here is my code:

$data = array(
    'Destina'   =>  $json['Destina'],
    'Sender'    =>  $json['Sender'],
    'StartTime' =>  $json['StartTime'],
    'EndTime'   =>  $json['EndTime'],
    'created'   =>  date('Y-m-d H:i:s')
);

$redis_data = sprintf(
    'INSERT IGNORE INTO my_table (%s) VALUES ("%s")',
    implode(',',array_keys($data)),
    implode('","',array_values($data))
);

$result = mysql_query($redis_data);

But duplication still happen, how do I get this over, I have try to add primary key to above columns but I got this message:

#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

Thank you

junior
  • 808
  • 15
  • 25
  • 2
    Did you Google your error message? I found a whole slew of results. Here's one http://stackoverflow.com/a/8114994/ and another http://stackoverflow.com/a/17633468/ – Funk Forty Niner Oct 13 '14 at 03:59
  • 1
    This error is caused by `CREATE TABLE` not by `INSERT`. In other words: the code your provided is irrelevant to the error message. – zerkms Oct 13 '14 at 03:59
  • How do I explain this, sorry for my english, I add the primary keys to the columns because I failed to run the INSERT IGNORE query, my_table only have 'id' as a primary key, I want to prevent duplicate in my tables, can you give me a valid query without recreating the table? – junior Oct 13 '14 at 04:03
  • If you want to avoid duplicates, then set your column(s) as UNIQUE. – Funk Forty Niner Oct 13 '14 at 04:11
  • @Fred-ii- already sir, should I still use INSERT IGNORE query? – junior Oct 13 '14 at 04:15
  • You could try but I don't think it's necessary. I've done INSERTs before without it while having UNIQUE set on certain columns. – Funk Forty Niner Oct 13 '14 at 04:17

1 Answers1

1

Add a UNIQUE constraints instead of trying to add other primary key. The message you are getting means that you already have an auto increment column, therefore, if you want to add a primary key, then this auto_increment column should be the one.

Jehad Keriaki
  • 545
  • 5
  • 10