php records navigation

Question

<?php 
$host='localhost'; 
$user='root'; 
$password='root'; 
$database='database'; 

$startindex=@$_REQUEST['seek']; 

$db=mysql_connect($host, $user, $password) 
or die ("Impossibile connettersi al server $host"); 

mysql_select_db($database, $db) 
or die ("Impossibile connettersi al database $database");



$query="SELECT * FROM ordini_master"; 
$dbResult=mysql_query($query, $db); 
$AffectedRows=mysql_affected_rows($db); 

mysql_data_seek($dbResult, $startindex); 

$row=mysql_fetch_row($dbResult); 

foreach($row as $k=>$v) 
{ 
    $myfield=mysql_fetch_field($dbResult, $k); 
    print($myfield->name . " : $v <br/>"); 
} 

mysql_free_result($dbResult); 
mysql_close($db); 

print("<br/>Seleziona il record<br/>"); 

for($index=0; $index<$AffectedRows; $index++) 
{ 
    print("<a href=\"{$_SERVER['PHP_SELF']}?seek=$index\" >" . 
    ($index+1) . "</a> "); 
} 
?>

This code allow the navigation between a query records, so it create a page foreach record in database and so shows one record time. How can i modify that code to paging every 10 records? So i want to show 10 records time and create a page for the next.

