Page 1 of 1

How do I add a lookup field to this code

Posted: Thu Jul 07, 2022 9:06 am
by mkamp81
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:

Code: Select all

<?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>

Re: How do I add a lookup field to this code

Posted: Sat Jul 09, 2022 9:02 am
by Benjamin
Check the output of the $tsql variable around line 121 to see what conditions are being triggered. If you still need help, post that query here, in addition to the output of:

Code: Select all

echo '<pre>' . print_r($_POST, true) . '</pre>';'

Re: How do I add a lookup field to this code

Posted: Mon Jul 11, 2022 12:52 pm
by mkamp81
Thank you ,
I know the issue is here somewhere, but I am struggling to figure it out. It is something I think perhaps I am missing with the else, else if statement.

Code: Select all

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 = 'stu_id' 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));
        }        
    ?>