how to add pagination in database table ?

Question

pagination php my sql

Rajanikant Hawaldar · Answer

Hi Dip, Try below two example, First one: html > head > title > Paging Using PHP title > head > body > php $ dbhost = 'localhost:3036' ; $ dbuser = 'root' ; $ dbpass = 'rootpassword' ; $ rec_limit = 10 ; $ conn = mysql_connect ($dbhost, $dbuser, $dbpass); if(! $conn ) { die('Could not connect: ' . mysql_error()); } mysql_select_db('test_db'); /* Get total number of records */ $ sql = "SELECT count(emp_id) FROM employee " ; $ retval = mysql_query ( $sql, $conn ); if(! $retval ) { die('Could not get data: ' . mysql_error()); } $ row = mysql_fetch_array ($retval, MYSQL_NUM ); $ rec_count = $row[0]; if( isset($_GET{'page'} ) ) { $ page = $_GET{'page'} + 1; $ offset = $rec_limit * $page ; }else { $ page = 0 ; $ offset = 0 ; } $ left_rec = $rec_count - ($page * $rec_limit); $ sql = "SELECT emp_id, emp_name, emp_salary " . "FROM employee ". "LIMIT $offset, $rec_limit"; $ retval = mysql_query ( $sql, $conn ); if(! $retval ) { die('Could not get data: ' . mysql_error()); } while($ row = mysql_fetch_array ($retval, MYSQL_ASSOC)) { echo "EMP ID :{$row['emp_id']} br > ". "EMP NAME : {$row['emp_name']} br > ". EMP SALARY : {$row['emp_salary']} br > ". "-------------------------------- br > "; } if( $page > 0 ) { $ last = $page - 2; echo " a href = "$_PHP_SELF? page = $last" > Last 10 Records a > |"; echo " a href = "$_PHP_SELF? page = $page" > Next 10 Records a > "; }else if( $ page == 0 ) { echo " a href = "$_PHP_SELF? page = $page" > Next 10 Records a > "; }else if( $left_rec $rec_limit ) { $ last = $page - 2; echo " a href = "$_PHP_SELF? page = $last" > Last 10 Records a > "; } mysql_close($conn); ?> body > html > Second one: "bootstrap3.css" rel= "stylesheet" > $dbhost = 'localhost' ; $dbuser = 'root' ; $dbpass = "; $conn = mysql_connect( $dbhost , $dbuser , $dbpass ) or die ( 'Error connecting to mysql' ); $dbname = 'test' ; $conn = mysqli_connect( $dbhost , $username , $password , $dbname ) or die ( "Connection failed: " . mysqli_connect_error()); /* check connection */ if (mysqli_connect_errno()) { printf( "Connect failed: %s
" , mysqli_connect_error()); exit (); } $limit = 2; if (isset( $_GET [ "page" ])) { $page = $_GET [ "page" ]; } else { $page =1; }; $start_from = ( $page -1) * $limit ; $sql = "SELECT * FROM posts ORDER BY title ASC LIMIT $start_from, $limit" ; $rs_result = mysqli_query( $conn , $sql ); ?> class = "table table-bordered table-striped" > title body while ( $row = mysql_fetch_assoc( $rs_result )) { ?> echo $row [ "title" ]; ?> echo $row [ "body" ]; ?> }; ?> $sql = "SELECT COUNT(id) FROM posts" ; $rs_result = mysqli_query( $conn , $sql ); $row = mysql_fetch_row( $rs_result ); $total_records = $row [0]; $total_pages = ceil ( $total_records / $limit ); $pagLink = " " ; for ( $i =1; $i $total_pages ; $i ++) { $pagLink .= " . $i . "'>" . $i . " " ; }; echo $pagLink . " " ; ?>

Sonu Gupta · Answer

Hi, Go to below link - https://codingsight.com/implementing-paging-using-offset-fetch-next-in-sql-server/

how to add pagination in database table ?

Answers (2)