How load a table into multiple webpages using HTML?


Tags: php,html,css,sql-server

Problem :

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">&laquo;</a> <a href="?page=' . ($currentpage - 1) . '" title="Previous page">&lsaquo;</a>' : '<span class="disabled">&laquo;</span> <span class="disabled">&lsaquo;</span>'; //the back link
    $nextlink = ($currentpage < $pages) ? '<a href="?page' . ($currentpage + 1) . '" title="Next page">&rsaquo;</a> <a href="?page=' . $pages . '" title="Last page">&raquo;</a>' : '<span class="disabled">&rsaquo;</span> <span class="disabled">&raquo;</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.



Solution :

i have figured it out with help from the link that @markb sent in the comments above (sql-Simple PHP Pagination Script). I ended up using the link in that answer:

Basic pagination tutorial

I tried with the accepted answer to the question, but could not get it to work. Here is what I got to work for me:

    <!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; //Create the max limit per page

    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()));
    }
    //How many rows in the table
    $TotalRows = $conn->query('select count(*) from pmdb.v_QDefs')->fetchColumn();
    //How many pages will there be
    $pages = ceil($TotalRows / $limit);
    //echo $pages;
    if(isset($_GET['currentpage']))
    {
        //var_dump($_GET);
        $currentpage = $_GET['currentpage'];
    }
    else
    {
        $currentpage = 1;
    }

    // if current page is greater than total pages...
    if ($currentpage > $pages) 
    {
       $currentpage = $pages;
    } 

    // if current page is less than first page...
    if ($currentpage < 1) 
    {
       $currentpage = 1;
    } 
    //Calculate Offset
    $offset = abs(($currentpage - 1) * $limit);

    $range = 3;
    echo "<p>";
    if($currentpage > 1)
    {
        echo "<a href='{$_SERVER['PHP_SELF']}?currentpage=1'> << </a> ";
        $prevpage = $currentpage - 1;
        echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'> < </a> ";
    }
    //Setting the number of links for pages around the current page
    for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++)
    {
        if (($x > 0) && ($x <= $pages))
        {
            if ($x == $currentpage)
            {
                echo "[<b>$x</b>]";
            }
            else 
            {
                echo "<a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a>";
            }
        }
    }

    if ($currentpage != $pages)
    {
        $nextpage = $currentpage + 1;
        echo "<a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'> > </a>";
        echo "<a href='{$_SERVER['PHP_SELF']}?currentpage=$pages'> >> </a>";
    }
    echo "</p>";
    //Some info to display to the user?
    $start = $offset + 1;
    $end = min(($offset + $limit),$TotalRows);
    echo '<div id="paging"><p>',' 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 ONLY";

    $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 "<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'];
        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>

This is now working and currently shows 15 rows per page. I used that number simply to make it quick. I'll be uping the number of rows before I complete the website.


    CSS Howto..

    Does HTML5 DOCTYPE affect how CSS3 effects are rendered?

    How to set min-height of div to height of viewport?

    How to style a dynamic html created by PHP

    How to create DropDown MenuBar using CSS

    HTML / CSS: How to make the content follow the footer?

    how to make div with image like below in html and css

    How can I make a span stretch the available space between to other spans?

    How do I stop css pushing
  • to the right?
  • Background Image in WebView showing in Simulator, but not on Device

    How can fixed element push content down the page using CSS only?

    How to override a filter:none in CSS

    How to display a div over other content and in the center of webpage using CSS?

    How to move a carousel item to the middle when it's clicked in jquery

    How to set height to wrap text only in certain screen size

    how to number questions using class in CSS

    How to add and remove div's (input)

    How to apply :hover on :after to certain div in CSS

    How to: Illustrator graphic to web animation [closed]

    How to adapt website to user's Screen Resolution?

    How to get a list of valid values for a CSS property with javascript?

    UI Challenge - how to draw “connectors” between elements?

    How to create a speech bubble in css?

    How do I get rid of this blue halo around the close dialog button with jqueryui?

    How do horizontally align text next to an image with CSS

    How to build simple image rotation effect like this one in exapmle?

    How to push a div from the top in percentage with css?

    How to make a div fall on hover and stay at at the bottom of the page using jquery or css?

    How to edit .css file from html via jquery

    Jquery Dropdownlist not shows the selected item.?

    How does google do the barrel roll?