MYSQL inserting multiple rows unexpectedly

Question

I'm having an issue and was hoping that someone could help me out.

My issue is that whenever I run the code in the "MainFile", It outputs the two ID's that it has added the entries at, but then when I view my database I have six entries instead of two... Could anyone tell me why it might be doing this?

Note: I've only supplied snippets of the code from the classes because supplying the full classes would be way too much code. This is the only code be executed though.

CODE IN EDIT HISTORY

Edit: I've added a debug log to the bottom of this post verifying that I'm only executing the SQL code once for each query.

Edit: I'm no longer using the serialization method as it is clearly a bad idea for storing this type of data. However, even with my new code that is storing each individual value within the database I'm still getting three entries instead of one. So, it's the same issue. Anything?

Edit: After a few days of debugging I have narrowed it down to this line that is causing the issue

Throwlite::$systemSQL->executeSql("INSERT into ".SQL_COMMENTTHREADS_TABLE." (id, sort_order) values (DEFAULT, '2')");

You can view the LiteSQL class here for reference: http://pastebin.com/a4C6fF4u

Also, For reference, Here is the code being used to create the Table:

"CREATE TABLE IF NOT EXISTS `" . SQL_COMMENTTHREADS_TABLE . "` (`id` int unsigned NOT NULL AUTO_INCREMENT, `sort_order` int NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;"

And, Even though I'm pretty sure it shouldn't matter, Here is where SQL_COMMENTTHREADS_TABLE is bing defined.

define( 'SQL_COMMENTTHREADS_TABLE', "tl_comment_threads");

Answer 1

I'm not sure this answers your question, but you seem to be specifying the ID that you are inserting. I'm not sure whether you aren't using a PRIMARY KEY on your id column or why this works at all, but maybe you want this:

INSERT INTO " . SQL_COMMENTS_TABLE . " (id, thread) VALUES (DEFAULT, ?)

Answer 2

When updating UPDATE tl_comments SET thread=? WHERE id = ? and the second ? is not replaced with a valid existing id but instead remains empty, the database can't find an entry to update and inserts a new row instead. Thus creating another entry wich leads you to generating 2 rows instead of just one every time you query.

Which means the GetLastInsertID() function is not working properly. This can be caused by a few things but there are already many helpful hints to that question around at stackoverflow.

Answer 3

I could not locate any logical issue in the code snippet provided by you. However can you make following changes and post the output here :

1. Add a getter for "prepared_statements" member variable.
2. After your insert query finished running, print out output of above getter function.

The way this DB access class is written, it is evident there is no way two insert query can exists in single index of the "prepared_statements" array. Still it is better to check and confirm. Also if you can share the function (under which insert query piece of code is running) and the code snippet from where this function is called can be useful for others to debug the problem.

Answer 4

I feel somewhere in your code a loop is running which is causing inserting it multiple times in the database. Can you try echo your query so that u get to know how many times your query is running.

Another hack can be put condition to check whether row already present- do not insert.

Can you please post your code so that I can debug it.

Answer 5

By the way, you are missing a second } on line 51 of the pastebin you posted. The if(sizeof($binds) > 0){ was not closed.