Too many records?

richardo

I have a very simple piece of PHP/MySQL -

$Connect=mysql_connect($dbserver,$dbuser,$dbpassword);
$res=mysql_select_db($dbdb,$Connect);

$Query=mysql_query("INSERT INTO courses1 SET title='NEWrecord', visible='y'");

My problem is that it insists on inserting two records into table 'courses1' in the database when it's run.

I'm stumped.

Has anyone come across this before??

jhegarty

I think you are mixing up insert and update.


Here is what you need.


INSERT INTO courses1 (title, visible) VALUES ('NEWrecord', 'y')

richardo

Thanks jhegarty, but isn't that just an alternative syntax? I just tried it anyway and it gives the same results - 2 identical records written!

jhegarty

Can't think of any reason for that. Do you want to put up the rest of the code.

richardo

That is what is completely baffling me.

I have a large page of code for processing forms, and it is producing these duplicate records. I spent ages debugging and commenting out any suspect lines.

In the end, I just copied out the following lines to a test file

<?
include("dbinc.php");

$Connect=mysql_connect($dbserver,$dbuser,$dbpassword);
$res=mysql_select_db($dbdb,$Connect);
$Query=mysql_query("INSERT INTO courses1 (title, visible) VALUES ('NEWrecord', 'y')");
?>

(dbinc.php just sets variables and can be ignored.)

The above is the code in its entirety.

jhegarty

you have me anyway.... makes no sense...

louie

That could be few reasons why..

1. The code runs twice due to some page redirection
2. the code exist twice on the page
3. the code could be inside a loop (foreach or while)

MOH

louie wrote: »

That could be few reasons why..

1. The code runs twice due to some page redirection
2. the code exist twice on the page
3. the code could be inside a loop (foreach or while)

Just a thought - can you write some kind of timestamp value into one of the fields, might indicate whether they're both being written at the same time, or one of the reasons suggested is causing the code to be executed twice.

There isn't a trigger on the DB that's causing extra stuff to be written?

richardo

The code above is precisely what is run. There are no loops or page redirects and the SQL appears only once.

I just tried the timestamp idea, but as I suspected, I got two identical values.

The database structure is identical to a database used on an existing site [that I am modifying] and there were no problems with the old site.

I'm tearing my hair out at this stage.......

galwayguy22

Try running the exact same query using PHPmyAdmin, see what happens. If it adds two records then there must be something up with the database structure.

You could export all the data, table sturctures etc, delete the database and import back in all the data.

Might magically fix the problem.

turgon

richardo wrote: »

Thanks jhegarty, but isn't that just an alternative syntax? I just tried it anyway and it gives the same results - 2 identical records written!

I think I know whats happening here, I remember a few days where I was pissy over this issue.

Basically whats happening is your browser is requesting the page twice for some reason, once for you and once in the background. So each time it runs the code your entry is inserted, twice altogether

The thing is for the background request it wont send post data. So what you should do is set up a POST form to direct to this page, rather than sending the data through GET method.

The solution I do that works well is this, in your form page insert a input like so (remember to use the POST method):
[HTML]
<form action="YourScript.php" method="post">
<input type="hidden" name="postman" value="yes">
<!--all your other inputs-->
</form>
[/HTML]
Then in your script do the following (ive used PHP):

if($_POST["postman"]=="yes"){

//in this case post data has been sent, so you can run your query and it will be only inserted once

$Connect=mysql_connect($dbserver,$dbuser,$dbpassword);
$res=mysql_select_db($dbdb,$Connect);
$Query=mysql_query("INSERT INTO courses1 SET title='NEWrecord', visible='y'");

}
else{

//this is if post data hasnt been sent, ie the broser is requesting another page in the background
die();
}

If this isnt clear, or isnt to your suiting, tell me what you want and you can work around it.

louie

modify the code as below:

<?
include("dbinc.php");

$Connect=mysql_connect($dbserver,$dbuser,$dbpassword);
$res=mysql_select_db($dbdb,$Connect);
$sSql = "INSERT INTO courses1 (title, visible) VALUES ('NEWrecord', 'y')";
echo $sSql;//comment this when finished
//$Query=mysql_query($sSql); // un-comment this when you want the DB to be updated
?>

