[RESOLVED] PHP keeps inserting twice too many rows in MYSQL database

[Mr. Baggins]

Hi, guys.

I'm working on a section of code that inserts values from an HTML form into a MYSQL database. However, each time I run the code, two rows of identical data are inserted into the database.

Can anybody help me resolve this problem?

Here is the code mentioned above. Even running this code by itself, without using the html form first, has the same problem.

<?php

//Define Variables

$username="dot_dot";
$password="q(@Q7SP4L((D";
$database="dot_thesiteguru";

$title=$_POST['title'];
$meta=$_POST['meta'];
$header_1=$_POST['header_1'];
$paragraph_1=$_POST['paragraph_1'];
$header_2=$_POST['header_2'];
$paragraph_2=$_POST['paragraph_2'];
$author=$_POST['author'];


//Connect To the Database Server

mysql_connect(localhost,$username,$password);


//Select the Database

@mysql_select_db($database) or die("Unable to select database");


//Command the Server to Insert Values

$query="INSERT INTO webpage VALUES ('','$title','$meta','$header_1','$paragraph_1','$header_2','$paragraph_2','$author')";

mysql_query($query);

$id = mysql_insert_id();


//Check if Insertation of Values Was Successful

if(mysql_query($query)) {
echo "You have successfully saved a webpage with author $author and title $title.  The ID for this webpage is $id.";
} else {
echo "There was a problem processing the information. Please contact us at support[@]thesiteguru.com.";
}


//Close the Connection

mysql_close();

?>

[criterion9]

The problem is you are running the query twice:

if(mysql_query($query)) {

Use something like:

$result = mysql_query($query);

...

if(!$result){

[tirna]

You are running the query in this line

mysql_query($query);

and again in this line

if(mysql_query($query))

Now, INSERT commands do not return a result set, so I normally check
how many affected rows there were (mysql_affected_rows()) - for example:

@mysql_query($query, $conn);
 
if(mysql_affected_rows($conn) == 1)  {
 
     //insert was successful
 
} else {
 
   //insert failed
  
} 

[Mindzai]

Now, INSERT commands do not return a result set, so I normally check
how many affected rows there were (mysql_affected_rows()) - for example:

@mysql_query($query, $conn);
 
if(mysql_affected_rows($conn) == 1)  {
 
     //insert was successful
 
} else {
 
   //insert failed
  
} 

Whether INSERT queries generate a resultset or not doesn't matter, because mysql_query does not return a resultset, it returns a resource identifier or false, and a resource identifier will always evaluate true. Criterion's code is perfectly valid (not that there's anything wrong with checking affected_rows either, but I just wanted to clarify as the reasoning for doing so seemed to be confused).

[Mr. Baggins]

Thanks guys.

I didn't expect to get three comprehensive solutions and explanations in 12 hours!

criterion9, I shall probably use your excellent solution. I see that instead of saying, "IF query is true," you assign the result of the query to a variable and then ask if THAT is true.

I didn't realize that a query would run if you put it inside an IF statement!

tirna and Mindzai, thanks for the information on INSERT and mysql_affected_rows(). That last function is a great tool to know.

I'm quite new to PHP, (as you've probably noticed by now) so you'll probably be seeing more of me!

Mr. Baggins

[Mr. Baggins]

(By the way, criterion9, can I give the $result variable its value at the beginning of the entire page of code, or do I need to wait to give it its value until after I run the query?)

[criterion9]

That really depends on your logical flow in the script...give it a shot and post back if you get stuck. I'm sure someone will be able to help you sort it out.

[Mr. Baggins]

I thought that I'd be able to fix it with the information I got. But when I tried it again after fixing it, it still generates two identical rows of data every time I run this code!

Any more ideas?

<?php

//Define Variables

$username="dot_dot";
$password="q(@Q7SP4L((D";
$database="dot_thesiteguru";

$title=$_POST['title'];
$meta=$_POST['meta'];
$header_1=$_POST['header_1'];
$paragraph_1=$_POST['paragraph_1'];
$header_2=$_POST['header_2'];
$paragraph_2=$_POST['paragraph_2'];
$author=$_POST['author'];


//Connect To the Database Server

mysql_connect(localhost,$username,$password);


//Select the Database

@mysql_select_db($database) or die("Unable to select database");


//Command the Server to Insert Values

$query="INSERT INTO webpage VALUES ('','$title','$meta','$header_1','$paragraph_1','$header_2','$paragraph_2','$author')";

mysql_query($query);

$id = mysql_insert_id();


//Check if Insertation of Values Was Successful

$result = mysql_query($query);

if($result) {
echo "You have successfully saved a webpage with author $author and title $title.  The ID for this webpage is $id.";
} else {
echo "There was a problem processing the information. Please contact us at support[@]thesiteguru.com.";
}


//Close the Connection

mysql_close();

?>

Mr. Baggins

[criterion9]

You are still executing the query twice...

mysql_query($query);

$id = mysql_insert_id();


//Check if Insertation of Values Was Successful

$result = mysql_query($query);

[Mr. Baggins]

Forgive me for my novice stance!

So assigning the function to a variable actually runs the function?

If that's the case, what do you think I should do?

[criterion9]

Yes assigning the result of a function runs the function to get the result.

$result = mysql_query($query);

$id = mysql_insert_id(); 


//Check if Insertation of Values Was Successful

[Mr. Baggins]

Wow, thanks a lot for that, criterion9 (and for your patience!).

IT WORKS!

Mr. Baggins

[criterion9]

Glad I could help. Coincidentally I would move the $id assignment to a block where you are sure the query didn't fail.

$result = mysql_query($sql);
if(!$result){
//result fail
} else {
$id = mysql_insert_id();
//other stuff here
}