PHP PDO Obtain information from MySQL table based on search results

I’m continuing work on software that I wrote a few months ago. Here’s my scenario:

When someone marks someone present, they have to enter the person’s last name in the Member Search box. As they do, a list appears with the person’s name and precinct they reside in.

The problem is, I need to add another field underneath this that pulls in the person’s address so that the person marking attendance will know they have selected the correct person.

For example, If I enter my own name:

image

I need a read-only field that will get my residential address from my database table. Is this possible?

Here is my table structure:

And the page’s HTML/PHP code:

<?php

include('nav/head.php');

$msg = "";

// Prepare SQL Statement
$stmt = $pdo->prepare("INSERT INTO attendance (member_id, member_email, member_phone, present, attend_state) VALUES (:member_id, :member_email, :member_phone, :present, :attend_state)");
$stmt->bindParam(':member_id', $member_id);
$stmt->bindParam(':member_email', $member_email);
$stmt->bindParam(':member_phone', $member_phone);
$stmt->bindParam(':present', $present);
$stmt->bindParam(':attend_state', $attend_state);

// insert a row and add new record
if(isset($_POST['btn_save'])) {
    try {
      $id = $_POST["member_id"];
      $ary = explode("-", $id);
      $member_id = $ary[0];
      $member_email = $_POST["member_email"];
      $member_phone = $_POST["member_phone"];
      $present = $_POST["present"];
      $attend_state = $_POST["attend_state"];
      $stmt->execute();
      header('Location: record_attn.php');
      exit();
    } catch(PDOException $e) {
      $msg = "This delegate has already been marked present";
    }
}

// insert a row and exit attendance screen
if(isset($_POST['btn_close'])) {
    try {
      $id = $_POST["member_id"];
      $ary = explode("-", $id);
      $member_id = $ary[0];
      $member_email = $_POST["member_email"];
      $member_phone = $_POST["member_phone"];
      $present = $_POST["present"];
      $attend_state = $_POST['attend_state'];
      $stmt->execute();
      header('Location: index.php');
      exit();
    } catch (PDOException $e) {
      $msg = "This delegate has already been marked present.";
    }
}

// return to home screen
if(isset($_POST['btn_cancel'])) {
  header('Location: index.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="">

<script src="https://code.jquery.com/jquery-1.12.4.min.js"></script>
<script src="js/member_search.js"></script>
<script src="js/phonenumber.js"></script>

  <title>CABGOP | Mark Attendance</title>

  <?php include('nav/header.php'); ?>

	<h1 class="h3 mb-2 text-gray-800">Mark Attendance</h1>
	<br>
  <div class="small">
       <p style="text-align: left"><b class="text-danger"><?php echo $msg; ?></b></p>
    </div>
	<form action="record_attn.php" method="post" id="record">
		<div class="form-group search-box">
			<label for="member_id">Member Search</label>
			<input type="text" class="form-control" id="member_id" name="member_id" placeholder="Enter Member's Last Name" maxlength="" autocomplete="off" autofocus="autofocus" 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="email" 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="tel" class="form-control" id="member_phone" name="member_phone" placeholder="(123) 456-7890" maxlength="14" autocomplete="off" onkeydown="javascript:backspacerDOWN(this,event);" onkeyup="javascript:backspacerUP(this,event);"/>
		</div>
		<div class="form-group">
			<label for="present">Attended &nbsp</label>
			<input type="checkbox" id="present" name="present" placeholder="" value="1" autocomplete="off" required />
		</div>
		<div class="form-group">
			<label for="attend_state">Plan to Attend State Convention? &nbsp</label>
      <input type="hidden" id="attend_state" name="attend_state" placeholder="" value="0" autocomplete="off" checked/>
      <input type="checkbox" id="attend_state" name="attend_state" placeholder="" value="1" autocomplete="off" checked/>
		</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>

If you want the extra information displaying as part of your search results (so the user knows which person to choose, when there is more than one with the same name) then I suspect you need to be looking in your member_search.js code as I can’t see anything in the PHP that handles the search. Or do you mean that you want an extra field in your form, rather than in the search results?

I want another field to show the address based on the search results.

The member_search.js looks like this:

$(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();
    });
});

This JS file is looking at a backend-search.php file which looks like this:

<?php

require('../dbconnect.php');

// Attempt search query execution
try{
    if(isset($_REQUEST["term"])) {
        // create prepared statement
        $sql = "SELECT id, last_name, first_name, middle_name, suffix, precinct FROM members WHERE last_name LIKE :term";
        $stmt = $pdo->prepare($sql);
        $term = $_REQUEST["term"] . '%';
        // bind parameters to statement
        $stmt->bindParam(":term", $term);
        // execute the prepared statement
        $stmt->execute();
        $result = $stmt->fetchAll();
		if($result) {
			foreach ($result as $row) {
				echo "<p>" . $row['id'] . " - " . $row['last_name'] . ", " . $row['first_name'] . " " . $row['middle_name'] . " " . $row['suffix'] . " (" . $row['precinct'] . ")</p>";
			}
		} else {
            echo "<p>No records found</p>";
        }
    }
} catch(PDOException $e){
    die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}

