Insert the Data
Once the validations are complete it is important to check if there are any errors, or in this case if there are no errors. If all of the inputs are valid and no error message have been stored in the $errors array. then the code will insert the data to the database. The code to do this is a continuation of the same code block as the validation. The prepared statement code to complete this is shown below:
// Make sure there are no errors from the data validations
if(!$errors){
// Populate local variables with data sent from the form
$firstname = $_REQUEST['txtFName'];
$lastname = $_REQUEST['txtLName'];
$email = $_REQUEST['txtEmail'];
//The sha1 PHP function encrypts the password to a 40 character hash
$password = sha1(trim($_REQUEST['pswd1']));
// Set up the insert to a table using prepared statements
$stmt = mysqli_stmt_init($link); //$link is declared in the connection object
// Set up the SQL query statement
$sql = "INSERT INTO Registration (reg_firstname, reg_lastname, reg_username, reg_password) VALUES (?, ?, ?, ?)";
// Prepare query to be run by insuring a working query and connection
if(mysqli_stmt_prepare($stmt, $sql)) {
// Bind local variables that have pre-assigned values to the prepared statement note that all are strings
// Within the quotes are data type declarations: s = string; i = integer; d = double; b = blob
mysqli_stmt_bind_param($stmt, "ssss", $firstname, $lastname, $email, $password);
//Get back the results of the insert statement -- Will return True or False
$result_query = mysqli_stmt_execute($stmt);
// Test to see if the insert was successful
if($result_query){
// assume that the record was created and a new id was created using the auto_incrementing primary key field,
// we can capture that value using the mysqli_insert_id PHP function and assign it to a local variable
$newid = mysqli_insert_id($link);
// Create a message to confirm that the insert was successful
$insertworked = "Thank you $firstname, you are registered with an id of $newid.";
// Store the message into the session
$_SESSION['regsuccess'] = $insertworked;
} else {
$insertfailed = "Sorry, there was an error registering you. If the problem persists, contact the webmaster.";
}
// Close the prepared statement
mysqli_stmt_close($stmt);
}
/* close the database connection */
mysqli_close($link);
// Tests for messages. If a success, forward to the login page
if($insertworked){
$insertGoTo = "/tests/login.php";
header(sprintf("Location: %s", $insertGoTo));
}
// if there is no success message, the insertion process ends here and the page continues to load
}
}
?>
The SELECT Query
With the connection object obtained the prepared statement to query the database can be built. Immediately below the PHP code block used to get the connection object, create a new PHP code block. Within the code block add the needed prepared statement functions (refer to the example code below).
<?php
// Check if a form has been submitted with a continent selected
if($_GET['continent_submit']){
// The next query would select all countries from the Country table
// Note that the query is commented out and will not be used
//$query = "SELECT Name, IndepYear FROM Country ORDER BY Name";
// This is the query to be used that limits the countries by continent
$query = "SELECT Name, IndepYear FROM Country WHERE Continent = ? ORDER BY Name";
// This line captures the value of the continent from the form that has been submitted
$continent = $_GET['rbContinent'];
// Get instance of statement and assign it to a local variable
$stmt = mysqli_stmt_init($link); //$link is declared in the connection.php file
// Prepare query to be run by making sure you have a working query and connection
if (mysqli_stmt_prepare($stmt, $query)) {
// Bind Parameters (inputs from the submitted form) and declare their data type
// ['i' for integer, 's' for string, 'd' for double]
mysqli_stmt_bind_param($stmt, "s", $continent);
// Execute Statement
mysqli_stmt_execute($stmt);
// Bind results from the query to local variables -- correspond field names in query
mysqli_stmt_bind_result($stmt, $name, $year);
// Declare two empty arrays to store results
$row = array();
$rows = array();
// Fetch Value(s) (if any) and assign to local variable(s). It will continue
// until all rows are accounted for.
// Returns True if data was returned, False if an error, Null if nothing returned
while(mysqli_stmt_fetch($stmt)){
// Get variables values and assign them to associative array using named keys
// This array constitues a single record resulting from a query
$row['name'] = $name;
$row['year'] = $year;
// Store the row array into an array
// This larger array constitutes a recordset or set of multiple records
$rows[] = $row;
} // Ends the while(mysqli_stmt_fetch($stmt)) loop
// Counts the rows in the big array
$count = count($rows);
// free the server memory used with the database interaction
mysqli_stmt_free_result($stmt);
// Closes the Prepared Statement database interaction
mysqli_stmt_close($stmt);
} // Ends the if(mysqli_stmt_prepare($stmt, $query)) test
} // Ends the if($_GET['continent_submit']) test
?>
Reader Comments