About Part:33 while loop showing records

[ozyedi]

Hi alan, i have a question. You showing your records with while loop. Can we make pagination for this records inside the while loop and how? I'm using your example for listing users in my database records. There are so many users, so need pagination for this. Can you help me about this situation? Thanks already

[ozyedi]

here is my code

<table class="table">
    <thead>
      <tr>
        <th>ID</th>
        <th>Name</th>
        <th>Surname</th>
        <th>Email</th>
        <th>Password</th>
        <th>Date</th>
        <th>Gender</th>
      </tr>
    </thead>
    <tbody>

    <?php
    $q = "SELECT * FROM users ORDER BY uid ASC";
    $r = mysqli_query($dbc,$q);

    while($userlist = mysqli_fetch_assoc($r)){ ?>
      <tr>
          <td><?php echo $userlist['uid']; ?></td>
          <td><?php echo $userlist['name']; ?></td>
          <td><?php echo $userlist['surname']; ?></td>
          <td><?php echo $userlist['email']; ?></td>
          <td><?php echo $userlist['password']; ?></td>
          <td><?php echo $userlist['date']; ?></td>
          <td><?php echo $userlist['gender']; ?></td>
      </tr>
      <?php } ?>     
    </tbody>
  </table>

[creptor]

You need to add parameters to the script, you should, at least, have these: maximum users per page and page number.

Example:

In this example there's a default of 12 items or custom limit (the variable is $_GET['limit']), and a pagination styled with Bootstrap.

//Count the total number of rows in users
$q='SELECT count(*) FROM users';
$r=mysqli_query($dbc,$q);
$n_rows=mysqli_fetch_assoc($r);

//if statements for default values. (finds if there was a custom input or sets the default)

//Using alternative if version.... example: (statements?true:false) (isset($_GET['limit'])&&is_numeric($_GET['limit'])&&$_GET['limit']>0?$showing=(int)$_GET['limit']:$showing=12);

//Returns the total of pages after the limit has been set $n_pages=ceil($n_rows/$showing);

//Sets the current page. (if view is not defined, then it will load the first page) if(isset($_GET['view'])&&$n_pages>1){ $view=pagination((int)$_GET['view'],$showing); }else{ $view=array('offset'=>0,'current'=>1); }

//Fetch data from tables with the restrictions $q='SELECT * FROM users ORDER BY uid ASC LIMIT '.$view['offset'].','.$showing; $r = mysqli_query($dbc,$q); while($data = mysqli_fetch_assoc($r)){ echo '

'.$data['uid'].'

      <td>'.$data['name'].'</td>
      <td>'.$data['surname'].'</td>
      <td>'.$data['email'].'</td>
      <td>'.$data['password'].'</td>
      <td>'.$data['date'].'</td>
      <td>'.$data['gender'].'</td>
  </tr>';

} echo'';

//Echo pagination function if($n_pages>1){ echo pagination_html($view['current'],$n_pages,'?view=',$showing); }

# Important
For this to work you must first  add these functions to the `function.php` file, or load them before running the script above, or it will output an error.

function pagination($page=null,$limit){ if($page==null||!is_numeric($page)){ $page=1; } return array('current'=>(int)$page,'offset'=>$limit*((int)$page-1)); } function pagination_html($current,$last,$href,$limit){ $var_limit=($limit!=null?'&limit='.$limit:null);

$pagination='<nav aria-label="Page navigation"><ul class="pagination">';
//pagination_html($view['current'],$n_pages,'?page=pages&view=',($showing!=12?$showing:null));
if($current!=1){
    $pagination.='<li><a href="'.$href.($current-1).$var_limit.'" aria-label="Previous"><span aria-hidden="true">&laquo;</span></a></li>';
}else{
    $pagination.='<li class="disabled"><a href="#" aria-label="Previous"><span aria-hidden="true">&laquo;</span></a></li>';
}
//middle logic
if(($last-$current)<2||(2-$current)>0){
    if((2-$current)>=0){
        for($a=1;$a<=$last&&$a<=5;$a++){
            $pagination.='<li'.($a==$current?' class="active"':'').'><a href="'.$href.$a.$var_limit.'">'.$a.'</a></li>';
        }
    }else{
        if(($last-$current)<2){
            for($a=$last-4;$a<=$last;$a++){
                ($a<1?$a=1:null);
                $pagination.='<li'.($a==$current?' class="active"':'').'><a href="'.$href.$a.$var_limit.'">'.$a.'</a></li>';
            }
        }
    }
}else{
    for($a=$current-2;$a<$current+2&&$a<=$last;$a++){
        $pagination.='<li'.($a==$current?' class="active"':'').'><a href="'.$href.$a.$var_limit.'">'.$a.'</a></li>';
    }
}

if($current!=$last){
    $pagination.='<li><a href="'.$href.($current+1).$var_limit.'" aria-label="Next"><span aria-hidden="true">&raquo;</span></a></li>';
}else{
    $pagination.='<li class="disabled"><a href="#" aria-label="Next"><span aria-hidden="true">&raquo;</span></a></li>';
}
$pagination.='</ul></nav>';
return $pagination;

}

[creptor]

If you have any questions feel free to ask 😋.

PD: if it doesn't work please tell me, maybe I made a mistake.

[ozyedi]

