duplicated rows in DB

[roice]

Hello,

 

I build a forum which allow user to submit support tickets.

when they submit the system the the main details (title, create date, open by and etc) and insert them into table "tkts_topics". the rest of the data (ticket_id, ticket text, wrote by, attachment files) insert into another table - "tkts_replies".

For some reason I found duplicated tickets. its not happened all the time. I see 2 identical records in table "tkts_topics". in the second table I see that only 1 row has added, which means that I ticket have no content.

Of course I add some rules:

form can't be submited if there is no titket content.

form can't be submited if 10 seconds haven't been past siince the last submit.

 

Why its keep happened for time to time and how can I fix that?

 

 

Here is Insert code in case you want to check it by yourself:

 

			if ($flag == 1)
			{
				$query = "INSERT INTO `tkts_topics` (open_by, track_id, name, email, system, cat_id, client, title, status, urgency, create_date, last_update, hd_owner, prog_owner)
										VALUES('$open_by', '$track_id', '$name', '$email', '$system_id', '$cat_id', '$client', '$title', '$status', 'low', '$date', '$date', '$hd_owner', '$prog_owner' )";
				$res = mysql_query($query);

				$tkt_id = mysql_insert_id();

				$query3 = "INSERT INTO `tkts_replies` (tkt_id, open_by, name, text, date, visible)
										VALUES('$tkt_id', '$open_by', '$name', '$text', '$date', 'on' )";

				$res3 = mysql_query($query3);	

 

Here is the checking code:

 

	//// check for double submiting ////
$query = mysql_query("SELECT create_date FROM `tkts_topics` WHERE (client = '$client') AND (email = '$email') AND (title = '$title') AND ($date - create_date <= 20) LIMIT 0,1");
if (mysql_num_rows($query))
{
	echo "<div class='msg_error'>You may accidentally submited this form twice<br />Don't worry - the first form received and email with all the details was sent to you</div>";
	$flag = '0';	
}

if (!isset($_SESSION['hdUser_id'])) // if its regular user (not helpdesk/SV )
{
	if ( (empty($name)) OR  (empty($email)) OR (empty($cat_id)) OR (empty($client)) OR (empty($title)) OR (empty($text)) OR (empty($system_id))  )
	{
		echo "<div class='msg_error'>You did not complete all of the required fields</div>";
		$flag = '0';
	}
}
else /////////////// if it's HD/SV
{
	if ( (empty($cat_id)) OR (empty($client)) OR (empty($title)) OR (empty($text)) OR (empty($system_id))  )
	{
		echo "<div class='msg_error'>You did not complete all of the required fields</div>";
		$flag = '0';
	}
	else // if all good than:
	{
		if ( (empty($name)) AND  (empty($email)) )  ///// if it's HD/SV and files name+email = empties than get them from DB
		{
			$query = mysql_query("SELECT name, email FROM `users` WHERE (id = '$_SESSION[hdUser_id]') AND (valid = '1') ");
			$index = mysql_fetch_array($query);
			$name = $index['name'];
			$email = $index['email'];
		}
	}

}

 

[The Little Guy]

The best way to stop duplicates, is to add a unique index on the table.

 

then when you do an insert into that table:

 

insert ignore into ( ... ) values ( ... );

[roice]

Hi

 

I'm not familar with "insert ignore" function. what does "ignore" do?

[scootstah]

It's possible someone hit refresh on their browser and resent the form.

[Quentinb]

Is this it?

http://assets.webassist.com/gsg/dataassist_gsg.pdf

 

 

[The Little Guy]

Hi

 

I'm not familar with "insert ignore" function. what does "ignore" do?

 

What it will do is it will insert into the database, but if there is a duplicate it will "ignore" the insert. aka the insert won't happen. so, lets say their is a unique index on the "email" column.

 

msyql_query("insert ignore into users (first, last, email) values ('$first', '$last', '$email')");
$id = (int)mysql_insert_id();
if($id > 0){
// Value was inserted
}else{
// Value was not inserted, duplicate was found
}

[Psycho]

On an unrelated note, why are you storing some of the same data in the separate tables? The only different information I see being put into the "tkts_replies" table is the text and a visibility value. based upon your initial explanation it seems there is always supposed to be 1 and only 1 record in the "tkts_replies" table for each record in the "tkts_topics". So, it makes no sense to duplicate data and I don't even see the logic in having two tables.

 

Also, I see the value of the 'visibility' field is being set to the string 'on'. Assuming that there are only two possible values ('on' and 'off') you should not be using text for those fields. For logical true/false values you should be using a tinyint type field and using the values of 1 (true) and 0 (false). Then change the name to 'visible' so the true/false make more sense. Lastly, if the default value will always be true (i.e. 'on') then set that as the default value for that field in the database and you can remove that field from the insert query. Same goes for the data - you can set up a date field to auto-populate with the creation and/or modification date.

[roice]

The Little Guy - thanks, I'll try that.

But I still don't understan how comes that I got duplicated rows in my DB...

 

Psycho - Thanks, you are right. Usually I user 1 and 0 for true and false...

About my tables - every topic can one several replies, so I create row in the DB and put its ID inside every reply that conect to this row. like in here - wer have topic called "duplicated rows in DB" with lots of replies by me , you, The Little Guy and others...

[Psycho]

This is not the correct way to query records based upon a date range

AND ($date - create_date <= 20)

 

What does '20' represent? Minutes, days, seconds, microseconds? See the problem. So, that query to check for a duplicate is probably never finding duplicates - if that is even the problem. Look into the date_sub() and date_add() MySQL functions. Then you can do something such as this

AND (date_add(create_date, INTERVAL 20 MINUTE) > NOW())

 

OR

 

AND (date_sub(NOW(), INTERVAL 20 MINUTE) > create_date)

[roice]

$date  = mktime(date("H"), date("i"), date("s"), date("m")  , date("d"), date("Y"));

"create_date" and "date" created from linux time - the number of second that past since 1970.

so 20 is 20 second.

 

 

Will both of the 2 codes you wrote be good for checking how much time past since the last submit?

[Psycho]

$date  = mktime(date("H"), date("i"), date("s"), date("m")  , date("d"), date("Y"));

"create_date" and "date" created from linux time - the number of second that past since 1970.

so 20 is 20 second.

 

Well, I wasn't certain, so I tested it. You can do a comparison of database timestamps fields using +/- seconds, but you cannot do that for fields set as date types. I didn't check the datetime field type. You should test that the comparison is working as you think it should.