I have in this php code, mssql script that was pulling 100s of records. I was able to fix that but now when a user selects a campus, it will not load any data for the campuses, it is blank. Can someone help me figure out how I need to rework either the script of the if statement? I am new to php but learning. Thanks

<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="107">Early Childhood Academy</option>
          <option value="041">Aledo Middle School</option>
		  <option value="042">McAnally 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" />
        <!--	if ($_SESSION['access'] == 2){-->
		<!--Student ID: <input type="text" name="query" value= '.$row['stu_id'].'> />
		<button type="submit" class="btn btn-primary btn-sm">Details</button>-->
				
        </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 = "declare @pagenumber int SET @PageNumber = 1 declare @pagesize int set @pagesize = 75 select * from (SELECT ROW_NUMBER() OVER (ORDER BY T1.stu_id asc) AS RowNum,T1.stu_id, T1.Student_ControlNumber,T1.StateStudentID, T1.grd_lvl, T1.name_f, T1.name_l ,T1.name_m, CONVERT(varchar(24),T1.StudentDOB) as 'studentDOB', 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])) as student_records where RowNum between (((@pagenumber - 1) * @pageSize )+ 1) 
and (@pagenumber * @pageSize ) ";
	
    if ($campus <> '999'){
            $query = $query . " AND common_name = '%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));
        }
 
        
    ?>

    The incorrect operation is because the two parts of the sql query that you are concatenating onto the end of the sql query statement, based on the $campus value, are part of the MAIN inner query, not part of the outer pagination query.

    Current versions of mssql server support OFFSET and FETCH arguments, e.g. OFFSET x ROWS FETCH NEXT y ROWS ONLY. This makes pagination simple. Also, because the x and y values are defined/calculated in the server-side code, don't use sql user variables for them. This is repetitious work for nothing (the page number is dynamic anyways so hard-coding it in the sql query is useless.)

    So, go back to your previous sql query code, add the OFFSET ... FETCH ... term to the end of the sql query statement, supplying the x and y values from the php code.

    Next, the sqlsrv database extension has a proper prepared/parametrized query. Use this instead of the current sprintf() code, which provides no protection against sql special characters breaking the sql query syntax, which is how sql injection is accomplished.

    Your php code must also perform the other actions needed for pagination -

    1. You need a SELECT COUNT(*) ... query to get the total number matching rows. The total number of matching rows is used to calculate the total number of pages. The FROM, JOIN, WHERE, and any HAVING terms in the main data retrieval query must be the same for this SELECT COUNT(*) query. The simple way to do this is build that part of the sql query statement in a php variable, and put any input values into an array (which you need for a prepared/parametrized query.) You would use this common part of the sql query statement and the array of input values when you build and execute both of the sql query statements.
    2. You need a $_GET['page'] input to the code for the requested page number. You need to limit the requested page number to be between 1 and the total number of pages. You would then calculate the OFFSET value for the query using the requested page number and the items per page value.
    3. You need to build and output pagination links. You would loop from 1 to the total number of pages, producing links with any existing $_GET parameters, e.g. the search inputs (which should use a get method form, not a post method form) , and each successive page number. Use php's http_build_query()to build the query string of the pagination links.
      Write a Reply...