// Close statement
unset($stmt);

// Close connection
unset($pdo);

?>

So after the user has selected the search result they want, or while they’re deciding?

If that’s the case, I’m not sure how you’ll do that as there doesn’t seem to be any point where it reads the information out of the database once the search result is selected, though that might be my lack of JS knowledge that stops me spotting it. Does it populate the email and phone number form fields when you choose a member from the search?

The address field needs to be populated after the search is made and after the name is selected. I have the field created already as readonly because this field does not need to be edited. Consider my name as an example. Once I select my name (shown in the OP above), the address field would then need to fill in with my residential address that is listed in the database.

No. Email address and phone number are manually provided. This is because when we run the conventions, the people running the check-in will ask for an email or phone number as the attendee is being recorded as present for the event.

That makes me wonder if this is a case in JavaScript (or jQuery/AJAX) or PHP?

Doing so will result in a bad User eXperience (UX.) The user makes a guess and clicks on the first, duplicate name. The corresponding address is then displayed. User confirms address with person in front of them. Address is wrong. The user must then click on the search box again and re-type the last name to see the choices again. Repeat until the correct entry is found.

Displaying the address as part of the id - last name, first name, (precinct) [address] value, will allow the user to do this in a single step.

User starts typing last name, sees a list of matching names and addresses. Confirms which address belongs to the person in front of them. Selects the correct entry the first time.

The posted code has some issues -

  1. The submitted member_id value has a space after the id. You must remove this before using the numeric value.
  2. The post method form processing code needs to detect if a post method form has been submitted before executing any of the form processing code. The current code is preparing the insert query and binding input data every time the page gets requested, even if the query is never executed.
  3. If you use implicit binding (supply an array of input data to the execute([…]) call) and ? place-holders, you can eliminate a lot of repetitive, error-prone typing for every query.
  4. The only difference between the ‘save’ and ‘close’ form processing code is the redirect location. Don’t Repeat Yourself (DRY.) The only thing you should have separate ‘save’ and ‘close’ conditional logic for is the header location value.
  5. You need to validate all inputs in the form processing code before using them.
  6. The main member’s table has email and phone columns. If you are inputting this information at the time of attendance, it should be stored in the main member table, not the attendance table.
  7. For the catch/duplicate error handling logic, there are more possible errors than just a duplicate index. Your code must test the error number to see if it is for a duplicate index before producing the $msg value and if the error is not for a duplicate index, you need to re-throw the pdo exception and let php handle it.
  8. There’s no ‘btn_cancel’ in the form but if there was, you need an exit; statement after the header() redirect for that branch in the form processing code.
  9. In the backend-search.php code, there’s no good reason to unconditionally output the raw database statement error onto a live web page. This only gives hackers useful information when they intentionally do things to trigger errors. . Except for error handling for a recoverable error like a duplicate index, you should not have any try/catch logic in your code. Just let php catch and handle the exception in these cases.

Edit: 10. Associated with item #5, you need to re-populate the form fields upon a validation error so that the user doesn’t need to keep re-entering the data over and over.

This was the main reason I was trying to be absolutely certain about where the address was needed - I figured it would be better to display it in the search box rather than afterwards. It would also make the code change very simple indeed, because it just means grabbing that extra column and sticking it in the echo statement in backend-search.php. Loading it afterwards is much more complex.

I think that ought to read “should”. As it’s PDO and the conversion is handled without having to specify whether the parameter is numeric, string or anything else, I doubt the extra space will matter. It certainly doesn’t to intval(). OP could just explode(" - ", $id); though.

So, if the requirement is to load the address after the member has been selected, my only thought is another Ajax call to retrieve it based on the selected member id, which would go in the section of code in member_search.js headed “// Set search input value on click of result item”, unless someone knows of a better way.

Or, maybe change the search output to create a JSON-encoded object that contains two things - the search display array, and the search addresses array, and modify the search display to use the correct one for each. That would save an extra call to the server, at the expense of sending back addresses for all search results even though only one is needed. If you’re going to go that way, the object could have a way to extract just the member-id and stick that in a hidden form field, to remove the need for the explode() at all.

I’ll just add the address into the search results. It’ll be much easier to add and I think better when confirming the correct person rather than taking a second guess and have to select another person again.

My code editor is showing me that the $row['residential_address'] part is inside a string, but the code is working. Is there a syntax error somewhere in this line, or this just how the code is formatted in the editor:

echo "<p>" . $row['id'] . " - " . $row['last_name'] . ", " . $row['first_name'] . " " . $row['middle_name'] . " " . $row['suffix'] . " (" . $row['precinct'] . ") | " . $row['residential_address'] . "</p>";

Here is a picture of the code line in my code editor. Sorry that it is so small; it is a long line of code.

1 Like

Yes.

An equivalent of that line of code without the extra quotes and dots -

echo "<p>{$row['id']} - {$row['last_name']}, {$row['first_name']} {$row['middle_name']} {$row['suffix']} ({$row['precinct']}) | {$row['residential_address']}</p>";