I'm getting duplicata entries in mysql database

countrydj · 04-13-2011, 07:51 PM

I'm getting duplicate entries in database.
Hi Guys...

I'm getting duplicate entries in mysql database when submitting a new item from a form.
I initially loaded the database via IMPORT function in phpMyAdmin.

This is my input form:

<center>
<br />
<FONT SIZE="5" ><b>Add Item Template</b></FONT>
<br />

<form action="add_items.php" method="GET">
<table width="100%" border="0" align="RIGHT" cellspacing="2" cellpadding="2">
<tr>
<td align="RIGHT" valign="top"><font size=2><b> Group: </td>
<td align="LEFT">
<select name="grtype">
<option value="PLEASE SELECT">PLEASE SELECT
<option value="Alloy Silencers">Alloy Silencers
<option value="Carbon Silencers">Carbon Silencers
<option value="Titanium">Titanium
<option value="Complete Systems">Complete Systems
<option value="Pipe Kits">Pipe Kits
<option value="Down Pipes">Down Pipes
<option value="Meggaphones (Un-Silenced)">Meggaphones (Un-Silenced)
<option value="Meggaphones (Silenced)">Meggaphones (Silenced)
<option value="Early Stock / Bygone Days">Early Stock / Bygone Days

</select>
</td>
</tr>

<tr>
<td align="RIGHT"><font size=2><b>Description: </b></font></td>
<td align="LEFT"><input type="text" size=35 name="description"></td>

</tr>

<tr>
<td align="RIGHT"><font size=2><b>Comment: </b></font></td>
<td align="LEFT"><input type="text" size=35 name="comment"></td>

</tr>

<tr>
<td align="RIGHT"><font size=2><b>Quantity: </b></font></td>
<td align="LEFT"><input type="text" size=35 name="quantity"></td>
</tr>

<tr>
<td align="RIGHT"><font size=2><b>Price: </td>
<td align="LEFT"><input type="text" size=35 name="price"> </td>
</tr>

<tr>
<td align="RIGHT"><font size=2><b>Order: </font></td>
<td align="LEFT"><input type="text" size=35 name="catorder"></td>
</tr>

<tr>
<td align="RIGHT"></b></font></td>
<td align="CENTER"><input type="button" value="Send" onclick="post()">
<input type="reset" value="Clear"></td>



<input type="hidden" name="act" value="add"></input>
<input type="hidden" name="type2" value="all"></input>
</form>
</td>
</tr>
</table>

</center>

This is my processing script:

<?
require("global.php");

$template = join('',file('add_items.tpl'));
$content = join('',file('add_items.txt'));

$grtype = $_REQUEST['grtype'];
$description = $_REQUEST['description'];
$comment = $_REQUEST['comment'];
$quantity = $_REQUEST['quantity'];
$price = $_REQUEST['price'];
$type2 = $_REQUEST['type2'];
$status = $_REQUEST['status'];

if($action=="add"){

$qry = "INSERT INTO " . $vars["table directory"];
$qry .= "(id,grtype,description,comment,quantity,price,type,type2,catorder,status)";
$qry .= "VALUES ('','$grtype','$description','$comment','$quantity','$price','$type','$type2','$catorder','1')";

if(!($results = mysql_query($qry, $link))){
displayErrMsg(sprintf("Error in executing %s query", $qry));
displayErrMsg(sprintf("error:%d %s", mysql_errno($link), mysql_error($link)));
exit();
}

# SEND RESPONSE

$template = str_replace("[main content]",$content,$template);

print $template;

}

?>

I think that it might be something to do with the RESPONSE section.
If I delete that and replace it with
print "Thank you";
I only get one entry in the database.

However, I do need to return the input form in order to keep entering new items into the database.

I would really appreciate any help and advise possible.

Sorry for such a long post.
I wanted to give as much information as possible.

Thanks,

John C

Ramurd · 04-14-2011, 01:58 AM

A good DBMS with a good database design will prevent duplicate entries; (this was exactly the reason why I have tried MySQL for about 5 minutes, which it took me to figure out it did not do such checks back then (+/- 10 years ago) on primary keys)

Anyway, it should nowadays from what I've heard, and in that case:

- what is your Primary Key on that table?
- what are other constraints on said table?

