How do I add a lookup field to this code

mkamp · July 18, 2022, 7:45pm

I am new to php and inherited this php site. I need to add a student id lookup field next to the last name lookup. I do not know how to do this. I have tried different things but nothing works. The student id field is referenced in the query in the below code. I am having problems getting the student id to populate correctly. We can enter a student id, however, that entered student id does not come up by itself, it comes up with hundreds of other students ids as well. I could not figure out how to post a picture of what the php site looks like.

I appreciate any help.

Here is the code to that:

<?php

// initialize session
include ("include/config.php");
ini_set('display_errors',1);
require_once ('./include/db_user.php');
function check_input($data)
{
        $data = trim($data);
        $data = stripslashes($data);
        $data = htmlspecialchars($data);
        return $data;
}
if(!isset($_SESSION['user'])) {
        // user is not logged in, do something like redirect to login2.php
        header("Location: login2.php");
        die();
}
?>
<!DOCTYPE html>
<html>
  <head>
    <title>Student Listing</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1" />
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.0/css/bootstrap.min.css" integrity="sha384-9gVQ4dYFwwWSjIDZnLEWnxCjeSWFphJiwGPXr1jddIhOegiu1FwO5qRGvFXOdJZ4" crossorigin="anonymous" />
	<link rel="stylesheet" href="https://cdn.datatables.net/1.10.16/css/dataTables.bootstrap4.min.css">
    <link rel="stylesheet" href="./styles/wam.css" />
    <style type="text/css">
body {
	background-color: #FCF5F1;
	background-image: url(school.png);
}
a:link {
	color: #FF3F00;
}
a:hover {
	color: #FCF5F1;
}
    </style>
  </head>
  <body>
    <br />
    <div class="container container-rounded bg-1">
      <h1 class="text-center">Student Locator</h1>
      <ul class="nav nav-tabs">
        <li class="nav-item">
          <a class="nav-link" href="protected.php">Home</a>
        </li>
        <li class="nav-item">
          <a class="nav-link-active" href="user_list.php">Student Locator</a>
        </li>
        <li class="nav-item">
          <a class="nav-link" href="staff_list.php">Staff Locator</a>
        </li>
        <li class="nav-item">
          <a class="nav-link" href="login2.php">Logoff</a>
        </li>
      </ul>
    </div>
    <br />
    <div class="container container-rounded bg-1">
      <form action="user_list.php" method="post">
        <p>Campus: 
        <select name="campus">
          <option value="999">All</option>
          <option value="101">school1</option>
          <option value="102">school2</option>
          <option value="103">school3</option>
          <option value="104">school4</option>
          <option value="105">school5</option>
		  <option value="106">school6</option>
          <option value="110">school7</option>
          <option value="041">school8</option>
          <option value="009">school9</option>
          <option value="001">school10</option>
        </select>
        Last Name: 
        <input type="text" name="query" /> 
        <input type="submit" value="Search" />
         Student ID: 
        <input type="number" name="student_id" /> 
        <input type="submit" value="StudentID" /> 
        </p>
      </form>
	   
	<?php
        if (!( isset($_POST['query']))) {
            echo "    </div>\n";
            echo "</body>\n";
            echo "</html>";
            exit;
        }ELSE{
            $lname= check_input($_POST['query']);
            $campus = check_input($_POST['campus']);
            $lname = $lname . '%';
            $studentId = check_input($_POST['student_id']);
            $studentId = $studentId.'%';
            
        }
        $connectionInfo = array(  "UID"=>$dbuser,
                "PWD"=>$dbpass,
                "Database"=>$dbname
        );
        $conn = sqlsrv_connect( $serverName, $connectionInfo);
        if( $conn === false ){
            echo "Unable to connect.</br>";
            die( print_r( sqlsrv_errors(), true));
        }
        $query = "SELECT T1.stu_id, T1.grd_lvl, T1.name_f, T1.name_l ,T2.common_name, RTRIM(T1.Pre2000StudentLogin) AS networklogin,RTRIM(T1.Pre2000StudentLogin) + '@school.org' AS gaccount" .
                " FROM [Students] AS T1 INNER JOIN [Campus] AS T2 ON T1.campus_id = T2.campus_id" .
                " WHERE name_l LIKE '%s' AND T1.LastUpdate = (SELECT MAX(LastUpdate) FROM [Students])";
        if ($campus <> '999'){
            $query = $query . " AND T1.campus_id = '%s' ORDER BY name_l, name_f";
            $tsql = sprintf($query,$lname,$campus);
        }ELSE IF(is_int($studentId)) { $query = $query . "AND T1.stu_id = '%s' ORDER BY name_l, name_f"; $tsql = sprintf($query, $studentId, $lname);
        }ELSE{
            $query = $query . " ORDER BY name_l, name_f";
            $tsql = sprintf($query,$lname);
            
        }
        $stmt = sqlsrv_query( $conn, $tsql);
        if( $stmt === false ){
            echo "Error in executing query.</br>";
            die( print_r( sqlsrv_errors(), true));
        }        
    ?>
      <!--Set up the table-->
      <table id="example" class="table table-hover table-bordered" style="width:100%">
        <thead>
          <tr>
            <th>ID</th>
            <th>Last Name</th>
            <th>First Name</th>
            <th>Google Login</th>
            <th>Campus</th>
            <th>Network Login</th>
            <th>Grade</th>
			<th></th>
          </tr>
        </thead>
		<tbody>		
	<?php
        while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
			//Send table rows
			echo '<tr>';
			echo '<td>'.$row['stu_id'].'</td>';
			echo '<td>'.$row['name_l'].'</td>';
			echo '<td>'.$row['name_f'].'</td>';
			echo '<td>'.$row['gaccount'].'</td>';
			echo '<td>'.$row['common_name'].'</td>';
			echo '<td>'.$row['networklogin'].'</td>';
			echo '<td>'.$row['grd_lvl'].'</td>';
			if ($_SESSION['access'] == 2){
				echo '<td><form action="student_detail.php" method="post" target="_blank">';
				echo '<input type="hidden" name="query" value='.$row['stu_id'].'>';
				echo '<button type="submit" class="btn btn-primary btn-sm">Details</button>';
				echo '</form></td>';
			}
			echo '</tr>';
        }       sqlsrv_free_stmt($stmt);
        sqlsrv_close( $conn);
        $lname = '';
        $campus = '';
        $studentid = '';
    ?>
		</tbody>
		<tfoot>
		<th>ID</th>
            <th>Last Name</th>
            <th>First Name</th>
            <th>Google Login</th>
            <th>Campus</th>
            <th>Network Login</th>
            <th>Grade</th>
			<th></th>
		</tfoot>
      </table>
    </div>
  </body>
