使用现有代码，使用MYSQLI / PHP实现PAGINATION

Question

i have existing code, I read a lot of example of using Pagination, but I'm not sure how to implement it in my case.

VIEW.php

<?php

$result = mysqli_query($con,$query);
echo "<div class=\"maincover \" data-role=\"scrollbox\" data-scroll=\"vertical\">";

echo "<div class=\"panel panel-default\">";
while($row = mysqli_fetch_array($result)) {
echo "<div class=\"panel-heading\">";
echo '<div><a class="panel-title btn-block" href="details.php?id='.$row['id'].'"><h3>'.$row['id'].' | '.$row['vidTitle'].'</h3></a></div>';
echo "</div>";

echo "<div class=\"panel-body\">";
echo "<div class=\"imgCover\"><img class=\"imageCover\"src=\"" . $row['url'] . "\"></div>";
echo "<div class=\"vidSD\">" . $row['vidSD'] . "</div>";
echo "<div class=\"vidDetails\"> 

<hr class=\"style-two\">
<table>
<tr><td class=\"vidDetailsTD\"><strong>" . $lang['vtYear'] . "</strong></td><td class=\"vidDetailsTD\">" . $row['vidYear'] ."</td></tr>
<tr><td class=\"vidDetailsTD\"><strong>" . $lang['vtCity'] . "</strong></td><td class=\"vidDetailsTD\">". $row['vidCity'] ."</td></tr>
<tr><td class=\"vidDetailsTD\"><strong>" . $lang['vtGenre'] . "</strong></td><td class=\"vidDetailsTD\">". $row['vidZanr'] ." , ". $row['vidZanr2'] ." , ". $row['vidZanr3'] . "</td></tr>
<tr><td class=\"vidDetailsTD\"><strong>" . $lang['vtQuality'] . "</strong></td><td class=\"vidDetailsTD\">". $row['vidQuality'] ."</td></tr>
<tr><td class=\"vidDetailsTD\"><strong>" . $lang['vtTranslatedBy'] . "</strong></td><td class=\"vidDetailsTD\">". $row['vidTranslated'] ."</td></tr>
<tr><td class=\"vidDetailsTD\"><strong>" . $lang['vtVideoTime'] . "</strong></td><td class=\"vidDetailsTD\">". $row['vidTime'] .  "</td></tr>
</table> 
</div></div>";

echo " <div class=\"panel-footer\">";
echo '<h6><strong>' . $lang['vsdAuthor'] . '</strong><a href="../userPages/user.php?u='.$row['addName'].'">'.$row['addName'].'</a></h6>';
echo '<div><h6><strong>' . $lang['vsdPublished'] . '</strong>' . $row['published'] . '</h6></div>'; 
echo "</div>";
}
echo "</div></div>";

mysqli_close($con);
?>

This code displaying a list of videos, and I'm not sure how to ADD pagination at the bottom of this list, using MY code.

This is what I found:
SampleOne SampleTwo SampleThree

And because I have sorting list, it's even more problematically, HAVE NO IDEA how to do it ((

sortOrder.php

<?php


$query ="SELECT newsvid.id, newsvid.addName, newsvid.vidTitle, newsvid.vidType, newsvid.size, newsvid.url, newsvid.vidSD, newsvid.published, videoinformation.vidLD, videoinformation.vidYear, videoinformation.vidCity, videoinformation.vidZanr, videoinformation.vidZanr2, videoinformation.vidZanr3, videoinformation.vidQuality, videoinformation.vidTranslated, videoinformation.vidTime  FROM newsvid, videoinformation WHERE newsvid.id = videoinformation.id";



// Video type
$vType = isset($_GET['vType']) ? $_GET['vType'] : 'ALL';
$goodTypeParam = array("AnyType", "Film", "Serials", "Cartoon", "Anime");

if (in_array($vType, $goodTypeParam)) {
    if($vType == 'AnyType'){}
    else{$query .= " AND newsvid.vidType ='".$_GET['vType']."'";}
}




//Video Genre one
$vGenre = isset($_GET['vGenre']) ? $_GET['vGenre'] : 'ALL';
$goodGenreParam = array("AnyGenre1", "Action", "Adventure", "Comedy", "Crime", "Faction", "Fantasy", "Historical", "Horror", "Mystery", "Paranoid", "Philosophical", "Political", "Realistic", "Romance", "Saga", "Satire", "Science-Fiction", "Slice-Of-Life", "Speculative", "Anime");

if (in_array($vGenre, $goodGenreParam)) {
    if($vGenre == 'AnyGenre1'){}
    else{$query .= " AND ( videoinformation.vidZanr ='".$_GET['vGenre']."' OR videoinformation.vidZanr2 ='".$_GET['vGenre']."' OR videoinformation.vidZanr3 ='".$_GET['vGenre']."')";}
}

