My notes on how to create a LAMP form using PDO and MySQL:
Part 5: Search for a record
This is part 5 of a series:
Part 1 - Prepare mysql
Part 2 - Create the mysql login files
Part 3 - Retrieve all records
Part 4 - Insert a new record
Part 5 - Search for a record
Part 6 - Embed the form
Create a PHP form to search for a set of records:
Contents of lookup.php:
<?php // lookup.php
echo <<<_END
<html>
<head>
<title>Lookup Test</title>
</head>
<body>
<form method="post" action="lookup.php">
Last name: <input type="text" name="lastName"> <br>
<br>
<input type="submit" value="submit">
</form>
<hr>
<br>
_END;
// Set variable $lastName if it was provided via a POST method
if (isset($_POST['lastName']) && (!empty($_POST['lastName'])))
{
// This is the code that sanitizes the user's input string
//$lastName = filter_var($_POST['lastName'], FILTER_SANITIZE_STRING);
$lastName = filter_var($_POST['lastName'], FILTER_SANITIZE_STRING, FILTER_FLAG_STR
IP_HIGH | FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_BACKTICK | FILTER_FLAG_ENCODE_AMP );
}
if (!empty($lastName))
{
echo "Entered Name: $lastName<br>";
echo '<br>';
// Retrieve records from mysql database
// Retrieve database connection info
require_once '/var/forms/login_reader.php';
// Build data source name and options
$dsn = "mysql:dbname=$db;host=$host;charset=$charset";
$opt = [
PDO::ATTR_ERRMODE=> PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];
// Set $targetLastName to value provided
// This appends a wildcard to whatever the user provided so it brings back a set of results
$targetLastName = $lastName . "%";
// Now try to connect to the database and retrieve records
try {
// We use PDO instead of mysqli so we can switch from mysql to MS SQL if needed
$pdo = new PDO($dsn, $user, $pass, $opt);
// Prepare the SQL statement to protect against SQL injection attacks.
// Notice the use of the question mark (?) placeholder.
$statement = $pdo-> prepare( "
select Firstname, Lastname
from Form1
where Lastname LIKE ?
order by Firstname asc
LIMIT 20
" );
// Now that the SQL statement is prepared, execute it
// Provide the $targetLastName parameter
$statement->execute([$targetLastName]);
// If we found matching records, prepare to display them
if ($statement->rowCount() > 0)
{
echo "Matching records found:<br><br>";
// Display each matching record
foreach ($statement as $row)
{
echo $row["Firstname"] . " " . $row["Lastname"];
echo "<br>";
}
}
// What do we do if no matching records are found?
else
{
echo 'No matching records found.<br>';
}
}
// Deal with problems with the database
catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}
}
echo '<br>';
echo '<br>';
echo 'Click <a href="index.html">here</a> to return to Main Menu';
echo '<br>';
echo '</html>';
?>
