How do I add a lookup field to this code

Ask about general coding issues or problems here.

Moderators: gesf, Michalio

Post Reply
mkamp81
New php-forum User
New php-forum User
Posts: 1
Joined: Wed Jun 29, 2022 7:44 am

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 appreciate any help.

Image
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(aledo.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">Vandagriff</option>
          <option value="102">Coder</option>
          <option value="103">Stuard</option>
          <option value="104">McCall</option>
          <option value="105">Walsh</option>
		  <option value="106">Annetta</option>
          <option value="110">McAnally</option>
          <option value="041">Middle School</option>
          <option value="009">Daniel</option>
          <option value="001">High School</option>
        </select>
        Last Name: 
        <input type="text" name="query" /> 
        <input type="submit" value="Search" />
          
        </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 . '%';
            
        }
        $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) + '@aledoisd.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{
            $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>
User avatar
Michalio
Moderator
Moderator
Posts: 205
Joined: Sun Jul 18, 2021 1:33 pm
Location: Poland

1. Should the field be text/number type?
2. Is the id field required?
If the field is optional then you need to add if statement to add the student id in the where clause and add the id to parameters.
The values are added to the query in insecure way:

Code: Select all

$tsql = sprintf($query,$lname,$campus);
I can send in the query part of the sql query to remove the Students table or whatever I want.
The code:

Code: Select all

                " 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{
            $query = $query . " ORDER BY name_l, name_f";
            $tsql = sprintf($query,$lname);
        }
        $stmt = sqlsrv_query( $conn, $tsql);
should look like this:

Code: Select all

                " WHERE name_l LIKE :name AND T1.LastUpdate = (SELECT MAX(LastUpdate) FROM [Students])";
	$params = [
	    'name' => $lname,
	];
        if ($campus <> '999'){
            $query = $query . " AND T1.campus_id = :campus";
            $params['campus'] = $campus;
        }
        
        $query = $query . " ORDER BY name_l, name_f";
        $stmt = sqlsrv_prepare( $conn, $tsql);
        foreach ($params as $key => $value) {
        	$stmt->bindParam(':'.$key, $value);
        }
        $result = sqlsrv_execute($stmt);
and use $result instead of $stmt to fetch rows.

Then you can add another if statement:

Code: Select all

        if ($studentId){
            $query = $query . " AND T1.student_id = :student";
            $params['student'] = $student;
        }
Free coding lessons: https://php-forum.com/phpforum/viewtopic.php?t=29852
Post Reply