and see how many times is the sql written on the page.
If only once, than I'll say is something to do with the DB setup

richardo

I had already tried the phpMyAdmin trick. That inserts only one record.

I just created a basic table and tried writing to that. Two records!!

This eliminates the database as the source of the problem.

Turgon's reply looks very promising! I'll try that and see what comes out of the boil.....

richardo

OK. Latest results.

The file I am running at th moment doesn't receive its contents from a form, as I am declaring the values implicitly.

To test the browser doing a double request, I ran the script using IE instead of Firefox.

Very interesting results!!!!
Internet Explorer produces one record
Firefox produces two.

So it's a browser problem [thanks a million Turgon - you have saved my sanity].

Now all I have to do is work out a way of stopping Firefox doing that trick.

turgon

richardo wrote: »

So it's a browser problem [thanks a million Turgon - you have saved my sanity].

Now all I have to do is work out a way of stopping Firefox doing that trick.

No problem!!!

Now you could do this two ways. First off (the least preferable way) is instead of making a normal link to your file, you could create a form with only the one input we talked about which will go to your file - the link would be in the form of the submit button. This isnt a great solution in my opinion.

The better way is this. Before you insert your entry in your script retrieve the last entry you inserted. The best way to to this is the following:

$sql = "SELECT title FROM courses1 ORDER BY course_id DESC LIMIT 0,1";

In this case the field “course_id” is the id of your course that is set to auto_increment (you can set any field to auto_increment in PHP MyAdmin). Because it increments every time, the last entry added will have the highest id number. Thats wh7y we have order by course_id descending, so the highest is first. Then we set limit as all we want is the highest entry,

Then you can do a comparison on this entry to the one your adding to make sure its not a duplicate entry. So it might look like:

$Connect=mysql_connect($dbserver,$dbuser,$dbpassword);
$res=mysql_select_db($dbdb,$Connect);
$Search = mysql_query("SELECT title FROM courses1 ORDER BY course_id DESC LIMIT 0,1");

if(mysql_result($Search,0,"title")==NEWrecord){
//its a duplicate entry, so we dont want to insert it.
}
else{
//your entry doesnt appear so insert away
$Query=mysql_query("INSERT INTO courses1 SET title='NEWrecord', visible='y'");
}

The drawback is if you want to add two Courses with the same title one after another you couldnt, unless you did more comparisons with other fields in your database table.

The if you really wanted to make it rock hard, you could add a new field to your table "time_added" say, which contains the timestamp of the time whenthat entry was added. Then you fetch the last added entry again, except this time you check how long since the last added entry was inserted. If it was inserted over 10 seconds ago you can be pretty sure everythings ok. Like so:

$Connect=mysql_connect($dbserver,$dbuser,$dbpassword);
$res=mysql_select_db($dbdb,$Connect);
$Search = mysql_query("SELECT title FROM courses1 ORDER BY time_added DESC LIMIT 0,1");
//this time ive ordered by time_added, as the newest entry will have the biggest timestamp.
if((time()-mysql_result($Search,0,"time_added"))<10){
//its a duplicate entry, so we dont want to insert it.
}
else{
//insert away!
$Query=mysql_query("INSERT INTO courses1 SET title='NEWrecord', visible='y'");
}

The way with the timestamp is probably the best I think.

richardo

The way I have finally done it -

I created a field - timestamp and a variable $inserttime=date()
The record is then inserted.
INSERT INTO course SET timestamp=$inserttime
read the table, selecting on timestamp and retrieve courseID [which is auto incremented]
I then delete ALL records where timestamp=$inserttime and courseID != retrieved value.

It's quick. It's dirty. It works.

turgon

richardo wrote: »

It's quick. It's dirty. It works.

Unless the two requests happen to take place in different seconds.

richardo

turgon wrote: »

Unless the two requests happen to take place in different seconds.

Ha! I thought of that. I set the value of 'timestamp' only once, and use it as a unique identifier. Once it is set, any amount of time can elapse, but it won't change value.