hi, thnks for reply. But it gives me that error "Fatal error: Unsupported operand types in C:\xampp\htdocs\deneme\admin\admin3d.php on line 59" line 59 is = //Returns the total of pages after the limit has been set $n_pages=ceil($n_rows/$showing);

here is my complete codding;

<?php
function pagination($page=null,$limit){
    if($page==null||!is_numeric($page)){
        $page=1;
    }
    return array('current'=>(int)$page,'offset'=>$limit*((int)$page-1));
}
function pagination_html($current,$last,$href,$limit){
    $var_limit=($limit!=null?'&limit='.$limit:null);

    $pagination='<nav aria-label="Page navigation"><ul class="pagination">';
    //pagination_html($view['current'],$n_pages,'?page=pages&view=',($showing!=12?$showing:null));
    if($current!=1){
        $pagination.='<li><a href="'.$href.($current-1).$var_limit.'" aria-label="Previous"><span aria-hidden="true">&laquo;</span></a></li>';
    }else{
        $pagination.='<li class="disabled"><a href="#" aria-label="Previous"><span aria-hidden="true">&laquo;</span></a></li>';
    }
    //middle logic
    if(($last-$current)<2||(2-$current)>0){
        if((2-$current)>=0){
            for($a=1;$a<=$last&&$a<=5;$a++){
                $pagination.='<li'.($a==$current?' class="active"':'').'><a href="'.$href.$a.$var_limit.'">'.$a.'</a></li>';
            }
        }else{
            if(($last-$current)<2){
                for($a=$last-4;$a<=$last;$a++){
                    ($a<1?$a=1:null);
                    $pagination.='<li'.($a==$current?' class="active"':'').'><a href="'.$href.$a.$var_limit.'">'.$a.'</a></li>';
                }
            }
        }
    }else{
        for($a=$current-2;$a<$current+2&&$a<=$last;$a++){
            $pagination.='<li'.($a==$current?' class="active"':'').'><a href="'.$href.$a.$var_limit.'">'.$a.'</a></li>';
        }
    }

    if($current!=$last){
        $pagination.='<li><a href="'.$href.($current+1).$var_limit.'" aria-label="Next"><span aria-hidden="true">&raquo;</span></a></li>';
    }else{
        $pagination.='<li class="disabled"><a href="#" aria-label="Next"><span aria-hidden="true">&raquo;</span></a></li>';
    }
    $pagination.='</ul></nav>';
    return $pagination;
}

//Count the total number of rows in users
$q='SELECT count(*) FROM users';
$r=mysqli_query($dbc,$q);
$n_rows=mysqli_fetch_assoc($r);

//if statements for default values. (finds if there was a custom input or sets the default)

//Using alternative if version.... example: (statements?true:false)
(isset($_GET['limit'])&&is_numeric($_GET['limit'])&&$_GET['limit']>0?$showing=(int)$_GET['limit']:$showing=12);

//Returns the total of pages after the limit has been set
$n_pages=ceil($n_rows/$showing);

//Sets the current page. (if view is not defined, then it will load the first page)
if(isset($_GET['view'])&&$n_pages>1){
    $view=pagination((int)$_GET['view'],$showing);
}else{
    $view=array('offset'=>0,'current'=>1);
}

//Fetch data from tables with the restrictions
$q='SELECT * FROM users ORDER BY uid ASC LIMIT '.$view['offset'].','.$showing;
$r = mysqli_query($dbc,$q);
while($data = mysqli_fetch_assoc($r)){
    echo '<tr>
          <td>'.$data['uid'].'</td>
          <td>'.$data['name'].'</td>
          <td>'.$data['surname'].'</td>
          <td>'.$data['email'].'</td>
          <td>'.$data['password'].'</td>
          <td>'.$data['date'].'</td>
          <td>'.$data['gender'].'</td>
      </tr>';
}
echo'</tbody></table>';

//Echo pagination function
if($n_pages>1){
    echo pagination_html($view['current'],$n_pages,'?view=',$showing);
}
?>

[creptor]

I made a mistake, please change this:

//Count the total number of rows in users
$q='SELECT count(*) FROM users';
$r=mysqli_query($dbc,$q);
$n_rows=mysqli_fetch_assoc($r);

To this...

//Count the total number of rows in users
$q='SELECT count(*) FROM users';
$r=mysqli_query($dbc,$q);
$db_result=mysqli_fetch_row($r);
$n_rows=$db_result[0];

[ozyedi]

Alan, this is working perfectly. Thnks for this powerful help. But, i have a situation about url's. I'm using index.php?page=7 for show that list. ?page=7 is my admin page. I'm including this with if($_SESSION) with php, connecting database to my pages table. In that page, my URL is index.php?page=7 If i press pagination 2; my url changing like index.php?view=2&limit=12. i need that ?page=7 how can it possible?

Beside, its working in my homepage correctly. my homepage is index.php?page=1 or index.php. if i press pagination 2, my url changing like index.php?view=2&limit=12 and its working.

I see, your codes affecting url's. How can i keep this ?page=(number) before your view=1&limit=12 or view=2&limit=12

Thanks already, and thanks this help

[creptor]

You just need to change the $url parameter on the pagination_html function. Like this:

....
echo pagination_html($view['current'],$n_pages,'?page=[page number here]&view=',$showing);
....

[creptor]

BTW I'm not Alan, just other dude trying to help 😋

[ozyedi]

Yep, its working. Thanks for your attention and help mr. Other dude :)