Stop auto-increment on page refresh

ukphp · Dec 24, 2011

Hi,

I'm new to the forum and this is indeed my first post, so hello!

I'm new to PHP and MySQL so please excuse if I'm not coding correctly, all a part of the learning curve I guess. Anyways, I'm trying to create a basic data capture form which captures user entries on one page, then when the submit button is clicked writes the data to a database, then takes you through to another page which displays the data from the database.

I've got all of this working, however on the page which is displaying the data from the database, if you F5 to refresh the page it appears to add another row of data into the database. I have an ID setup as the primary key which is setup to auto-increment, so I'm guessing I need an if statment to say if no data has been inputted, do not auto-increment? Hope that makes sense! My code is below if you need to see it from the page which displays the data from the database. Not too sure how to go abouts this so any help would be great.

<?php

//connect to database
include_once ('config.php');

// define variables
$name = $_POST['name'];
$age = $_POST['age'];
$fav_animal = $_POST['fav_animal'];
$fav_food = $_POST['fav_food'];

// add data to database
$sql = "INSERT INTO basic_data_capture (name, age, fav_animal, fav_food) VALUES ('$name', '$age', '$fav_animal', '$fav_food')";

// show error message if failed to write to database
if (!mysql_query($sql)) {
	die('Error: ' . mysql_error());
}

// Get all the data from the table
$result = mysql_query("SELECT * FROM basic_data_capture") 
or die(mysql_error());  

echo "<table border='1'>";
echo "<tr> <th>Name</th> <th>Age</th> <th>Favourite Animal</th> <th>Favourite Food</th> </tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
	// Print out the contents of each row into a table
	echo "<tr><td>"; 
	echo $row['name'];
	echo "</td><td>"; 
	echo $row['age'];
	echo "</td><td>";
	echo $row['fav_animal'];
	echo "</td><td>"; 
	echo $row['fav_food'];
	echo "</td></tr>"; 
} 

echo "</table>";

// close connection to mysql
mysql_close();
?>

Thanks in advance.

bpat1434 · Dec 24, 2011

This is a fairly common issue with some scripts, the resubmission of the form. I'm willing to bet that if you hit refresh, a pop-up is shown saying something like "Do you want to resend the data?".

The simple answer is to wrap the following code in an if statement checking to see if $POST is in fact there and not empty.

<?php
// define variables
$name = $_POST['name'];
$age = $_POST['age'];
$fav_animal = $_POST['fav_animal'];
$fav_food = $_POST['fav_food'];

// add data to database
$sql = "INSERT INTO basic_data_capture (name, age, fav_animal, fav_food) VALUES ('$name', '$age', '$fav_animal', '$fav_food')";

// show error message if failed to write to database
if (!mysql_query($sql)) {
    die('Error: ' . mysql_error());
}

so you'd have something like:

if (isset($_POST) && is_array($_POST) && count($_POST) > 0)
{
	// define variables
	$name = $_POST['name'];
	$age = $_POST['age'];
	$fav_animal = $_POST['fav_animal'];
	$fav_food = $_POST['fav_food'];

// add data to database
$sql = "INSERT INTO basic_data_capture (name, age, fav_animal, fav_food) VALUES ('$name', '$age', '$fav_animal', '$fav_food')";

// show error message if failed to write to database
if (!mysql_query($sql)) {
    die('Error: ' . mysql_error());
} 
}

Now, that does no validation and means that an empty form could be sent. So it's better if you actually do validation to make sure your required fields are included and of the proper value(s):

