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.

1st here: `$stmt->execute();` and then: `$conn->query($sql)` — Fakhruddin Ujjainwala, Mar 14 '16 at 05:03
It is because you are querying twice, I have added an answer to how to do that, and also how to take care of the id. — , Mar 14 '16 at 05:14

score 1 · Answer 1 · edited May 23 '17 at 12:15

1

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.

edited May 23 '17 at 12:15

Community

1
1

answered Mar 14 '16 at 05:03

Panda

6,955
6
40
55

As a matter of fact... I don't this it has to do with the part you guys are talking about. Though, when I commented out this part > $stmt->execute(); then it worked and submitted only once... – John Doe Mar 14 '16 at 05:07
@JohnDoe You can either use `if ($stmt->execute()) { ` or `if ($conn->query($sql) === TRUE) {`, the idea of the answer is that you should use one of those only, not both – Panda Mar 14 '16 at 05:10
@JohnDoe Also, you should stick to `execute()` for maximum security, more information at http://stackoverflow.com/questions/4700623/pdos-query-vs-execute. – Panda Mar 14 '16 at 05:11

score 1 · Answer 2 · 2016-03-14T05:13:01.327

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().

score 0 · Answer 3 · answered Mar 14 '16 at 05:31

 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();

MySQL Insert occurs twice on one submit

3 Answers3