使用php mysql面临分页困难

Question

I am showing results arranged in table from mysql database using php, but I am not being able to find a way to paginate my results, because of the data being arranged in table and because some of the rows are missing and as webpage will be dynamic so user will be able to delete rows and insert new rows again and again, so because of that I can't get my results paginated using auto_increment value as well.

Following is my script:

<?php
    $mysql_session = mysql_query("SELECT * FROM session");
    $no_of_rows_session = mysql_num_rows($mysql_session);
    $msg_session='';
    $last_row = mysql_query("SELECT * from session ORDER BY id DESC LIMIT 1");
    $last_id = '';

    while($select_id_of_last_row = mysql_fetch_array($last_row)) {
        $last_id = $select_id_of_last_row['id'];
    }
?>



<?php echo $msg_session; ?>
<?php
    if($no_of_rows_session < 1) {
        $msg_session = 'No Session has been added yet';
    } else {
        for($i = 1; $i<=$last_id; $i++) {                   
            ${'mysql_session_every_single_query_' . $i} = mysql_query("SELECT * FROM session WHERE id LIKE '%$i%'");
            ${'mysql_existance_of_session_id_' . $i} = mysql_num_rows(${'mysql_session_every_single_query_' . $i});
            while(${'mysql_every_session_data_' . $i} = mysql_fetch_array(${'mysql_session_every_single_query_' . $i})){
                ${'id_session_' . $i} = ${'mysql_every_session_data_' . $i}['id'];
                ${'session_start_' . $i} = ${'mysql_every_session_data_' . $i}['start'];
                ${'session_end_' . $i} = ${'mysql_every_session_data_' . $i}['end'];
                echo "<table>";
                    echo "<tr id='tr_session_hover_" . $i . "'
                                class='tr_session_hover' 
                                onClick=\"document.location='session_edit.php?ss=${'session_start_' . $i}&se=${'session_end_' . $i}';\">
                            <td>" . ${'session_start_' . $i} . "-" . ${'session_end_' . $i} . "</td>
                        </tr>";
                echo "</table>";
            }
        }

    }     
?>

Answer 1

I have got a solution. If you are also facing such kind of problem and want to proceed with variables only (like me):

suppose you need to show 6 results on one page and your auto_increment (suppose a column named id is the auto_increment one, in your table) values are't regular, then what you can do is, get id of the 6th element by

I am supposing that you have already connected to phpmyadmin and selected a database

$no_of_results_per_page = 6;

if(isset($_GET['page_no'])) {
  $page_no = $_GET['page_no']; 
} else{
  $page_no = 1;

}


  $upper_limit = ($page_no - 1)*$no_of_result_per_page - 1;

if($page_no > 1){
  $mysql_getting_id_le = mysql_query("SELECT id FROM table LIMIT $upper_limit, 1");

  while($getting_id_last_elem = mysql_fetch_array($mysql_getting_id_le)) {
    $id_last_element = $getting_id_last_elem['id'];
  }
} else {
  $mysql_getting_id_le = mysql_query("SELECT id FROM table LIMIT 0, 1");

  while($getting_id_last_elem_p_1 = mysql_fetch_array($mysql_getting_id_le)) {
    $id_last_element = $getting_id_last_elem_p_1['id'];
$id_last_element = $id_last_element - 1;
  }
}

The above query will help us in getting id of the last element of the page

Now we will be creating the buttons to navigate from one page to another.

$final_mysql_query = mysql_query("SELECT * FROM table WHERE id > '%id_last_element%' LIMIT $no_of_results_per_page");

//Now, we can get all data limited to show no. of results you have defined per page using a loop

$total_results = mysql_query("SELECT * FROM table");

$total_results_count = mysql_num_rows($total_results);

$total_pages = ceil($total_results_count/$no_of_results_per_page);

$page_no = '';

for($i = 1; $i <= $total_pages; $i++) {
  $page_no .= "<a href='?page_no=" . $i . "'>" . $i . "</a>";
}