<?php
if (
	isset($_POST) && is_array($_POST) &&
	(array_key_exists("name", $_POST) && strlen(trim($_POST["name"])) > 0) &&
	(array_key_exists("age", $_POST) && preg_match("/^[0-9]+$/", $_POST["age"])) &&
	(array_key_exists("fav_animal", $_POST) && strlen(trim($_POST["fav_animal"])) > 0) &&
	(array_key_exists("fav_food", $_POST) && strlen(trim($_POST["fav_food"])) > 0)
)
{
	// define variables
	$name = $_POST['name'];
	$age = $_POST['age'];
	$fav_animal = $_POST['fav_animal'];
	$fav_food = $_POST['fav_food'];

// add data to database
$sql = "INSERT INTO basic_data_capture (name, age, fav_animal, fav_food) VALUES ('$name', '$age', '$fav_animal', '$fav_food')";

// show error message if failed to write to database
if (!mysql_query($sql)) {
    die('Error: ' . mysql_error());
} 
}
else
{
	?>
	<p>Invalid data supplied; please try again.</p>
	<?php
}

As for the resubmission issue, the only way to fix that is to have the form post to a separate page which does the processing of the posted form data. Then once the processing is done (i.e. you've inserted it into the database successfully) redirect the user to another page which shows the success message and anything else you want.

For example:

index.php

<html>
	<head>
		<title>Form Test</title>
	</head>
	<body>
		<form action="process.php" method="post">
			<p>
				<label for="name">Name</label>
				<input type="text" id="name" name="name" />
			</p>
			<p>
				<label for="age">Age</label>
				<input type="text" id="age" name="age" />
			</p>
			<p>
				<label for="fav_animal">Favorite Animal</label>
				<input type="text" id="fav_animal" name="fav_animal" />
			</p>
			<p>
				<label for="fav_food">Favorite Food</label>
				<input type="text" id="fav_food" name="fav_food" />
			</p>
			<p>
				<input type="submit" name="submit" value="Submit" />
			</p>
		</form>
	</body>
</html>

process.php

<?php
if (
	isset($_POST) && is_array($_POST) &&
	(array_key_exists("name", $_POST) && strlen(trim($_POST["name"])) > 0) &&
	(array_key_exists("age", $_POST) && preg_match("/^[0-9]+$/", $_POST["age"])) &&
	(array_key_exists("fav_animal", $_POST) && strlen(trim($_POST["fav_animal"])) > 0) &&
	(array_key_exists("fav_food", $_POST) && strlen(trim($_POST["fav_food"])) > 0)
)
{
	// define variables
	$name = $_POST['name'];
	$age = $_POST['age'];
	$fav_animal = $_POST['fav_animal'];
	$fav_food = $_POST['fav_food'];

// add data to database
$sql = "INSERT INTO basic_data_capture (name, age, fav_animal, fav_food) VALUES ('$name', '$age', '$fav_animal', '$fav_food')";

// show error message if failed to write to database
if (!mysql_query($sql)) {
    die('Error: ' . mysql_error());
}

header("Location: success.php");
exit;
}
else
{
	?>
	<p>Invalid data supplied; please go back and try again.</p>
	<?php
}

success.php

<?php
// Get all the data from the table
$result = mysql_query("SELECT * FROM basic_data_capture")
or die(mysql_error());  

echo "<table border='1'>";
echo "<tr> <th>Name</th> <th>Age</th> <th>Favourite Animal</th> <th>Favourite Food</th> </tr>";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
    // Print out the contents of each row into a table
    echo "<tr><td>";
    echo $row['name'];
    echo "</td><td>";
    echo $row['age'];
    echo "</td><td>";
    echo $row['fav_animal'];
    echo "</td><td>";
    echo $row['fav_food'];
    echo "</td></tr>";
}

echo "</table>";

One last thing to note is that unless you are using persistent connections, you don't need to call mysql_close() as the connection will be closed at the end of script execution.

Hope that helps.

ukphp · Dec 25, 2011

Hi bpat1434,

Ah ok I see, thank you very much for taking the time to provide such a helpfull reply. Was exactly what I was after and more!

Yes that makes more sense like you have done it, to have a "process.php" page which is something I've seen used in tutorials, so will now use in future.

Thanks for the help.

Stop auto-increment on page refresh

Uukphp

bpat1434

Uukphp