This is what the form currently looks like:
And this is what the search bar does:
The only thing the search is doing is a name lookup, not an ID lookup. Here is the form’s code:
<?php
include('nav/head.php');
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<meta name="description" content="">
<meta name="author" content="">
<style type="text/css">
/* Formatting search box */
.search-box{
width: 100%;
position: relative;
display: inline-block;
font-size: 16px;
}
.search-box input[type="text"]{
height: 32px;
padding: 5px 10px;
border: 1px solid #CCCCCC;
font-size: 16px;
}
.result{
position: absolute;
z-index: 999;
top: 100%;
left: 0;
}
.search-box input[type="text"], .result{
width: 100%;
box-sizing: border-box;
}
/* Formatting result items */
.result p{
margin: 0;
padding: 7px 10px;
border: 1px solid #CCCCCC;
border-top: none;
cursor: pointer;
background-color: #FFFFFF;
}
.result p:hover{
background: #f2f2f2;
}
</style>
<script src="https://code.jquery.com/jquery-1.12.4.min.js"></script>
<script type="text/javascript">
$(document).ready(function(){
$('.search-box input[type="text"]').on("keyup input", function(){
/* Get input value on change */
var inputVal = $(this).val();
var resultDropdown = $(this).siblings(".result");
if(inputVal.length){
$.get("api/search/backend-search.php", {term: inputVal}).done(function(data){
// Display the returned data in browser
resultDropdown.html(data);
});
} else{
resultDropdown.empty();
}
});
// Set search input value on click of result item
$(document).on("click", ".result p", function(){
$(this).parents(".search-box").find('input[type="text"]').val($(this).text());
$(this).parent(".result").empty();
});
});
</script>
<title>CCRP | Record Attendance</title>
<?php include('nav/header.php'); ?>
<h1 class="h3 mb-2 text-gray-800">Record Attendance</h1>
<br>
<form action="api/attn_record.php" method="post">
<div class="form-group search-box">
<label for="member_name">Member Search</label>
<input type="text" class="form-control" id="member_name" name="member_name" placeholder="Enter Member's Last Name" maxlength="" autocomplete="off" required />
<div class="result"></div>
</div>
<!--<div class="form-group">
<label for="member_id">Member ID</label>
<input type="text" class="form-control" id="member_id" name="member_id" placeholder="" maxlength="255" autocomplete="off" readonly/>
</div>-->
<div class="form-group">
<label for="member_email">Member's Email Address</label>
<input type="text" class="form-control" id="member_email" name="member_email" placeholder="" maxlength="255" autocomplete="off" />
</div>
<div class="form-group">
<label for="member_phone">Member's Phone Number</label>
<input type="text" class="form-control" id="member_phone" name="member_phone" placeholder="" maxlength="14" autocomplete="off" />
</div>
<div class="form-group">
<label for="present">Attended  </label>
<input type="checkbox" id="present" name="present" placeholder="" value="1" autocomplete="off" required />
</div>
<div class="form-group">
<label for="alternate">Plan to Attend State Convention?  </label>
<input type="checkbox" id="alternate" name="alternate" placeholder="" value="2" autocomplete="off" />
</div>
<input type="submit" name="btn_save" class="btn btn-success" value="Save and Add New">
<input type="submit" name="btn_close" class="btn btn-danger" value="Save and Close">
</form>
</div>
<!-- /.container-fluid -->
</div>
<!-- End of Main Content -->
<?php include('nav/footer.php'); ?>
</html>
This is the backend-search.php file where the data is retrieved from the user input:
<?php
require('../dbconnect.php');
// Attempt search query execution
try{
if(isset($_REQUEST["term"])){
// create prepared statement
$sql = "SELECT name FROM members WHERE name LIKE :term";
$stmt = $pdo->prepare($sql);
$term = $_REQUEST["term"] . '%';
// bind parameters to statement
$stmt->bindParam(":term", $term);
// execute the prepared statement
$stmt->execute();
if($stmt->rowCount() > 0){
while($row = $stmt->fetch()){
echo "<p>" . $row["name"] . "</p>";
}
} else{
echo "<p>No matches found</p>";
}
}
} catch(PDOException $e){
die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}
// Close statement
unset($stmt);
// Close connection
unset($pdo);
?>
This is the authentication that PHP does in the background (the attn_record.php file):
<?php
include ('dbconnect.php');
$stmt = $pdo->prepare("INSERT INTO attendance (member_name, member_email, member_phone, present) VALUES (:member_name, :member_email, :member_phone, :present)");
$stmt->bindParam(':member_name', $member_name);
$stmt->bindParam(':member_email', $member_email);
$stmt->bindParam(':member_phone', $member_phone);
$stmt->bindParam(':present', $present);
// insert a row and add new record
if(isset($_POST['btn_save'])) {
$member_name = $_POST["member_name"];
$member_email = $_POST["member_email"];
$member_phone = $_POST["member_phone"];
$present = $_POST["present"];
$stmt->execute();
header('Location: ../record.php');
}
// insert a row and exit attendance screen
if(isset($_POST['btn_close'])) {
$member_name = $_POST["member_name"];
$member_email = $_POST["member_email"];
$member_phone = $_POST["member_phone"];
$present = $_POST["present"];
$stmt->execute();
header('Location: ../index.php');
}
?>
And (just in case) here is the structure of the attendance table:
So, someone comes up to me and gives me their last name. I put in the person’s last name in the search bar. That search bar adds the person’s name in the attendance table and I add their email and updated phone number and mark them as present.
What I want to do with this is run a report on a different page that’s basically saying “I need a report showing me who all was here with their name and address sorted by what precinct they reside in.” I would like to generate this report in both XLSX and PDF files that the admin can download to look at. The problem in generating the report is that I can’t get the information I need because I’m not calling in the information in the table. I’m not sure what the best way to do that is…