Sorry for my english (I'm italian) , i hope you can help me.

You should use the COUNT command of SQL and divide the result from the records you need. — iWumbo, Feb 01 '15 at 17:32
If you're just learning PHP, please, do not learn the obsolete `mysql_query` interface. It's awful and is being removed in future versions of PHP. A modern replacement like [PDO is not hard to learn](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/). A guide like [PHP The Right Way](http://www.phptherightway.com/) can help explain best practices. Always be absolutely **sure** your user parameters are [properly escaped](http://bobby-tables.com/php) or you will have severe [SQL injection bugs](http://bobby-tables.com/). — tadman, Feb 01 '15 at 17:47

stefan · Answer 1 · 2015-02-01T19:55:23.837

Use LIMIT to get as many records as you wish.
Example:

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

Here is your modified code. This should work for your.
I haven't tested it but give it a try:

<?php 
$host='localhost'; 
$user='root'; 
$password='root'; 
$database='database'; 

$startindex=(isset($_REQUEST['seek']) ? $_REQUEST['seek'] : 0); 

$db=mysql_connect($host, $user, $password) 
or die ("Impossibile connettersi al server $host"); 

mysql_select_db($database, $db) 
or die ("Impossibile connettersi al database $database");

$queryCnt = "SELECT count(*) as cnt FROM ordini_master"; 
$CntRow=mysql_fetch_row(mysql_query($query, $db));
$CntData = $CntRow[0];

$step = 10;
$query="SELECT * FROM ordini_master LIMIT $startindex, $step"; 
$result=mysql_query($query, $db); 

while ($row = mysql_fetch_assoc($result)) {
    foreach($row as $k=>$v) { 
        $myfield=mysql_fetch_field($result, $k); 
        print($myfield->name . " : $v <br/>"); 
    } 
} 

mysql_free_result($result); 
mysql_close($db); 

print("<br/>Seleziona il record<br/>"); 

for($index=0; $index<$CntData; $index=$index+$step) { 
    print("<a href=\"{$_SERVER['PHP_SELF']}?seek=$index\" >" . 
    ($index+1) . "</a> "); 
}

Use the information from tadman too.
It's very important to do something to prevent SQL injection and it's also necessary to use PDO or the mysqli extension because the mysql extension will not longer be supported.

@LuigiDaddario I have edited my answer. Now `$v` will be used. But I haven't tested the code. Please adjust it as you need it. — stefan, Feb 01 '15 at 19:57
but the problem is that it doesn't show the paging, but i if to change the seek directly from the url (e.g. http://myurl.com/hello?seek=10) it change the records. — Luigi Daddario, Feb 01 '15 at 20:24
@LuigiDaddario What gives `$CntData` back if you make `echo $CntData`? — stefan, Feb 02 '15 at 19:23

score 0 · Answer 2 · edited May 23 '17 at 11:43

What you need first is the LIMIT statement from mysql. MySql states:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

As for how to implement it in your code I could not have written a better answer as the one found here.

Junius L · Answer 3 · 2015-02-01T21:34:40.837

You can use COUNT to display your records and create pages

   function getLimitData($start,$limit){
    //getting all items
    $db = new PDO('mysql:host=localhost; dbname=data','root','');
    $results = $db->query("SELECT * FROM table ORDER BY ID DESC limit $start,$limit");
    $results = $results->fetchAll(PDO::FETCH_ASSOC);
    return $results;    
}
 //database connection
$db = new PDO('mysql:host=localhost; dbname=data','root','');
//pagination pages
  $nav_counter = basename($_SERVER['SCRIPT_FILENAME']);
  $page_counter = $nav_counter;
  $nav_counter = rtrim($nav_counter, ".php");
  $cPage = $page_counter + 1;

  //creating next pages
  $first_next = $nav_counter + 1 ;
  $sec_next = $first_next + 1 ;
  $third_next = $sec_next + 1 ;
  $fourth_next = $third_next + 1 ;
  $fifth_next = $fourth_next + 1 ;
  $sixth_next = $fifth_next + 1 ;
  $seventh_next = $sixth_next + 1 ;
  $next_page = $seventh_next + 1;

  //creating previous pages
  $first_prev = $nav_counter - 1 ;
  $sec_prev = $nav_counter - 2 ;
  $third_prev = $nav_counter - 3 ;
  $fourth_prev = $nav_counter - 4 ;
  $fifth_prev = $nav_counter - 5 ;
  $sixth_prev = $nav_counter - 6 ;
  $seventh_prev = $nav_counter - 7 ;

  //row count
  $tableExists = $db->tableExist();
  $ROW_COUNT = $db->getRowCount();

  $numRows= 9; //number of items to be displayed
  //last page
  //last page
  $last_page = ($ROW_COUNT / $numRows) - 1;
    if(!is_int($last_page)){
    $last_page = (int)$last_page + 1;
  }

  $pageNate = '';
    if($ROW_COUNT <= $numRows){
        $pageNate = 'class="hide"';
    }else{
        $pageNate = 'class="exist"';
  }

  //displaying torrents
  $start = 0;
  $limit = $numRows;
  if ($page_counter !== 'index.php') {

   $start = ($limit * $nav_counter);
   }
  //getting number of rows left in the table
  $rows_left = $db->getLimitData($start, $limit);

     $number_rows = 0;
     foreach ($rows_left as $r) {
        $number_rows = $number_rows + 1;
     }
    if ($number_rows < $numRows) {
    $limit = $number_rows;
    }

    $items = $db->getLimitData($start, $limit);
   ?>

displaying item

         <ol>
        <?php foreach($items as $item) : ?>
          <li><php echo $item['Value']; ?></li>
        <?php endforeach; ?>
        </ol>

pagination code

<?php 
  $pages = array();
   for ($counter = 1; $counter <= $last_page; $counter++) { 
    $pages[] = $counter;
 }
  //storing pages in array and creating a page if it doesn't exist
    foreach ($pages as $key) {
      $page = $key.'.php';
       //if page doesn't exists create page
        if(file_exists($page)== false && $key <= $last_page){
       copy('index.php', $page);
      }
   }
 ?>




        <p class="pagenav" >
            <a href="index.php" <?php if ($page_counter == 'index.php') {echo 'class="hide"';} ?>>&lt;&lt;</a>
                    <a href="<?php if ($page_counter == '1.php') {echo 'index.php';}else{echo "$first_prev".".php";}  ?>" <?php if ($page_counter == 'index.php') {echo 'class="hide"';} ?>>&lt;</a>
            <a href="<?php echo "$seventh_prev".".php"; ?>" <?php if($seventh_prev <= 0){echo 'class="hide"';} ?>><?php echo $seventh_prev;?></a>
            <a href="<?php echo "$sixth_prev".".php"; ?>" <?php if($sixth_prev <= 0){echo 'class="hide"';} ?>><?php echo $sixth_prev;?></a>
            <a href="<?php echo "$fifth_prev".".php"; ?>" <?php if($fifth_prev <= 0){echo 'class="hide"';} ?>><?php echo $fifth_prev;?></a>
            <a href="<?php echo "$fourth_prev".".php"; ?>" <?php if($fourth_prev <= 0){echo 'class="hide"';} ?>><?php echo $fourth_prev;?></a>
            <a href="<?php echo "$third_prev".".php"; ?>" <?php if($third_prev <= 0){echo 'class="hide"';} ?>><?php echo $third_prev;?></a>
            <a href="<?php echo "$sec_prev".".php"; ?>" <?php if($sec_prev <= 0){echo 'class="hide"';} ?>><?php echo $sec_prev;?></a>
            <a href="<?php echo "$first_prev".".php"; ?>" <?php if($first_prev <= 0 ){echo 'class="hide"';} ?>><?php echo $first_prev;?></a>
                    <a <?php if ($page_counter == 'index.php') {echo 'class="hide"';}else{ echo 'id="here"';} ?>><?php echo $nav_counter; ?></a>
            <a href="<?php echo $first_next.'.php'; ?>" <?php if($first_next <= $last_page){echo 'class="exist"';}else{echo 'class="hide"';} ?>><?php echo $first_next;?></a>
                    <a href="<?php echo "$sec_next".".php"; ?>" <?php if($sec_next <= $last_page){echo 'class="exist"';}else{echo 'class="hide"';} ?>><?php echo $sec_next;?></a>
                    <a href="<?php echo "$third_next".".php"; ?>" <?php if($third_next <= $last_page){echo 'class="exist"';}else{echo 'class="hide"';} ?>><?php echo $third_next;?></a>
                    <a href="<?php echo "$fourth_next".".php"; ?>" <?php if($fourth_next <= $last_page){echo 'class="exist"';}else{echo 'class="hide"';} ?>><?php echo $fourth_next;?></a>
                    <a href="<?php echo "$fifth_next".".php"; ?>" <?php if($fifth_next <= $last_page){echo 'class="exist"';}else{echo 'class="hide"';} ?>><?php echo $fifth_next;?></a>
                    <a href="<?php echo "$sixth_next".".php"; ?>" <?php if($sixth_next <= $last_page){echo 'class="exist"';}else{echo 'class="hide"';} ?>><?php echo $sixth_next;?></a>
                    <a href="<?php echo "$seventh_next".".php"; ?>" <?php if($seventh_next <= $last_page){echo 'class="exist"';}else{echo 'class="hide"';} ?>><?php echo $seventh_next;?></a>
            <a href="<?php echo "$first_next".".php"; ?>" <?php if($first_next <= $last_page){echo 'class="exist"';}else{echo 'class="hide"';} ?>>&gt;</a>
                    <a href="<?php echo $last_page.'.php'; ?>" <?php if($nav_counter == $last_page){echo 'class="hide"';}else{echo 'class="exist"';} ?>>&gt;&gt;</a>
        </p>

enter image description here

php records navigation

3 Answers3