I have a website that I am creating for showing reports. all the reports are in table format and are pulled from SQL using PHP. I have the pages working except they take a long time to pull the data when there are more than a few hundred rows. There are usually more than 30 columns in these table. I would like to know if there is a way to either make the tables load faster or, preferably, split the tables into multiple pages that will have navigation buttons at the bottom to move forward or backward through the table.
I am trying to do this using only PHP, HTML and CSS. I don't have direct access to the servers where the code resides, just to my code. so updating anything other than my code would be very difficult.
Here is the code that I've got for pulling and displaying that tables now:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8" />
<title>QDef</title>
<link rel="StyleSheet" href="StyleSheet.css" type="text/css">
</head>
<body>
<div class="menu-wrap">
<nav class="menu">
<ul class="clearfix">
<li><a href="default.aspx">Home</a></li>
<li><a href="#">Material Tracking</a>
<ul class="sub-menu">
<li><a href="SearchStateProject.php">Search by State or Contract Number</a></li>
<li><a href="MaterialTrackingAllStates.php">All Contracts</a></li>
</ul>
</li>
<li><a href="#">OPR Reports</a>
<ul class="sub-menu">
<li><a href="COEI_OPR_Filtered.php">COEI OPR Filtered</a></li>
<li><a href="OSP_OPR_Filtered.php">OSP OPR Filtered</a></li>
</ul>
</li>
<li><a href="#">Admin</a>
<ul class="sub-menu">
<li><a href="QDef.php">QDef</a></li>
<li><a href="CheckPHP.php">PHP Check</a></li>
<li><a href="EditQDefForm.php">Edit QDef form</a></li>
<li><a href="FormToEditMaterial.php">Form to Edit Material</a></li>
<li><a href="TableUpdates.php">Table Updates</a></li>
</ul>
</li>
</ul>
</nav>
</div>
<br>
<br>
<h1>QDef Table</h1>
<?php
$configs = include('DBConn.php');
$servername = $configs['ServerName'];
$username = $configs['UserName'];
$password = $configs['Password'];
$dbname = $configs['DBName'];
$limit = 15;
try
{
$conn = new PDO("sqlsrv:server=$servername;database=$dbname", $username,$password);
//set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->setAttribute(PDO::SQLSRV_ATTR_QUERY_TIMEOUT, 1);
//echo "Connected Successfully<br>" /*. $conn*/;
/*If conncected see if we can pull any data!*/
}
catch(Exception $e)
{
die( print_r( $e->getMessage()));
}
$TotalRows = $conn->query('select count(*) from pmdb.v_QDefs')->fetchColumn(); //How many rows in the table
$pages = ceil($TotalRows / $limit); //How many page will there be
$currentpage = 1;
$currentpage = min($pages, filter_input(input_get, 'page', filter_validate_int, array('options' => array('default' => 1,'min_range' => 1,),))); //What page are you currently on
var_dump($currentpage);
$offset = abs(($currentpage - 1) * $limit); //Calculate Offset
//Some info to display to the user?
$start = $offset + 1;
$end = min(($offset + $limit),$TotalRows);
$prevlink = ($currentpage > 1) ? '<a href="?page=1" title="First page">«</a> <a href="?page=' . ($currentpage - 1) . '" title="Previous page">‹</a>' : '<span class="disabled">«</span> <span class="disabled">‹</span>'; //the back link
$nextlink = ($currentpage < $pages) ? '<a href="?page' . ($currentpage + 1) . '" title="Next page">›</a> <a href="?page=' . $pages . '" title="Last page">»</a>' : '<span class="disabled">›</span> <span class="disabled">»</span>'; //the Forward link
echo '<div id="paging"><p>', $prevlink, ' Page ',$currentpage, ' of ',$pages,' pages, displaying ',$start,'-',$end, ' of ',$TotalRows,' results ',$nextlink,' </p></div>'; //display the paging information
$tsql = "select Id,QSrc,QName,isActive,RunReport,FilePath,QDef from pmdb.v_QDefs order by Id OFFSET $offset ROWS FETCH NEXT $limit ROWS";
//echo $tsql . "<br>";
$getqueries = $conn->query($tsql);
$queries = $getqueries->fetchALL(PDO::FETCH_ASSOC);
$countqueries = count($queries);
if(isset($countqueries))
{
if($countqueries > 0)
{
//echo "There are queries returned";
BeginQueriesTable($countqueries);
$CountValues = 1;
foreach($queries as $query)
{
PopulateQueryTable($query,$CountValues);
$CountValues = !$CountValues;
}
EndQueriesTable();
}
else
{
echo "<br>Values returned: $countqueries";
}
}
else
{
echo "No count";
}
function BeginQueriesTable($rowCount)
{
$headings = array("Edit","Id","QSrc","QName","isActive","RunReport","FilePath","QDef");
echo "<p class=" . chr(34) . "headings" . chr(34) . ">$rowCount Results</p>";
echo "<table class=" . chr(34) . "tab" . chr(34) . "id=" . chr(34) . "OuterTable" . chr(34) . ">";
echo "<tr>";
foreach($headings as $heading)
{
echo "<th class=" . chr(34) . "cell" . chr(34) . ">$heading</th>";
}
echo "</tr>";
}
function PopulateQueryTable($values,$Number)
{
$queryID = $values['Id'];
//var_dump($values);
//echo "<br/>";
//var_dump ($queryID);
echo "<tr class=" . chr(34) . "row" . ($Number) . chr(34) . "><td><a href=" . chr(34) . "EditQDefForm.php?id=" . $values['Id'] . chr(34) . ">Edit</a></td>";
foreach($values as $key=>$value)
{
if(!is_null($value))
{
echo "<td>$value</td>";
}
else
{
echo "<td></td>";
}
}
echo "</tr>";
}
function EndQueriesTable()
{
echo "</table><br/>";
}
?>
</body>
</html>
Right now this table will pull about 15000 rows. I know this isn't huge, but it takes 5 - 10 minutes to load because there are 61 columns some of which are Comments that are very long.
Let me know if you need more info, any help would be very appreciated.
