Why is my MySQL INSERT query inserting 3 identical rows?

Question

I am trying to track what users are searching for on my site (from a simple search form on the front page) with PHP and MySQL.

At the end of all my queries I am using this query:

INSERT INTO `DiggerActivity_Searches` (
`SearchTerms`,
`SearchType`,
`NumResults`,
`Location`,
`Date`,
`Time`
) VALUES (
'SearchKeywords',
'SearchTypes',
'NumberOfResults',
'User'sLocation',
'CurDate',
'CurTime'
)

Now, whenever there is a new search keyword, it inserts 3 identical rows. However, if I refresh the page it only inserts 1 row, as it should.

The values are passed as a GET like this (I have mod rewritten the URL stuff):

http://www.mysite.com/Search-Category-Search_these_words

Answer 1

Your table is missing a primary key. Id suggest a PK like search ID or something similar

Read more about this at Unique key - Wikipedia

Answer 2

You might want to check first whether your script executes the query three times or the script is invoked three times (e.g. by some browser addons).
If you do not have a debugger installed you can use something like

function trace_log() {
    static $magic = null;
    if (is_null($magic)) {
        $magic = uniqid();
    }

    $s = $magic . ' '. microtime(true) . ":\r\n";
    foreach( debug_backtrace() as $d) {
        $s .= '  '. $d['file'].'@'.$d['line']."\r\n";
    }

    file_put_contents('trace_log.txt', $s, FILE_APPEND);
}

...
trace_log();
mysql_query(....) // or stmt->execute() or whatever you use to execute the query.

If the first value of each log entry (the "magic" id) in trace_log.txt changes, your script is invoked multiple times. If it's the same for all three calls to trace_log(), your script executes the query three times.

Answer 3

Just know you are not alone in dealing with this strange bug.

This same problem showed up on my website in the past few days as well. The only thing I recently added was a third party banner ad.

SOLUTION: I commented out the banner ad script (Hint: from search engine that starts with a G) and everything was good again.

Answer 4

I was just going to make a comment put need more reputation to so...

Anyway, similar happened to me. Turns out I was echoing out debug information above the header causing the browser to reload automatically. Easiest way to check is just use

<script type="text/javascript">alert("loading");</alert>

in the header and see how many times you see it.