Limit data on a page

General PHP Help
#1

How do I limit data on my page to just 50 records in the dataset? Currently, it is loaded the entire dataset on the page which is consuming a lot of resources. Here is what I have so far:

<?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);
    margin-right: 5px;
}
a:link {
	color: #FF3F00;
}
a:hover {
	color: #FCF5F1;
}
    </style>
    <link href="/styles/bootstrap.css" rel="stylesheet" type="text/css">
    <style type="text/css">
    @import url("styles/bootstrap.min.css");
    </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">V</option>
          <option value="102">C</option>
          <option value="103">S</option>
          <option value="104">M</option>
          <option value="105">W</option>
		  <option value="106">A</option>
          <option value="107">E</option>
          <option value="041">M</option>
		  <option value="042">L</option>
          <option value="009">D</option>
          <option value="001">H</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 = "SELECT 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) + '@test.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 width="100%" height="125" class="table table-hover table-bordered" id="example" style="width:100%">
        <thead>
          <tr>
            <th width="5%">ID</th>
	    <th width="10%">Control Number</th>	
		    <th width="10%">TSDS Number</th>
            <th width="13%">Last Name</th>
            <th width="15%">First Name</th>
			<th width="12%">Middle Name</th>
			<th width="14%">Birthdate</th>
            <th width="12%">Google Login</th>
            <th width="9%">Campus</th>
            <th width="14%">Network Login</th>
            <th width="10%">Grade</th>
			<th width="11%"></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['Student_ControlNumber'].'</td>';
			echo '<td>'.$row['StateStudentID'].'</td>';
			echo '<td>'.$row['name_l'].'</td>';
			echo '<td>'.$row['name_f'].'</td>';
			echo '<td>'.$row['name_m'].'</td>';
			echo '<td>'.$row['studentDOB'].'</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>Control Number</th>
			<th>TSDS Number</th>
            <th>Last Name</th>
            <th>First Name</th>
			<th>Middle Name</th>
			<th>Birthdate</th>
            <th>Google Login</th>
            <th>Campus</th>
            <th>Network Login</th>
            <th>Grade</th>
			<th></th>
		<td></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": true,
				"pageLength": 50,
				 "order": [[1,"asc"]]
            });
        });


    </script>
#2

What you are looking for is called pagination and here’s an example of it → 

/*
 * Using pagination in order to have a nice looking
 * website page.
 */

if (isset($_GET['page']) && !empty($_GET['page'])) {
    $current_page = urldecode($_GET['page']);
} else {
    $current_page = 1;
}

$per_page = 12; // Total number of records to be displayed:
$total_count = CMS::countAllPage('blog'); // Total Records in the db table:


/* Send the 3 variables to the Pagination class to be processed */
$pagination = new Pagination($current_page, $per_page, $total_count);


/* Grab the offset (page) location from using the offset method */
$offset = $pagination->offset();
//echo "<pre>" . print_r($offset, 1) . "</pre>";
//die();
/*
 * Grab the data from the CMS class method *static*
 * and put the data into an array variable.
 */
$cms = CMS::page($per_page, $offset, 'blog');
?>

/*
 * Pagination Format
 * Read all the data from the database table in an array format
 */
function readData($pdo, $table, $page, $perPage, $offset) {
    $sql = 'SELECT * FROM ' . $table . ' WHERE page=:page ORDER BY date_added DESC LIMIT :perPage OFFSET :blogOffset';
    $stmt = $pdo->prepare($sql); // Prepare the query:
    $stmt->execute(['perPage' => $perPage, 'blogOffset' => $offset, 'page' => $page]); // Execute the query with the supplied data:
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

Here’s a little more detail here at my GitHub repository - GitHub - Strider64/phototechguru: The Photo Tech Guru dealing with photography, website development and technology.
Plus you will find info on pagination doing an internet search

HTH

Sponsor our Newsletter | Privacy Policy | Terms of Service