MySQL Insert occurs twice on one submit

Question

I have this PHP file that handle's users input to signup using mysql... I have a problem with it that makes the users input be entered twice... So, this was only one input into the signup form. Below is about half of my signup form (the most useful part)...

  if ($_SERVER["REQUEST_METHOD"] == "POST") {

    require("db-settings.php");

    // Security

    if (empty($_POST['name'])) {
      echo "Sorry, fullname input was empty, please retry if you like.";
      die();
    } else {
      $fullname = $_POST['name'];
    }
    if (empty($_POST['email'])) {
      echo "Sorry, email input was emty, please retry if you like.";
      die();
    } else { 
      $email = $_POST['email'];
    }
    if (empty($_POST['password'])) {
      echo "Sorry, password was empty, please retry if you like.";
      die();
    } else {
      $password = $_POST['password'];

      // If password variable is success to set, let's encrypt it now!
      $password = password_hash($password, PASSWORD_DEFAULT)."\n";
    }

  // Log users IP and store in variable
    $ip = $_SERVER["REMOTE_ADDR"];

  // Create connection
  $conn = new mysqli($servername, $username, $db_password, $dbname);
  // Check connection
  if ($conn->connect_error) {
      die("Connection failed: " . $conn->connect_error);
  }

  $sql = "INSERT INTO `table-ex` (fullname, email, password, ip) VALUES ('$fullname', '$email', '$password', '$ip')";
  $stmt = $conn->prepare($sql);
  //$stmt->bind_param('sss', $fullname, $email, $password, $ip);
  $stmt->execute();

  if ($conn->query($sql) === TRUE) {
      echo "New user was created successfully, please wait for activation...";
  } else {
    echo "Error: " . $sql . "<br>" . $conn->error;
  }

  $conn->close();

So, with all this here. I will also give the entire form section in the html code below...

  <form action="signup.php" method="post">
    <h1>Sign up</h1><br/>

    <span class="input"></span>
  <input type="text" name="name" placeholder="Full name" title="Format: Xx[space]Xx (e.g. John Doe)" autofocus autocomplete="off" required pattern="^\w+\s\w+$" />
    <span class="input"></span>
    <input type="email" name="email" placeholder="Email address" required />
    <span id="passwordMeter"></span>
    <input type="password" name="password" id="password" placeholder="Password" title="Password min 10 characters. At least one UPPERCASE and one lowercase letter" required pattern="(?=^.{10,}$)(?=.*[a-z])(?=.*[A-Z])(?!.*\s).*$"/>

    <button type="submit" value="Sign Up" title="Submit form" class="icon-arrow-right"><span>Sign up</span></button>
  </form>

So, there must be something in the code that makes it enter in twice... Plus, how do I reset the id numbers? Cause every time I make a new user, and this happens (which is every time) then I just delete the users and it still counts as though they still exist.

Answer 1

 if ($_SERVER["REQUEST_METHOD"] == "POST") {

    require("db-settings.php");

    // Security

    if (empty($_POST['name'])) {
      echo "Sorry, fullname input was empty, please retry if you like.";
      die();
    } else {
      $fullname = $_POST['name'];
    }
    if (empty($_POST['email'])) {
      echo "Sorry, email input was emty, please retry if you like.";
      die();
    } else { 
      $email = $_POST['email'];
    }
    if (empty($_POST['password'])) {
      echo "Sorry, password was empty, please retry if you like.";
      die();
    } else {
      $password = $_POST['password'];

      // If password variable is success to set, let's encrypt it now!
      $password = password_hash($password, PASSWORD_DEFAULT)."\n";
    }

  // Log users IP and store in variable
    $ip = $_SERVER["REMOTE_ADDR"];

  // Create connection
  $conn = new mysqli($servername, $username, $db_password, $dbname);
  // Check connection
  if ($conn->connect_error) {
      die("Connection failed: " . $conn->connect_error);
  }

  $sql = "INSERT INTO `table-ex` (fullname, email, password, ip) VALUES ('$fullname', '$email', '$password', '$ip')";
  $stmt = $conn->prepare($sql);
  //$stmt->bind_param('sss', $fullname, $email, $password, $ip);


  if ($stmt->execute()) {
      echo "New user was created successfully, please wait for activation...";
  } else {
    echo "Error: " . $sql . "<br>" . $conn->error;
  }
  $stmt->close();
  $conn->close();

Answer 2

You used both execute() and query(), thus executing twice.

Firstly, it inserted 1 row at $stmt->execute();. Then it inserted another row at $conn->query($sql).

$stmt->execute();

if ($conn->query($sql) === TRUE) {
    echo "New user was created successfully, please wait for activation...";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

You should only $stmt->execute();:

if ($stmt->execute()) { 
    echo "New user was created successfully, please wait for activation...";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

Note:

It's a better practice is to stick with prepared statements and use execute() for increased security rather than using $conn->query($sql). More information of the difference at PDO's query vs execute.

Answer 3

It's because of this line. You don't need to put an if else statement.

  if ($conn->query($sql) === TRUE) {
          echo "New user was created successfully, please wait for activation...";
      }

Simply do this-

    $sql = "INSERT INTO `table-ex` (fullname, email, password, ip) VALUES ('$fullname', '$email', '$password', '$ip')";
  $stmt = $conn->prepare($sql);
  //$stmt->bind_param('sss', $fullname, $email, $password, $ip);
//Set the variables here for $fullname, $email, $password and $ip
 if($stmt->execute())

 {
      echo "New user was created successfully, please wait for activation...";
} 
  else { echo "There was a problem";}

  $stmt->close();
  $conn->close();

UPDATE

For the id part, I assume you are using auto increment but I would suggest you to insert them manually instead of relying on it. I would suggest you to use a unique key derivation function and encoding them (in case you would prefer them to be plaintext and using them as IDs).

If you want to track how many entries are in there, you can always count the number of rows with mysqli_num_rows().