MySQL Query pagination with PHP

Question

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>";
}

?>

score 2 · Accepted Answer · edited May 23 '17 at 10:31

2

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.

edited May 23 '17 at 10:31

Community

1
1

answered Mar 24 '14 at 19:08

Zoltán Tamási

12,249
8
65
93

i'm editing this for like hours and i still can't do this system. I Tried everything from that post and your code! – Tsukki Mar 24 '14 at 23:21
But what's wrong? What exactly is not working? Could you post your error message, or anything? – Zoltán Tamási Mar 25 '14 at 07:44
Nevermind, i was forgetting fo inform my database. But i got a new issue, the pagination is working. You can see on my code there's a filter to search on database and display it. i'm getting this error: `PHP Warning: Division by zero in E:\Domains\site.com\wwwroot\products.php on line 252`, i'll edit and post how the code is right now. `$total = ceil($rows/$limit);` this is the line of the error. I didn't figure it out – Tsukki Mar 25 '14 at 15:06
I see that you set the $limit variable only if there is no filter. Probably you are getting this error when you have any filter applied, because then the undefined variable has its default value in the numeric expression, which is 0. – Zoltán Tamási Mar 25 '14 at 18:32
How i didn't figured it out? Haha i'm feeling dumb. It's all working now, i just set a limit to the filter. There's something else that i can do? – Tsukki Mar 25 '14 at 19:00

MySQL Query pagination with PHP

1 Answers1