//Video Genre two
$vGenre2 = isset($_GET['vGenre2']) ? $_GET['vGenre2'] : 'ALL';
$goodGenre2Param = array("AnyGenre2", "Action2", "Adventure2", "Comedy2", "Crime2", "Faction2", "Fantasy2", "Historical2", "Horror2", "Mystery2", "Paranoid2", "Philosophical2", "Political2", "Realistic2", "Romance2", "Saga2", "Satire2", "Science-Fiction2", "Slice-Of-Life2", "Speculative2", "Anime2");

if (in_array(vGenre2, $goodGenre2Param)) {
    if(vGenre2 == 'AnyGenre2'){}
    else{$query .= " AND ( videoinformation.vidZanr ='".$_GET['vGenre2']."' OR videoinformation.vidZanr2 ='".$_GET['vGenre2']."' OR videoinformation.vidZanr3 ='".$_GET['vGenre2']."')";}
}

//Video Genre three
$vGenre3 = isset($_GET['vGenre3']) ? $_GET['vGenre3'] : 'ALL';
$goodGenre3Param = array("AnyGenre3", "Action", "Adventure", "Comedy", "Crime", "Faction", "Fantasy", "Historical", "Horror", "Mystery", "Paranoid", "Philosophical", "Political", "Realistic", "Romance", "Saga", "Satire", "Science-Fiction", "Slice-Of-Life", "Speculative", "Anime");

if (in_array($vGenre, $goodGenre3Param)) {
    if($vGenre3 == 'AnyGenre3'){}
    else{$query .= " AND ( videoinformation.vidZanr ='".$_GET['vGenre3']."' OR videoinformation.vidZanr2 ='".$_GET['vGenre3']."' OR videoinformation.vidZanr3 ='".$_GET['vGenre3']."')";}
}





// Video Years
$vYear = isset($_GET['vYear']) ? $_GET['vYear'] : 'ALL';
$goodYearParam = array("AnyYear", "2014", "2013", "2012", "2011", "2010", "2009", "2008", "2007", "2006", "2005", "2004", "2003", "2002", "2001", "2000", "1999", "1998", "1997");

if (in_array($vType, $goodYearParam)) {
    if($vYear == 'AnyYear'){}
    else{$query .= " AND newsvid.vidYear ='".$_GET['vYear']."'";}
}


// Video City
$vCity = isset($_GET['vCity']) ? $_GET['vCity'] : 'ALL';
$goodCityParam = array("AnyCity", "Russian", "England");

if (in_array($vCity, $goodCityParam)) {
    if($vCity == 'AnyCity'){}
    else{$query .= " AND newsvid.vidCity ='".$_GET['vCity']."'";}
}



//NEW of OLD
$order = isset($_GET['order']) ? $_GET['order'] : 'ALL';
$goodParam = array("NEW", "OLD");

if (in_array($order, $goodParam)) {

    if($order == 'NEW'){
     $query .= " ORDER BY newsvid.id ASC"; 
    }else if($order == 'OLD'){
     $query .= " ORDER BY newsvid.id DESC"; 
    }else{
     $query .= " AND videoinformation.vidYear = 2014"; 
        }
}

?>

Answer 1

why is your HTML inside your PHP? if your going the old-fashioned way without using any modern template engine then you should at least not put the HTML inside of your PHP code.

about your question, you should just create a PHP script that will handle an offset/limit parameters. and will adjust the SQL Query.

There's a few steps you need to do in order to accomplish what you want to do.

1. use GET parameter in order to know on which page you are on $page=$_GET['page']
2. use $offset and $limit parameters in order to paginate.
3. $offset = ($page-1) * 10; $limit = 10; // constant limit per page
4. return the COUNT of the total amount of rows from your query with out any LIMIT & OFFSET.
5. use Limit & Offset in the same query and return the rows MySQL lets assume our $offset is 0 and $limit is 10. LIMIT 0,10 / $query .= "LIMIT $offset, $limit";
6. you can now display all of the rows you've got back from the previous query. (10 rows in our example for the first page).
7. lets assume the 1 step return that you have total of 100 rows. now you need to display the pagination in your page your current offset is 0 and the limit per page is 10 (from ourLIMIT 0,10 example). and you need to check the total amount of pages $total_pages = ceil($totalRows / $rows_per_page) = (100/10 = 10).
8. loop on the total_pages and create links

:

for ($page = 1; $page < $total_pages; ++$page) {
     echo "<a href=\"list.php?page={$page}\">{$page}</a>";
}