Page 1 of 2

MySQL Duplicate Rows

Posted: Tue Mar 14, 2006 9:38 am
by Jough
Perhaps I am missing something very simple...

I am using a simple MySQL table to track search queries made by my users.

Table: searchqueries
term varchar(200) Not Null
user varchar(50) Not Null
id timestamp(14) Null Primary Key

Basic PHP insert code:

Code: Select all

$searches = "INSERT INTO searchqueries (term, user) VALUES ('".$search."', '".$un."')";
mysql_query($searches) or die('Query failed: ' .mysql_error());
For some strange reason this idea that seams so simple has turned ugly. Every insert inserts TWO rows into the table. They are duplicate rows except for the 'user' field is blank on the second insert. With the timestamp I am able to see that the inserts are about two seconds apart. Would it be possible to use an auto inc for a PK instead of the timestamp, or would that simply throw a duplicate PK error? I am using sessions to track the user. Could this be effecting it? Please help!! :oops:

Posted: Tue Mar 14, 2006 10:20 am
by feyd
Your code is likely creating the situation, not MySQL. Post more of your code.

The code is excessive..

Posted: Tue Mar 14, 2006 11:11 am
by Jough
That was my initial thought. I normally use the variable $query to hold the MySQL query and I thought there may be another mysql_query statement somewhere in the rest of the code that was throwing the second insert; so, I changed the variable name to $searches which is used nowhere else in the entire website. The entire code for search.php is nearly 500 lines long as it is a site search. Here is the beginning:

Code: Select all

session_start();

$replace = array("'", "\"", ">", "<", "(", ")", ":", ";", "[", "]", "/", "\\", "=", "+", "@", "%", "$", "&", "*", "^", "-");
$search = str_replace($replace, "", $_GET['term']);

if ($search == "")
	$search = "Blogs";

include './Login/VerifySession.php';

include './loginbox.php';

$search = strtolower($search);
$keywords = preg_split("/[\s,]+/", $search);
$numwords = count($keywords);
							
$link = mysql_connect('*******', '*******', '*******')
	or die('Could not connect: ' . mysql_error());
mysql_select_db('*********') or die('Could not select database');
							
$searches = "INSERT INTO searchqueries (term, user) VALUES ('".$search."', '".$un."')";
mysql_query($searches) or die('Query failed: ' .mysql_error());

mysql_close($link);
The rest of the code performs the search and is fairly long...
In case you were wondering, $un is defined within /Login/VerifySession.php

Thanks for the reply!

Posted: Tue Mar 14, 2006 2:38 pm
by feyd
Is search.php being included in another file? Maybe it's being called multiple times?

Nope.

Posted: Tue Mar 14, 2006 3:27 pm
by Jough
No. search.php is its own seperate identity and displays its own HTML as well. It is called through a simple GET form. The form is also included within search.php itself as a 'refine your search' form. Could this be what is causing the problem?

Posted: Tue Mar 14, 2006 5:40 pm
by kkoston
Check your conditions for calling this part of the script. You said it includes the form. To me it looks like you called it twice : when you display the page and then when you submit it. Obvioulsy you shouldn't call it when you first display the page.

Rules?

Posted: Tue Mar 14, 2006 6:03 pm
by Jough
I'm sure I have not called it twice. I have been over that a countless number of times and it seems as though I have memorized my code function by function :D . That is why I was beginning to think it was a database issues and something to do with the timestamp or a session issue since one row has the username and the other has none. What are the rules on posting php code? How long is too long... :?

Posted: Tue Mar 14, 2006 6:09 pm
by feyd
when the buffer runs out.. 64K is the first-pass processed limit.

I'd suggest creating a log file for when this code is called. Include a timestamp (date('Y-m-d H:i:s')) and var_export(debug_backtrace(),true) .. it may tell you where and how the code is being called twice. If the log gets written to once, then you have something else that is doing the insertion bypassing the logging code.

Thanks!

Posted: Wed Mar 15, 2006 6:16 pm
by Jough
Thanks feyd!

I have never used the debug_backtrace function (and regrettably didn't know it existed). I will give that a try tomorrow and let you know how it turns out!

I just wish my server would upgrade to PHP5 so I could use debug_print_backtrace...

Bah.. forgettaboudit

Posted: Thu Mar 16, 2006 10:10 am
by Jough
I give up...

From what I can see from the display given by debug_backtrace the mysql_query is only being run once (on line 84 like it should be). And, just to be safe I created a simple 'if' statement to check for a row in the table with the same term within 5 seconds before inserting a new one. It still inserts duplicates...

I'm so stumped! I think it's about time I give up and create a function to delete duplicate rows! :lol:

Posted: Thu Mar 16, 2006 3:23 pm
by feyd
it may be something in MySQL that's doing it, but that's odd behaviour for MySQL..

If you echo $searches, what get's output?

Never hadda problem like this before.

Posted: Thu Mar 16, 2006 3:40 pm
by Jough

Code: Select all

INSERT INTO searchqueries (term, user) VALUES ('Jough', 'whutzit')
Just as it should... Like I said before; this is the ONLY spot on the entire website I have used the variable name $searches. So, I doubt there would be anything throwing the duplication later on in the code. I've even gone to the extent of double checking my HTML headers to make sure the page isn't redirecting to itself!

Problem resolved!

Posted: Thu Mar 16, 2006 4:36 pm
by Jough
I fixed it! (I think...)

My original code resembled the following:

Code: Select all

<?
some_php_code();
?>

<Some="HTML" />

<?
more_php_code();

mysql_query($searches);

echo '<form>Search form</form>';

?>

<More="HTML" />
I changed it to look something like this:

Code: Select all

<?
some_php_code();
?>

<Some="HTML" />

<?
more_php_code();

mysql_query($searches);

?>

<form>Search form</form>

<More="HTML" />
Supposedly I had some reason for including the Search form within an echo statement in the PHP code. I don't remember what that reason was now and it obviously was not that important as the entire process works flawlessly now.

Now here is the big question - Why would this create duplicate rows??? :?

Posted: Fri Mar 17, 2006 10:24 am
by timvw
As already suggested in the very beginning of this thread: Because somewhere you're already performing that sql statement...

No...

Posted: Fri Mar 17, 2006 10:43 am
by Jough
Clearly, timvw, if you would have read my previous post you would have come to the same conclusion as I did that it was not the fact that the sql statement had been performed twice. We have been over this many times already. Now unless you can explain your reasoning behind the harsh statement you just made I may pass it off as helpful instead of spiteful.

Maybe you should consider that just because I am a newbie to the board doesn't mean I am a newbie to PHP...