this will make your DBMS prevent duplicates; Which is a good idea altogether.

In your front-end, you can prevent this occurrence (and possibly technical error message) with changing the query like this:

Code:

insert into <table>(field1,field2,field3) values (value1,value2,value3) where not exists (select field1 from <table> where field1=value1);

(given that field1 is your primary key)

It doesn't hurt to add a UNIQUE NOT NULL to your primary key; In that long past ago, MySQL had issues with that one too. It may depend on your MySQL engine whether or not this actually works, or that you have to do all the prevention in your front-end (by making all your insert queries like above)

countrydj · 04-14-2011, 06:47 AM

Hi Ramurd...

Thanks for taking the time and trouble to help me.

Quote:

insert into <table>(field1,field2,field3) values (value1,value2,value3) where not exists (select field1 from <table> where field1=value1);

Im not sure what you mean here ???

More information:
I'm using PHP5.2 and MYSQL5.1 under CENTOS 5.5 x86_64

My table is set so that field1 id is PRIMARY, KEY and AUTO INCREMENT.
Therefore I can't state "...where field1=value1);
Or am I missing something ???

Regards

John C

michaelk · 04-14-2011, 07:52 AM

Difficult after a quick glance (at least for me) at code to tell what is going on since we do not know what the contents of global.php as well as the add_items.tpl and add_items.txt files. Also since there isn't a statement in your first post that $action as actually be assigned anything so it isn't obvious (again at least for me) that your code is even working to insert data into the database. Also $catorder is not being assigned a value either.

Ramurd · 04-14-2011, 08:10 AM

Quote:

Originally Posted by michaelk

Difficult after a quick glance (at least for me) at code to tell what is going on since we do not know what the contents of global.php as well as the add_items.tpl and add_items.txt files. Also since there isn't a statement in your first post that $action as actually be assigned anything so it isn't obvious (again at least for me) that your code is even working to insert data into the database. Also $catorder is not being assigned a value either.

That's why I gave a pretty generic answer; The situation is vague at best;

Quote:

Quote:
insert into <table>(field1,field2,field3) values (value1,value2,value3) where not exists (select field1 from <table> where field1=value1);
Im not sure what you mean here ???

More information:
I'm using PHP5.2 and MYSQL5.1 under CENTOS 5.5 x86_64

My table is set so that field1 id is PRIMARY, KEY and AUTO INCREMENT.
Therefore I can't state "...where field1=value1);
Or am I missing something ???

Even when the table has an auto increment, you can state it, but it is not logical.
The "insert into ..." line you quoted is a SQL query, if it's not saying anything to you, I'm not sure we can help you much further.

Anyway, what you do in the case of an auto increment is modify the query slightly:

Code:

INSERT INTO table(field2,field3) VALUES (value2,value3) WHERE NOT EXISTS (SELECT field1 FROM table WHERE field2=value2 AND field3=value3)

What you get from such a query is that the data is only inserted when there is no record that contains value2 for field2 and value3 for field3; Hence: no duplicate; I'm pretty sure that if you know SQL a bit you can extend your queries in such a way that they will prevent duplicate data that may not be duplicated.

countrydj · 04-14-2011, 11:38 AM

Hi Guys...
Thanks again for trying to help me.
I'm sorry that you don't think that I have given you sufficient information.
I thought that I had.
You can have whatever information that you need, except of course the login and password to the database.
The global.php only contains the connection to the database, along with login information.
It also contains some variables that don't affect the running of the script.
This is the content of global.php:

Code:

// database variables
$hostName="localhost";
$databaseName="removed";
$userName="removed";
$password="removed";
$directory="exhausts";
$date=date(' dS F Y');
$date_time=date('l dS \of F Y h:i:s A');
#$date=date('l dS \of F Y h:i:s A');
$client = $_SERVER[HTTP_USER_AGENT];

// Prints something like: Monday 15th of August 2005 03:12:46 PM
// echo date('l dS \of F Y h:i:s A');


