CODEWITHSUNDEEP
phpmysqlpagination
Tsukki
Tsukki

Reputation: 33

MySQL Query pagination with PHP

How can i add a pagination system to this simple item display? And how i can add pagination for the results from filter? I just get lost that part and i cant figure it out! I want to apply CSS on the menu too!

Here's the code:

<?php
                include('db.php');
                if(isset($_POST['filter']))
                {
                    $filter = $_POST['filter'];
                    $result = mysql_query("SELECT * FROM products where Product like '%$filter%' or Description like '%$filter%' or Category like '%$filter%'");
                }
                else
                {
                    $result = mysql_query("SELECT * FROM products");
                }
                while($row=mysql_fetch_assoc($result))
                {
                    echo '<li class="portfolio-item2" data-id="id-0" data-type="cat-item-4">';
                    echo '<div>
                    <span class="image-block">
                    <a class="example-image-link" href="reservation/img/products/'.$row['imgUrl'].'" data-lightbox="example-set" title="'.$row['Product'].'""><img width="225" height="140" src="reservation/img/products/'.$row['imgUrl'].'" alt="'.$row['Product'].'" title="'.$row['Product'].'" />                    
                    </a>
                    </span>
                    <div class="home-portfolio-text">
                    <h2 class="post-title-portfolio"><font color="#666666">'.$row['Product'].'</font></h2>
                    <p class="post-subtitle-portfolio"><font color="#666666">Descrição: '.$row['Description'].'
                    <p class="post-subtitle-portfolio"><font color="#666666">Categoria: '.$row['Category'].'
                    <p class="post-subtitle-portfolio">Código: '.$row['Price'].'</p><br/></font></p>
                    </div>
                    </div>';
                    echo '</li>';
                }
                ?>

EDITED:

<?php
                include('db.php');
                if(isset($_POST['filter']))
                {
                    $filter = $_POST['filter'];
                    $result = mysql_query("SELECT * FROM products where Product like '%$filter%' or Description like '%$filter%' or Category like '%$filter%'");
                }
                else
                {
$start=0;
$limit=6;

if(isset($_GET['id']))
{
    $id = $_GET['id'];
    $start = ($id-1)*$limit;
}

$result = mysql_query("SELECT * FROM products LIMIT $start, $limit");
                }
while($row = mysql_fetch_array($result))
{
    echo '<li class="portfolio-item2" data-id="id-0" data-type="cat-item-4">';
                    echo '<div>
                    <span class="image-block">
                    <a class="example-image-link" href="reservation/img/products/'.$row['imgUrl'].'" data-lightbox="example-set" title="'.$row['Product'].'""><img width="225" height="140" src="reservation/img/products/'.$row['imgUrl'].'" alt="'.$row['Product'].'" title="'.$row['Product'].'" />                    
                    </a>
                    </span>
                    <div class="home-portfolio-text">
                    <h2 class="post-title-portfolio"><font color="#666666">'.$row['Product'].'</font></h2>
                    <p class="post-subtitle-portfolio"><font color="#666666">Descrição: '.$row['Description'].'
                    <p class="post-subtitle-portfolio"><font color="#666666">Categoria: '.$row['Category'].'
                    <p class="post-subtitle-portfolio">Código: '.$row['Price'].'</p><br/></font></p>
                    </div>
                    </div>';
                    echo '</li>';
}
echo "</ul>";


$rows = mysql_num_rows(mysql_query("SELECT * FROM products"));
$total = ceil($rows/$limit);

if($id>1)
{
    echo "<center><a href='?id=".($id-1)."' class='button'>Anterior</a></center>";
}
if($id!=$total)
{
    echo "<center><a href='?id=".($id+1)."' class='button'>Próximo</a></center>";
}

?>

Upvotes: 1

Views: 1526

Answers (1)

Zolt&#225;n Tam&#225;si
Zolt&#225;n Tam&#225;si

Reputation: 12754

You should have something like this:

// declare a base query
$q = "SELECT * FROM products";
if(isset($_POST['filter']))
{
    $filter = $_POST['filter'];
    // append filter to query
    $q += "where Product like '%$filter%' or Description like '%$filter%' or Category like '%$filter%'");
}
// check for "page" URL parameter, if not available, go to first page
$page = isset($_GET['page']) ? $_GET['page'] : 1;
// check for "pageSize" URL parameter, if not available, fall back to 20
$pageSize = isset($_GET['pageSize']) ? $_GET['pageSize'] : 20;
// append the pagination to your query
$q += sprintf("LIMIT %d,%d;", ($page-1)*$pageSize, $pageSize);
// execute the constructed query
$result = mysql_query($q);

Note that the code is "pseudoish", not tested, but should give you the base idea.

Also, you can check this SO post about pagination with MySQL.

UPDATE

In PHP if you use an uninitialized variable, then it will have a context-dependent default value. See the documentation about this here. Here is an extract:

It is not necessary to initialize variables in PHP however it is a very good practice. Uninitialized variables have a default value of their type depending on the context in which they are used - booleans default to FALSE, integers and floats default to zero, strings (e.g. used in echo) are set as an empty string and arrays become to an empty array.

Upvotes: 2

Related Questions