MySQL Duplicate Rows

Looking for volunteers to join your project? Need help with a script but can't afford to pay? Want to offer your services as a volunteer to build up your portfolio? This is the place for you...

Moderator: General Moderators

User avatar
Jough
Forum Newbie
Posts: 17
Joined: Tue Mar 14, 2006 9:21 am
Location: Montana

MySQL Duplicate Rows

Post 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:
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Your code is likely creating the situation, not MySQL. Post more of your code.
User avatar
Jough
Forum Newbie
Posts: 17
Joined: Tue Mar 14, 2006 9:21 am
Location: Montana

The code is excessive..

Post 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!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Is search.php being included in another file? Maybe it's being called multiple times?
User avatar
Jough
Forum Newbie
Posts: 17
Joined: Tue Mar 14, 2006 9:21 am
Location: Montana

Nope.

Post 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?
kkoston
Forum Newbie
Posts: 1
Joined: Tue Mar 14, 2006 4:43 pm

Post 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.
User avatar
Jough
Forum Newbie
Posts: 17
Joined: Tue Mar 14, 2006 9:21 am
Location: Montana

Rules?

Post 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... :?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
Jough
Forum Newbie
Posts: 17
Joined: Tue Mar 14, 2006 9:21 am
Location: Montana

Thanks!

Post 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...
User avatar
Jough
Forum Newbie
Posts: 17
Joined: Tue Mar 14, 2006 9:21 am
Location: Montana

Bah.. forgettaboudit

Post 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:
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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?
User avatar
Jough
Forum Newbie
Posts: 17
Joined: Tue Mar 14, 2006 9:21 am
Location: Montana

Never hadda problem like this before.

Post 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!
User avatar
Jough
Forum Newbie
Posts: 17
Joined: Tue Mar 14, 2006 9:21 am
Location: Montana

Problem resolved!

Post 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??? :?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

As already suggested in the very beginning of this thread: Because somewhere you're already performing that sql statement...
User avatar
Jough
Forum Newbie
Posts: 17
Joined: Tue Mar 14, 2006 9:21 am
Location: Montana

No...

Post 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...
Post Reply