how to avoid/stop duplicate insertion for php and html?

Question

Hi does anyone knows how to avoid/stop duplicate insertion for php and html? Whenever I refresh, the same data repeats which is not what I want. Is there anything that I can change from here? I heard that adding UNIQUE INDEX can avoid/stop it. Maybe it works, but I place it wrongly which makes it unable to work. Any help? Thanks in advance!!! Will really appreciate it!

<?php
session_start();
include("Validation.php");

$connect=mysqli_connect("localhost","root","","jailbird");
if(mysqli_error($connect))
{
die("Could not connect.");
}

if(isset($_POST["insert_click"]))
{
//Bookingid is auto increment, therefore no need
//$Bookingid=$_POST["BookingID"];
$Prisonerid=$_SESSION['Prisonerid'];
$Visiting_method=$_POST["VisitingMethod"];
$Visiting_location=$_POST["VisitingLocation"];
$Date=$_POST["Date"];
$Time=$_POST["Time"];

$query=$connect->prepare("insert into Booking(PrisonerID, VisitingMethod, VisitingLocation, Date, Time) values (?,?,?,?,?)");
$query->bind_param('sssss', $Prisonerid, $Visiting_method, $Visiting_location, $Date, $Time);
$query->execute();
}

$query=$connect->prepare("select * from booking WHERE Prisonerid=?");
$query->bind_param('s',$_SESSION['Prisonerid']);
$query->execute();
$query->bind_result($Bookingid, $Prisonerid, $Visiting_method, $Visiting_location, $Date, $Time);

while($query->fetch())
{
echo "<tr>";
//echo "<td width=60>".$Bookingid."</td>";
echo "<td>$Prisonerid</td>";
echo "<td>$Visiting_method</td>";
echo "<td>$Visiting_location</td>";
echo "<td>$Date</td>";
echo "<td>$Time</td>";
echo "</tr>";
}   
?>

Answer 1

Here is a small class I wrote (not tested)

<?php
class FormID {
    private $lastFormID = "";
    private $newFormID = "";

    function __construct(){
        $this->lastFormID = $_SESSION['__frmid__'];
        $_SESSION['__frmid__'] = uniqid('sm');
        $this->newFormID = $_SESSION['__frmid__'];
    }

    public function isFormDataValid(){
        return (isset($_POST['__frmid__']) && $_POST['__frmid__']==$this->lastFormID);
    }

    public function FromIDHTML(){
      return "<input type=\"hidden\" name=\"__frmid__\" value=\"{$this->newFormID}\"/>";
    }

}
?>

You can use it like this

<?php
    $frmid = new FormID();

    if ($frmid->isFormDataValid()){
      //do your insert here
    }
?>

<form method="post">
<?php echo $frmid->FromIDHTML(); ?>
<input .... />
</form>

Edit: Here is how to use this in your code

change

if(isset($_POST["insert_click"]))
{

to

$frmid = new FormID();
if(isset($_POST["insert_click"]) && $frmid->isFormDataValid())
{

And in the file where you generate the HTML form do something like this

<form method="post">
<?php $frmid = new FormID(); echo $frmid->FromIDHTML(); ?>
<!--Below is your form with all the fields -->
<input type="text" name="VisitingMethod" />

</form>

Note i have just added one line in the form <?php $frmid = new FormID(); echo $frmid->FromIDHTML(); ?>

let me know if you have any problems.

Answer 2

You can make the PrisonerID a primary key in your MySql table that way no duplicates will be allowed.

Answer 3

Like you've already said, you need to add a UNIQUE constraint to a field in your table you know can't have two values. When you say maybe it works it means to haven't you really tried that method. And we can't help unless you give more details about how you tried that and what went wrong.

For the UNIQUE constraint to work, you need to choose which fields in your table uniquely represent each row. For example, let's assume the PrisonerId uniquely represent each row. Then when your create the table you need to add the UNIQUE contraint to the PrisonerId field as follows:

CREATE TABLE `Booking` (
  `PrisonerId` int(10) NOT NULL,
  `VisitingMethod` varchar(100) NOT NULL,
  # Put other fields here #
  UNIQUE KEY `uc_PrisonerId` (`PrisonerId`)
) ENGINE=InnoDB;

You run that query when creating the table, or you can ALTER the table to add the new contraint or use phpMyAdmin/MySQL Workbench or whatever else method you prefer and when you insert a new record MySQL will make sure it respects the new constraint.

Also, note that you can still insert duplicate can't insert duplicate rows but can silence error messages if you want using the IGNORE keyword in your query. See insert documentation

The other approach, and expensive, would be to check if the new records aren't in the database yet. If they are, don't insert them, else do.

Finally consider redirecting from the form handler (the url in the action of the form) after the form submission. See here