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

This site is best viewed in a modern browser with JavaScript enabled.

[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

[FONT=Courier New]mysql_queryCOLOR=#007700;[/COLOR][/FONT]
[FONT=Courier New][/FONT]
[FONT=Courier New]and again in this line[/FONT]
[FONT=Courier New][/FONT]
[FONT=Courier New]if(mysql_queryCOLOR=#007700)[/COLOR][/FONT]
[FONT=Courier New][/FONT]
[FONT=Courier New]Now, INSERT commands do not return a result set, so I normally check
how many affected rows there were (mysql_affected_rows()) - for example:[/FONT]
[FONT=Courier New][/FONT]

 
@mysql_query($query, $conn);

if(mysql_affected_rows($conn) == 1)  {

 //insert was successful

} else {

   //insert failed

}

[FONT=Courier New][/FONT]

Mindzai

tirna;1104229 wrote:
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!:o

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
}

coxixx

<?php
header("Content-Type: text/plain");
require_once("../private/connection.php");

if($_POST["name"] && $_POST["lastname"] && $_POST["birthday"] && $_POST["email"] && $_POST["password"] && $_POST["confirmPassword"] ){

	$name     =$_POST["name"];
	$lastname =$_POST["lastname"];
	$birthday =$_POST["birthday"];
	$email    =$_POST["email"];
	$password =$_POST["password"];
	$hashed_password = password_hash($password, PASSWORD_BCRYPT);	
	$sql = "INSERT INTO students (student_name,student_family,student_birthday,student_email,student_password)

VALUES ('$name','$lastname' ,'$birthday' ,'$email','$hashed_password')";
$result=$conn->query($sql);

	if ($result) {
echo "yes";

}
else {
echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();

}else

{ $conn->close(); echo "no";}

?>

coxixx


<?php
header("Content-Type: text/plain"); 
require_once("../private/connection.php");





if($_POST["name"] && $_POST["lastname"] && $_POST["birthday"] && $_POST["email"] && $_POST["password"] && $_POST["confirmPassword"] ){

	$name     =$_POST["name"];
	$lastname =$_POST["lastname"];
	$birthday =$_POST["birthday"];
	$email    =$_POST["email"];
	$password =$_POST["password"];
	$hashed_password = password_hash($password, PASSWORD_BCRYPT);	
	$sql = "INSERT INTO students (student_name,student_family,student_birthday,student_email,student_password)
VALUES ('$name','$lastname' ,'$birthday' ,'$email','$hashed_password')";
	$result=$conn->query($sql);

	if ($result) {
echo "yes";
} 
		else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();


}else


{ $conn->close(); echo "no";}	

?>