</html>
<script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.0/umd/popper.min.js" integrity="sha384-cs/chFZiN24E4KMATLdqdvsezGxaGsi4hLGOzlXwp5UZB1LY//20VyM2taTB4QvJ" crossorigin="anonymous"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.0/js/bootstrap.min.js" integrity="sha384-uefMccjFJAIv6A+rW+L4AHf99KvxDjWSu1z9VI8SKNVmz4sk7buKt/6v9KI65qnm" crossorigin="anonymous"></script>
    <script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.10.16/js/dataTables.bootstrap4.min.js"></script>
    <script>
        $(document).ready(function() {
            $('#example').DataTable(
            {                "paging": false,
                "order": [[1,"asc"]]            });
        });

    </script>
![2022-07-07 08_58_23-c06ef5d7f3195b12cdbd1506f4e78e58d67aa9d6.png (1937×604)|690x201](upload://owd2jzYeBagLdbDtQlD2nmISTN8.png)

phdr · July 19, 2022, 1:04am

It’s not actually clear what you want to search for and when, i.e. do you want to search for either the campus/last name OR a student id, or all three at the same time, and you need to also define what should happen if no search has been submitted (should the query match all data or should a message tell the user to enter a search.) This would be the first thing to define.

There’s at least four logic mistakes -

Before the form has ever been submitted, the three inputs don’t exist AND all the logic for the search is not inside of the conditional test. This results in undefine variable messages and causes the search to not match any data, because the campus part of the search tries to match an empty value. This wastes a bunch of resources running a query that won’t match any data. If the intent is to instead display a message and NOT run the query, you need to correct the code so that it does this.
The default value for the campus ‘all’ choice should be an empty string. This is partly related to item #1. An empty value is not equal to ‘999’ and the current code thinks you are searching for a specific campus id that’s an empty value.
The order of the sprint() values is backwards in the student id logic, resulting in the last name trying to match the student id and the student id trying to match the last name.
The handling of the student id value, adding a wild-card % to it, would require a LIKE comparison in the query, but the query is using an = comparison. You need to decide which you are trying to use.

Next, about security, don’t use this check_input function. It’s a piece of junk from the w3schools site (if you did need to use a function like this, you would use it on the values right before the sql query, not before you use them in the php code.) The sqlsrv has a proper prepared query with place-holders in the sql query statement. Use this instead of the current sprintf() code or switch to the much simpler PDO extension.

Lastly, if you rearrange the code it will make it much easier to design, write, test, and debug. You should lay out the code in this general order -

Initialization
Post method form processing - you should be using a get method form for searching, the get method form should be ‘sticky’ and populate the field values with the exiting form data. Don’t use a post method form for searching.
Get method business logic - get/produce data needed to display the page. A majority of the php search code should go here. The result of this code is an array of data that the query matched. You would test/loop over this array in the html document.
The html document.

phdr · July 19, 2022, 11:43am

I think I have determined why when there was a student id being entered that all student id’s were being matched. You are adding the % wild-card to the input value, then testing if this value is_int. This test was failing and leaving the stu_id term out of the WHERE clause. Did you echo the sql query to see what it was when it wasn’t working?

I recommend that you dynamically build the WHERE clause, by adding each conditional term to an array, then imploding the array with the ’ AND ’ keyword and by adding each input value to an array of parameters that will be supplied to the sqlsrv_query() as the 3rd parameter in a proper prepared query. You would add any wild-card characters to the value as it is being added to the parameter array.