Am just a beginner in PHP, am trying to implement user registration using bellow codes. But when am testing to register a user am getting "No data supplied for parameters in prepared statement" error please anyone with an idea please help..

session_start ();
require_once ( "../include/config.php" );
require_once ( "../functions/functions.php" );
$message = array ();

//check if the form has been submitted
if ( isset ( $_POST [ 'submitted' ]) )
{
$firstname = sanitize( $connection , $_POST [ 'firstname' ] );
$lastname = sanitize( $connection , $_POST [ 'lastname' ] );
$username = sanitize( $connection , $_POST [ 'username' ] );
$password = sanitize( $connection , $_POST [ 'password' ] );
$confirm_password = sanitize( $connection , $_POST [ 'confirm_password' ] );
$status_id = sanitize( $connection , $_POST [ 'status' ] );

//check if username exist in the database
$stmt = $connection ->prepare( "SELECT * FROM users WHERE username =?" );
$stmt ->bind_param( "s" , $username );
$stmt ->execute();

$results = $stmt ->get_result();

if ( $results -> num_rows > 0 )
{
$message [ 'error' ] = "Username is not available!<br>Choose another and try again" ;
$_SESSION [ 'error' ] = $message ;
header ( "Location:../../public_html/user_registration.php" );
}

else
{
//check password matching
if ( $password !== $confirm_password )
{
$message [ 'error' ] = "Passwords do not match!<br>Make sure password and confirm password fields match" ;
$_SESSION [ 'error' ] = $message ;
header ( "Location:../../public_html/user_registration.php" );
}

else
{
//encrypt and salt the password
$presalt = "@&^3*!" ;
$postsalt = "%&*$!" ;

$salted_password = $presalt . $password . $postsalt ;
$hashed_password = hash ( 'ripemd128' , $salted_password );

//inserting the user
$user_id = "" ;
$date_created = date ( 'Y-m-d H:i:s' );
$date_edited = "" ;

$stmt = $connection ->prepare( "INSERT INTO users values(?,?,?,?,?,?,?,?)" );
$stmt- >bind_param( "i,s,s,s,s,s,s,s" , $user_id , $firstname , $lastname , $username , $hashed_password , $date_created , $date_edited , $status_id );

$result = $stmt ->execute();

if ( $result )
{
echo "<i>Successful registered a user..</i>" ;
header ( "refresh:1;url=../../public_html/user_registration.php" );
}

else
{
$message [ 'error' ] = "User registration failed!<br>" . $stmt -> error ;
$_SESSION [ 'error' ] = $message ;
header ( "Location:../../public_html/user_registration.php" );
}
}
}


}

Obviously if you don't have all the data required for the insert, you are going to get an error. One issue that you have made for yourself unnecessarily is:

$user_id=""; $date_created = date( 'Y-m-d H:i:s' ); $date_edited ="";

So i'm going to assume that you have an auto_increment for user_id, and date_edited can be null, and since this is a new row you want that to be NULL on insert.

In this case, you should not be passing values for these. MySQL figures out how to auto_increment, and it makes no sense to pass a NULL date string for a date parameter that you will never set in this context.


Change the insert so that those are not included:

$stmt = $connection->prepare("INSERT INTO users values(?,?,?,?,?,?)"); $stmt->bind_param("s,s,s,s,s,s", $firstname, $lastname, $username, $hashed_password, $date_created, $status_id);

there would be a related and helpful php error, if php's error_reporting/display_errors were set properly, that would help identify that the problem is most likely due to the wrong format for the 1st bind_param() parameter. this should be a string of just the format specifiers, without commas between them.

next, whatever your sanitize() function code is doing, it is either one or all of - ineffective, insecure, or redundant and is probably not needed. could you post the code for the sanitize() function.

the current code will allow empty value(s) to be used for all the fields (an empty username won't match any existing user and an empty password will be equal to an empty confirmation password.)

you should validate all the inputs before using them and validate all the inputs at once. you are only validating some of the data and you are stopping at the first validation error, so it will take multiple form submissions to validate all of the data, but since you are redirecting back to the form, the user must keep filling in all the form values after each error, which will increase the likely-hood of introducing more errors.

you should put the form processing code and the form on the same page so that you can display all the validation errors when you re-display the form and you can populate the form fields with the existing values so that the user doesn't need to keep re-tying in all the values when there is a validation error.

edit, you should also list the column names in the INSERT query so that your code will be self-documenting and will still work if the columns get rearranged.

edit, you should also list the column names in the INSERT query so that your code will be self-documenting and will still work if the columns get rearranged.

^^^ and which is required if you leave out a column value.

Obviously if you don't have all the data required for the insert, you are going to get an error. One issue that you have made for yourself unnecessarily is:

$user_id=""; $date_created = date( 'Y-m-d H:i:s' ); $date_edited ="";

So i'm going to assume that you have an auto_increment for user_id, and date_edited can be null, and since this is a new row you want that to be NULL on insert.

In this case, you should not be passing values for these. MySQL figures out how to auto_increment, and it makes no sense to pass a NULL date string for a date parameter that you will never set in this context.

Change the insert so that those are not included:

$stmt = $connection->prepare("INSERT INTO users values(?,?,?,?,?,?)"); $stmt->bind_param("s,s,s,s,s,s", $firstname, $lastname, $username, $hashed_password, $date_created, $status_id);

Thanks for the useful comment, i will revise and remove unnecessary variable; although the problem was due to commas on type specifier on bind param()

changed to;

$stmt = $connection ->prepare( "INSERT INTO users ( firstname , lastname , username , password , date_created , status_id ) VALUES (?,?,?,?,?,?)" );

$stmt ->bind_param( "sssssi" , $firstname , $lastname , $username , $hashed_password , $date_created , $status_id );

and it worked!..