// other variables
$vars = array();
$vars["domain url"] = "http://gvlmicro.co.uk/";
$vars["table directory"] = "exhausts";
//$vars["overall width"] = "776";
$vars["admin ip"] = "removed";
$vars["admin link"] = "<a href='/admin/allprices.php'>Admin</a>";
$vars["add link"] = "<a href='/admin/add_items.php'>Add</a>";
$vars["lh image"] = "<img src='images/caution.jpg' width='102' height='177' border='0' alt='Beware Motor Cycles'>";
$vars["admin lh image"] = "<img src='/images/caution.jpg' width='102' height='177' border='0' alt='Beware Motor Cycles'>";
$vars["date"] = "date('F Y')";

#=================================================================================================

//connect to the database
if(!($link=mysql_pconnect($hostName, $userName, $password))){
	displayErrMsg(sprintf("Error connecting to the host %s, by user %s", $hostName, $userName));
	exit();
}

//select the database
if(!mysql_select_db($databaseName, $link)){
	displayErrMsg(sprintf("Error in selecting the %s database", $databaseName));
	displayErrMsg(sprintf("error: %d %s", mysql_errno($link), mysql_error($link)));
	exit();
}


###############################################################################################

I don't use all these variables. I have used an existing global.php from another project and modified it to suit.

Connecting to the database and entering data is not the problem.
The problem is that when inserting from the form (listed in first post):

Quote:

If I select ONLY the group (which is the only one that is trapped) it INSERTS into the database OK
If I select the GROUP and then enter a description, it INSERTS into the database TWICE
If I select the GROUP and then enter a description and another field, it INSERTS into the database TWICE
If I select the GROUP and then enter a description and TWO other fields, it INSERTS into the database TWICE
If I select the GROUP and then enter a description and THREE or FOUR other fields, it INSERTS into the database THREE TIMES

Referring to the php script. The script has entered the data into the databse, it sends a response and returns the form to enable entering another item.
If I remove ALL the script AFTER the #RESPONSE and replace with print "Your information has been added to the database"; everything works OK. Only ONE entry in the database, every time.

I'm hoping that this further information will help you to help me.

Thanks again fo ryour help.

John C

Ramurd · 04-14-2011, 03:40 PM

So, going by what you tell here, taking a shortcut:

PHP Code:



$template = str_replace("[main content]",$content,$template);

print $template;

will insert data into the database twice; At the top of the code is this:

PHP Code:



$template = join('',file('add_items.tpl'));
$content = join('',file('add_items.txt'));

Diving to php.net:

Quote:

mixed str_replace ( mixed $search , mixed $replace , mixed $subject [, int &$count ] )

could it be that the html-code you posted is from your add_items.tpl?

Code:

<!--################## END STANDARD TEMPLATE ONE ########################-->

<input type="hidden" name="act" value="add"></input>
<input type="hidden" name="type2" value="all"></input>
</form>
</td>
</tr>
</table>

</center>

But then there's something weird:

PHP Code:



if($action=="add"){

I don't see $action being set anywhere...

countrydj · 04-14-2011, 04:07 PM

Hi Ramurd...
Thanks for getting back to me.
This is the explanation:

PHP Code:



$template = join('',file('add_items.tpl')); 
$content = join('',file('add_items.txt'));

This code picks up the two files required to create the web page form.
add_items.tpl is the web page.
add_items.txt is the form that is inserted into the web page by

PHP Code:



$template = str_replace("[main content]",$content,$template); 
 
print $template;

The web page can be seen at http://www.gvlmicro.co.uk/admin/add_items.php

PHP Code:



<input type="hidden" name="act" value="add"></input>

This is passed to the script and converted to $action by $action = $_REQUEST['act'];
$action is then picked up by:

PHP Code:



else if($action=="add"){

The rest of the form fields are then inserted into the database:

PHP Code:



$qry = "INSERT INTO " . $vars["table directory"]; 
$qry .= "(id,grtype,description,comment,quantity,price,type,type2,catorder,status)"; 
$qry .= "VALUES ('','$grtype','$description','$comment','$quantity','$price','$type','$type2','$catorder','1')";

Then the script continues to:

PHP Code:



# SEND RESPONSE 
 
$template = str_replace("[main content]",$content,$template); 
 
print $template;

This is where the script sems to fall over.
If I replace the above script with:

PHP Code:



print "Your information has been added to the database";

Only one set of data is inserted into the database.
Which is what I want.

I hope this makes it a little clearer.

Regatds,

John C