week 7: Database Web Programming
Database Web Programming
HTML (HyperText Markup Language)
- HTML is the foundation of web pages and applications. It provides the structure and content of a webpage, defining elements like headings, paragraphs, images, links, forms, and more. HTML uses tags to mark up the content and allows browsers to interpret and render the page.
- HTML tags are
used to define different types of elements and structure the content. Tags are written
between angle brackets (<>). Some common HTML tags include:
- <h1> to <h6> : Headings, where h1 is the most important and h6 is the least important.
- <p> : Paragraphs of text.
- <a> : Anchors, used for hyperlinks.
- <img> : Images.
- <ul> : Unordered lists.
- <ol> : Ordered lists.
- <li> : List items.
- <div> : Generic container to divide sections.
- <span> : Inline container for styling specific parts of text.
- A basic
HTML Form:
<form>
<label for="username">Username</label>
<input type="text" id="username" name="username">
<label for="password">Password</label>
<input type="password" id="password" name="password">
<input type="submit" value="Register">
</form>
Sending Data & Functions
- Data is sent to itself using <form action='$_SERVER[PHP_SELF]' method='POST'>
- Invoking a function is easy enough, we just call the function name like registerForm();
<?php
function registerForm() {
return '
<form method="POST">
<label for="username">Username</label>
<input type="text" id="username" name="username">
<label for="password">Password</label>
<input type="password" id="password" name="password">
<input type="submit" value="Register">
</form>
';
}
?>
User Input
- HTML forms are the primary means of gathering user input on the web. They consist of various form elements like text fields, checkboxes, radio buttons, select dropdowns, and buttons. Use the <form> element to wrap form controls and specify the action attribute to define where the form data should be submitted.
- We can use PHP to check if we have user input.
- If we don't have user input the POST variable will either not exist or will be empty.
- To know if the POST variable is empty we simply ask if the POST array is empty. If the form has not been submitted, then a POST will be empty (or not set).
-
<?php
if (isset($_POST) && !empty($_POST)) echo(registerForm());
else echo("The POST array isn't set or is empty");
?>
Processing Input
- We can create a
function to deal with the processing of the input.
function registerUser() {
$pdo = new pdo('mysql:host=localhost;dbname=myDB', 'root', '');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
$un = $_POST['username'];
$pw = $_POST['password'];
$sql = "INSERT INTO user (username, password) VALUES (:userId, :userPassword)";
$stmt = $pdo->prepare($sql);
$stmt->execute([
'userId' => $un,
'userPassword' => $pw
]);
} - We should also
use Password Hashing. Password hashing in the context of a database refers to the
process of converting a user's plain-text password into a fixed-length string of characters
(the hash) using a cryptographic hashing algorithm. This hashed representation is then
stored in the database instead of storing the actual password in plain text. The purpose of
password hashing is to enhance security by protecting user passwords in case the database is
compromised. When passwords are stored as plain text, a security breach could expose all the
passwords, potentially leading to unauthorized access to user accounts on various websites
or applications. However, when passwords are hashed, it becomes significantly more
challenging for attackers to reverse-engineer the original passwords from the hashes.
function registerUser() {
$un = $_POST['username'];
$pw = $_POST['password'];
$pw = password_hash($pw, PASSWORD_DEFAULT);
...
} - SQL SELECT
Statements can also be used but they are a little trickier than inserting data because the
data returned will need to be stored somewhere. A SELECT statement could return multiple
rows so we have to iterate over the fetched results.
function loginUser() {
$un = $_POST['username'];
$pw = $_POST['password'];
$sql = "SELECT username, password FROM user WHERE username = :userId";
$pdo = new pdo('mysql:host=localhost;dbname=myDB', 'root', '');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
$stmt = $pdo->prepare($sql);
$stmt->execute([
'userId' => $un
]);
$stmt->setFetchMode(PDO::FETCH_ASSOC);
while ($row = $stmt->fetch()) {
if (password_verify($pw, $row['password'])) {
echo("Password OK - User Logged In.");
} else {
echo("Invalid Credentials");
echo("User" . $row['username']);
echo("Password" . $row['password']);
}
}
}
Practice Question
- To begin with,
create a HTML form which asks the user to input the details for an order which includes the:
- Order_ID
- Date
- Time
- Status
- Customer_ID
- Note: Clicking the button in the editor below will render the elements from the HTML code you have written.
- The following questions are based on the PHP code given below which selects and displays details of all workers hired after 1st April 2022 from the 'Workers' table
- Based on the code provided above, complete the SQL query to get the details of the workers hired after 1st April 2022 from the 'Workers' table
<?php
$host = "your_host";
$username = "your_username";
$password = "your_password";
$dbname = "your_dbname";
// Create a connection to the database
$conn = new mysqli($host, $username, $password, $dbname);
// Check the connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Function to retrieve worker detail
function getWorkerDetails(mysqli $conn) {
$sql = // CONSTRUCT THE QUERY
// Execute the query
$result = $conn->query($sql);
// Check if there are results
if ($result->num_rows > 0) {
// Process the results
while (COMPLETE THE WHILE CONDITION) {
// Output or process each order record
echo "Worker ID: " . $row["Worker_ID"] . ", First Name: " . $row["First_Name"] .", Last Name: " . $row["Last_Name"] .", Hired Date: " . $row["Hired_Date"];
}
} else {
echo "No workers found.";
}
// Close the database connection
}
// Call the function to get worker details
getWorkerDetails($conn);
?>
Select the correct way to loop through all the data returned by the query(Complete the while condition in the code above).
From the following options, select the correct way to close the connection.
End Of Week Quiz
Note: A score of 70% or higher would mean you
have successfully completed the week 7 workshop