php - Inserting data using mysqli

Question

This code gets through all of the debugs but for some reason, it is still not inserting. It tries to check if the username already exists in the database and if it doesn't, it adds it. For some reason, it still doesn't add it to the data table. It does get to the insert part but it doesn't add a row.

<?php 
require "conn.php";
echo "debug 1";
$stmt = $conn->prepare("SELECT * FROM UserData WHERE username = ?");
$stmt->bind_param('s', /*$_POST["username"]*/ $username );
$username = 'hi'; 
$stmt->execute();
$stmt->store_result();

echo "debug 2";
if ($stmt->num_rows == 0){ // username not taken
  echo "debug 3";
  $stmt2 = $conn->prepare("INSERT INTO UserData (username, password) VALUES (?, ?)");
  $password =(/*$_POST["password"]*/ "hey");
  $username =(/* $_POST["username"]*/ "hi");  
  $stmt2->bind_param('s',$username);
  $stmt2->bind_param('s',$password);
  $stmt2->execute();
  if ($stmt2->affected_rows == 1){
    echo 'Insert was successful.';

  }else{ echo 'Insert failed.';
   var_dump($stmt2);
  }
}else{ echo 'That username exists already.';}

?>

Answer 1

You should bind all variables once with bind_param() and not twice or N times. The correct way is pass first the types followed by the variables.

change:

$stmt2->bind_param('s',$username);
$stmt2->bind_param('s',$password);

By

$stmt2->bind_param('ss',$username, $password);

With php5.6 >= you can pass an array with ... operator to simplify.

$data = array('user' => 'someUser', 'password' => 'secret');
$stmt2->bind_param('ss', ...$data);