0

im looking to execute the below code on a single page but with over 1 thousand results it takes a couple minutes to display is there a way to have it load the page then load results from the table say 10 at a time without me having to click anything

$results = mysqli_query($con,"SELECT * FROM email_list");
echo "<table>";
while($row2 = mysqli_fetch_array($results))
{
echo"<tr>";
echo "<td>" .  $row2['email_idno'] . "</td>";
echo "<td>" .  $row2['email_email'] . "</td>";
echo "<td>" .  $row2['email_name'] . "</td>";   
echo"</tr>";            
}
echo "</table>";

after lots of research i think this is possible but cannot work out how to do it any help is very much apreciated

Nathan
  • 509
  • 4
  • 16

2 Answers2

0

There are three things you could do here to make you page load faster.

  1. Easiest solution is work on query optimisation and indexing of database. Google for faster data access from db. It may not always bring faster result. But it is the easier to learn and implement .

  2. Pagination. Load 100 results with limit . you do not need to learn any new language here.

  3. Ajax. This is most apt solution for your needs . But you need to learn ajax. Also learn how to use ajax via jquery. This is time consuming if for a simple time bound task. The procedure is simple .. you load your page. Then a javascript function you have written(imho use jquery) loads and contacts a server php page which returns the result. The result is then displayed on the page via javascript again. Now the complexity increases when you do it 10 at a time.

To learn ajax and jquery ( go to any tutorial website )

These links will help you if you decide to do it with ajax

http://www.w3schools.com/php/php_ajax_database.asp

https://phpseason.wordpress.com/2013/02/15/ajax-add-retrieve-mysql-records-using-jquery-php/

Ajax on interval

How to fire AJAX request Periodically?

Timely interval to retrieve data from db, and stop when the data arrives

Community
  • 1
  • 1
Raj
  • 1,945
  • 20
  • 40
  • in reality i just need it to load the page then start to load the database 1by1 or 10 then the next ten – Nathan Mar 20 '15 at 07:45
  • @Nathan sorry if my answer was not specific. I understood what you required and the way to do that is via ajax(also jquery) my third suggestion. But Like i mentioned it takes time to learn all this. From your question i felt you need it 10 at a time as the page loads slower..so that is why my first suggestion was to try better that. if you are satisfied with the result then why bother learning ajax. if that was not satisfactory .. you could use pagination .. you cannot do what you want to do with just php and sql. you need ajax. you need to learn that or try my first 2 suggestions. – Raj Mar 20 '15 at 10:08
0

Here's a full example:

Caller:

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8">
        <title>title</title>
        <link rel="stylesheet" href="style.css">
        <script src="jquery.js"></script>
    </head>
    <script>
    $(document).ready(function()
    {
        iOffset = 0;
        function callEndpoint( call_url, payload ){
            return $.ajax({
                url: call_url,
                type: 'GET',
                data: payload
            });
        }
        function loadRows() {
            iLimit  = 10;
            oRequest = callEndpoint( '/play/endpoint2.php', { 'offset': iOffset, 'limit': iLimit } );
            oRequest.done(function( sJson ) {
                aRaw = JSON.parse( sJson );
                        console.log( aRaw );
                aData = aRaw.data;
                if( jQuery.isEmptyObject( aData ) )
                {
                    console.log( 'empty!! ' );
                    clearInterval( t );
                }
                else
                {
                    sNewRows = '';
                    for ( var i = 0; i < aData.length; ++i ) {
                        sNewRows += '<tr>';
                        for ( var prop in aData[ i ] ) {
                            if( aData.hasOwnProperty( prop ) ){
                                sNewRows += '<td>' + aData[ i ][ prop ] + '</td>';
                            }
                        }
                        sNewRows += '</tr>';
                    }

                    $( '#loading-data tr:last' ).after( sNewRows );
                    iOffset += 10;
                }
            });
        }
        loadRows();
        var t = setInterval(function(){
            loadRows();
        },2000); // Load more rows every 2 seconds.
    });
    </script>
    <body>
        <table id="loading-data">
            <tbody>
                <thead>
                    <tr>
                        <th>PK</th>
                        <th>co</th>
                        <th>type</th>
                        <th>num</th>
                    </tr>
                </thead>
            </tbody>
        </table>
    </body>
</html>

Endpoint:

class MySql
{
    private $sDbName      = 'play';
    private $sUsername    = 'root';
    private $sPassword    = '';
    private $sHost        = 'localhost';
    private $oConnection  = null;

    public function __construct()
    {
        $this->oConnection = new PDO( 
            'mysql:host=' 
            . $this->sHost 
            . ';dbname=' 
            . $this->sDbName, 
            $this->sUsername, 
            $this->sPassword 
            );
    }
    public function getDb()
    {
        return $this->oConnection;
    }
}
$oMySql = new MySql;
$oDb = $oMySql->getDb();

$aGet = $_GET;
if( !empty( $aGet ) )
{
    $iOffset = $aGet[ 'offset' ];
    $iLimit  = $iOffset + $aGet[ 'limit' ];
}

$sSql = "
    SELECT pk, companyId, type, page_nav
    FROM
    `1`
    WHERE
    pk >= :custom_offset
    AND 
    pk < :custom_limit
    ";
$oStmp = $oDb->prepare( $sSql );

$oStmp->bindValue( ':custom_offset', $iOffset );
$oStmp->bindValue( ':custom_limit', $iLimit );
$oStmp->execute();
$aResults = $oStmp->fetchAll();
// var_dump( $aResults );
$oErrors = $oStmp->errorInfo();
// var_dump( $oErrors );

$aReturn[ 'data' ] = $aResults;
$sJson = json_encode( $aReturn, 1 );
header( 'Content-type', 'application/json' );
echo $sJson;
Vladimir Ramik
  • 1,920
  • 2
  • 13
  • 23