MySQL Prepared Statements Lab - Hsanokklis/2023-2024-Tech-journal GitHub Wiki

Lab Prep

Set up MySQL and PHP server from recent modules

Make sure pets database is loaded

Connect to MySQL and the database

Using the birthday.html and scripts as a template, you will create a version using prepared statements.
Copy birthday.html to birthday-sec.html. Update the new html file to point to birthday-sec.php
Create birthday-sec.php using prepared statements:
- The first part of the php script with the database variables, $name=$_POST['name'] and "$mysqli = new ..." will be the same.

Then it gets different:

Create statements
- Example: $stmt=$mysqli->prepare("SELECT birth from cats where name=?");
Bind the parameter to the ? - in our example, what is the cat's name:
- $stmt->bind_param('s',$variable that you set from the $_POST);
Execute with $stmt->execute();
Bind the results - you need to set variables for the result of the query - in our example the birthdate + cat's name.
- $stmt->bind_result($birth); //This creates the variable with the birthday result
To use a If...Else 'No results', you need to use:
- $stmt->store_result(); //After the bind_result so the script knows how many rows are returned.
- Then a if ($stmt->num_rows >0) {
To fetch the results in a While loop
- while ($stmt->fetch()) {
And then echo the results
- Example: echo $name."'s birthday is ".$birth;
And the Else to close the logic, in case there's nothing matching ... including SQL injection attempts!

example: else {
echo 'No Results';
}