problem with putting variable sending with submit button into LIMIT part

Question

Niloofar24 15 Posting Whiz

9 Years Ago

hello me again:)
There is a page in my cms that users can see my posts there and i want to limit the number of posts that must be shown to users in each page and paginate.
This is my code:

<?php
$servername = "localhost";
$dbname = "mydbname";
$dbusername = "mydbusername";
$dbpassword = "mydbpassword";
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $dbusername, $dbpassword);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $number = "";

    if(isset($_POST['submit'])) {

        $sql = "SELECT ID, Title, Author, Content FROM Posts LIMIT" . $_POST['number'] . ", 3";
        $result = $conn->query($sql);

        foreach($result as $row) {
            echo $row['ID'] . ") ";
            echo "<a href='single-post-page.php?post=" . $row['ID'] . "'>" . $row['Title'] . "</a>";
            echo "<br>" . " by: " . $row['Author'];
            echo "<br>";
            echo $row['Content'];
            echo "<br><br><br>";
        }
    }

} catch(PDOException $e) {
    echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>

<html>
<head>
<title>User View Page</title>
</head>
<body>
<hr>

<form method="post">
<input type="text" name="number">
<input type="submit" value="Submit">
</form>

</body>
</html>

How the script can take the value i send after clicking the submit button, to LIMIT x, y part?
My code above doesn't work, i face with a page with an input form and a submit button, and when i type a number and click the submit button, nothing happens.

cms php

Edited 9 Years Ago by Niloofar24

2 Contributors
20 Replies
588 Views
3 Days Discussion Span
Latest Post 9 Years Ago Latest Post by Niloofar24

cereal 1,524 Nearly a Senior Poster

9 Years Ago

My fault, sorry: you have to pass an integer to the bind method, from the form it receives a string and so the query will output badly, something like:

LIMIT "1', 3'"

I was testing from command line. Add the filter_var() and it will work properly:

$number = trim($_POST['number']);
$number = filter_var($number, FILTER_VALIDATE_INT);

$stmt = $conn->prepare("SELECT ID, Title, Author, Content FROM Posts LIMIT :limit, 3");
$stmt->bindParam(':limit', $number, PDO::PARAM_INT);

In addition, with the filter_var() function you can define a range of valid values, for example:

$data = array(
    'options' => array(
        'default'   => 1,
        'min_range' => 1,
        'max_range' => 100
       )
);

$number = filter_var($number, FILTER_VALIDATE_INT, $data);

Last, add the name attribute to your submit button, otherwise this statement if(isset($_POST['submit'])) will fail:

<input type="submit" name="submit" value="Submit">

Edited 9 Years Ago by cereal

cereal 1,524 Nearly a Senior Poster

9 Years Ago

The trim() function is used to remove leading and trailing spaces, for example:

$a = ' 1';
var_dump($a);

Outputs: string(2) " 1", instead:

$b = trim($a);
var_dump($b);

Outputs: string(1) "1"

As you see, it removes the extra space, but it returns always a string, not an integer type, to get an integer you can do:

$c = (int)$b;
$d = intval($b);

But you still need to validate the input, so in this case you can use filter_var() where:

the first argument is the string;
the second argument is a constant to define the type of validation or sanitazation;
the third argument in this case is an array with a default value, in case the user submits something else, like a negative number or word values, and it is useful if you want to stop at a defined value.

So:

$e = filter_var($b, FILTER_VALIDATE_INT);

In all these cases ($c, $d and $e) var_dump will return:

int(1)

Not anymore string. Some information here:

I want each page to show only 3 posts to the user, so posts 1-2-3 go into page 1, and posts 4-5-6 go into page 2, and posts 7-8-9 go into page 3.

Now, what you're searching for is pagination. In practice you have to define how many posts you want to display for each page, in your case 3, so we need to define the boundaries, first page is 1, last page is the total divided by the number of posts per page, in pseudo code:

max = floor(total / per_page)

So, basically all you do is:

# get total posts
$total   = $conn->query("SELECT COUNT(ID) as rows FROM Posts")
           ->fetch(PDO::FETCH_OBJ);

# define total pages
$perpage = 3;
$posts   = $total->rows;
$pages   = floor($posts / $perpage);

# get current page, define 1 as default value
$get_page = isset($_GET['page']) ? $_GET['page'] : 1;

$data = array(
    'options' => array(
        'default'   => 1,
        'min_range' => 1,
        'max_range' => $pages
       )
);

$number = trim($get_page);
$number = filter_var($number, FILTER_VALIDATE_INT, $data);

# set current range
$range  = $perpage * $number;
$prev   = $number - 1;
$next   = $number + 1;

# query current range
$stmt   = $conn->prepare("SELECT ID, Title, Author, Content FROM Posts LIMIT :limit, :perpage");
$stmt->bindParam(':limit', $range, PDO::PARAM_INT);
$stmt->bindParam(':perpage', $perpage, PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetchAll();

foreach($result as $key => $row) {

And in your HTML you write the navigation code:

<div class="navigation">
<?php

    if(count($result) > 0)
    {
        # first page
        if($number <= 1)
            echo "<span>prev</span> | <a href=\"?page=$next\">next</a>";

        # last page
        elseif($number >= $pages)
            echo "<a href=\"?page=$prev\">prev</a> | <span>next</span>";

        # in range
        else
            echo "<a href=\"?page=$prev\">prev</a> | <a href=\"?page=$next\">next</a>";
    }

    else
        echo "No results found.";

?>
</div>

Now it should work fine, if it doesn't please post the updated code and let us know the issue. For some additional information about pagination read this:

http://www.xarg.org/2011/10/optimized-pagination-using-mysql/

Bye!

Edited 9 Years Ago by cereal

cereal 1,524 Nearly a Senior Poster

9 Years Ago

Whoops! I'm sorry, I did a mistake when I defined the default values, change lines from 11 to 19 with this:

$get_pages = isset($_GET['page']) ? $_GET['page'] : 0;

$data = array(

    'options' => array(
        'default'   => 0,
        'min_range' => 0,
        'max_range' => $pages
       )
);

And in the HTML part change this condition:

if($number <= 1)

To:

if($number <= 0)

So, when ?page= is not defined or is 0 it will return 0,3 i.e. the first three posts, page=1 will return 3,3, page=2 will return 6,3 and so on... I think it should work fine now.

Going to your questions:

What does floor do here?

$pages = floor($posts / $perpage);

Floor is a function to round fractions down, if you do 15 / 2 you get 7.5, by using floor you get 7, you can decide to round always up by using ceil() or to depend on the value with round() ( by default this function rounds down when the decimal is .4 or lower, with .5+ rounds up), for example:

$n = 15 / 2;
echo floor($n); # 7
echo ceil($n);  # 8
echo round($n); # 8

In this case, if we round up, the last page will be empty.

Docs:

What does this line say?

This line is an IF statement, defined with the ternary operator ?::

$get_page = isset($_GET['page']) ? $_GET['page'] : 0;

It is a short form for this:

$get_page = 0;

if(isset($_GET['page']))
{
    $get_page = $_GET['page'];
}

In the script this statement is used:

to initialize the variable $get_page that will be used in the validation process;
ensure that the query is performed even if $_GET['page'] is not defined.

There is an alternative function you could use here: filter_input(), I choosed filter_var() because right after initializing $get_page I use trim to remove possible extra spaces, by using the alternative approach you cannot trim the variable before the validation is performed, so it will fail directly to the default value. For your information, the code would look like this:

$number = filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, $data);

Docs:

I didn't understand this part well.

$data = array(
    'options' => array(
        'default'   => 0,
        'min_range' => 0,
        'max_range' => $pages
    )
);

This is the array used by filter_var(), open this link:

http://php.net/manual/en/filter.filters.validate.php

You will see a table that defines the constants to use in the filter_var() function, in this case we are using FILTER_VALIDATE_INT, the options column defines what you can pass as third argument of the function: default, min_range, max_range.

The full picture looks like this:

$number = $_GET['num'];

$data = array(
    'options' => array(
        'default'   => 0,
        'min_range' => 0,
        'max_range' => 100
    )
);

$number = filter_var($number, FILTER_VALIDATE_INT, $data);

If the user submits:

?num=abc
?num=-1
?num=101

$number will be always 0, while these will work fine:

?num=0
?num=1
?num=2
...
?num=98
?num=99
?num=100

Hope it is understandable (& correct enough) :D

cereal 1,524 Nearly a Senior Poster

9 Years Ago

Hmm, no I don't think so. I wrote a test script, check if it works for you:

<?php

    $servername = "localhost";
    $dbname     = "mydbname";
    $dbusername = "mydbusername";
    $dbpassword = "mydbpassword";
    $error      = FALSE;
    $result     = FALSE;

    try {
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $dbusername, $dbpassword);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $total  = $conn->query("SELECT COUNT(id) as rows FROM Posts")
                  ->fetch(PDO::FETCH_OBJ);

        $perpage = 3;
        $posts   = $total->rows;
        $pages   = floor($posts / $perpage);

        # default
        $get_pages = isset($_GET['page']) ? $_GET['page'] : 0;

        $data = array(

            'options' => array(
                'default'   => 0,
                'min_range' => 0,
                'max_range' => $pages
                )
        );

        $number = trim($get_pages);
        $number = filter_var($number, FILTER_VALIDATE_INT, $data);
        $range  = $perpage * $number;

        $prev = $number - 1;
        $next = $number + 1;

        $stmt = $conn->prepare("SELECT ID, Author, Content FROM Posts LIMIT :limit, :perpage");
        $stmt->bindParam(':perpage', $perpage, PDO::PARAM_INT);
        $stmt->bindParam(':limit', $range, PDO::PARAM_INT);
        $stmt->execute();

        $result = $stmt->fetchAll();

    } catch(PDOException $e) {
        $error = $e->getMessage();
    }

    $conn = null;
?>
<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>User View Page</title>
        <style type="text/css">

            body {
                font:1rem Arial,sans-serif;
                color:#1a1a1a;
            }

            a {
                text-decoration:none;
                color:#4281A4;
                transition: .3s color;
            }

            a:hover {
                color: #314CB6;
            }

            .error {
                width:100%;
                padding:.5em;
                background-color: #D7F75B;
            }

            .navigation span,
            .navigation a {
                display: inline-block;
                padding:0.5rem;
            }

            #wrap {
                margin:50px auto;
                width: 960px;
            }

            table {
                width:100%;
                border-collapse:collapse;
            }

            th {
                text-align:left;
            }

            tbody > tr:nth-child(odd) {
                background:#f3faf1;
            }

            tbody > tr:nth-child(even) {
                border-top: 1px solid #e5e5e5;
                border-bottom: 1px solid #e5e5e5;
            }

            td:first-child {
                width:25px;
            }

            td:nth-child(2) {
                width:10%;
            }

            thead th,
            tbody td {
                padding:.5rem;
                line-height:1.4rem;
            }

        </style>
    </head>
    <body>

        <div id="wrap">

            <?php
            if($error)
            {
                echo "<div class=\"error\"><strong>Database Error:</strong> $error</div>";
            }
            ?>

            <div class="navigation">
            <?php

                if($result && count($result) > 0)
                {
                    echo "<h3>Total pages ($pages)</h3>";

                    # first page
                    if($number <= 0)
                        echo "<span>&laquo; prev</span> | <a href=\"?page=$next\">next &raquo;</a>";

                    # last page
                    elseif($number >= $pages)
                        echo "<a href=\"?page=$prev\">&laquo; prev</a> | <span>next &raquo;</span>";

                    # in range
                    else
                        echo "<a href=\"?page=$prev\">&laquo; prev</a> | <a href=\"?page=$next\">next &raquo;</a>";
                }

                else
                {
                    echo "<p>No results found.</p>";
                }

            ?>
            </div>

            <?php

                if($result && count($result) > 0)
                {
                    echo '
                    <div class="posts">
                        <h3>Posts</h3>
                        <table>
                            <thead>
                                <tr>
                                    <th>ID
                                    <th>Author
                                    <th>Content
                            <tbody>
                    ';
                        foreach($result as $key => $row)
                        {
                            echo "
                                <tr>
                                    <td>$row[ID]
                                    <td>$row[Author]
                                    <td>$row[Content]

                            ";
                        }


                    echo '
                        </table>
                    </div>
                    ';
                }

            ?>
        </div>

    </body>
</html>

Just change the database credentials and it should work. See if this helps you, otherwise paste your last version here.

Edited 9 Years Ago by cereal

cereal 1,524 Nearly a Senior Poster

9 Years Ago

Why you set $error = FALSE; and $result = FALSE; to False?

This is done to initialize the variables in my test script, otherwise if the database fails for some reason (no connection, query error) then PHP will show this notice:

Severity: Notice
Message: Undefined variable: result

by simply setting the variable $result = FALSE; PHP will run the code into the IF statements and will find other variables not initialized like $pages, $number, $next and $prev, also the loop at line 181 will fail. To avoid most of these errors I could do:

$pages  = FALSE;
$number = FALSE;
$next   = FALSE;
$prev   = FALSE;

But the loop warning will still remain:

Invalid argument supplied for foreach()

Why? Because of this:

$result = FALSE;
var_dump(count($result)); # int(1)

It returns 1, so the IF statement:

if(count($result) > 0)

Will fail, and it is not fixable by changing the condition to 1:

if(count($result) > 1)

because the query could return only one post. So to avoid the warning all you have to do is to verify if the variable is not FALSE. In my test script my check is implicit, this:

if($result)

Is equal to write:

if($result !== FALSE)

Note if you write:

if($result === TRUE)

The warning will disappear in case of database fails, but when the query works well it will not show any results because the types are different: $result will be an array and TRUE is boolean. A workaround is to expressively avoid the strict comparison (===) and make the PHP engine judge if the condition is verified:

if($result == TRUE)

Which is pratically identical to:

if($result)
if($result != FALSE)
if($result !== FALSE)

I prefer to define the conditions and make clear when I'm using a strict comparison. This is why I'm checking both the type and the number of rows:

if($result && count($result) > 0)

Is this something that you should apply to your code? In my opinion: yes. I didn't suggested in my previouses posts because I wasn't testing and I didn't wanted to go too specific.

Docs:

About $error

You changed this part:

} catch(PDOException $e) {
    echo $sql . "<br>" . $e->getMessage();
}

to this:

} catch(PDOException $e) {
    $error = $e->getMessage();
}

Was the first model incorrect?! Should i change this part of every php files in to the second model?!

There is nothing wrong in your approach, but $sql is not defined anywhere in my test script and the error will be printed before the HTML, for example:

Database Error: SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: YES)
<!DOCTYPE html>
<html>
    <head>

In my test script I moved the contents to the DOM and made an IF statement to show the error only when occurs a database fail:

if($error)
{
    echo "<div class=\"error\"><strong>Database Error:</strong> $error</div>";
}

To test it just change the username or the password of your database credentials.

Now, in the development stage it's ok to show specific errors in the page, but when you go in production you should save these messages to the PHP system log and send out simple error messages, to avoid users to understand what happened. You can use the error_log() function, so your catch statement would change to:

} catch(PDOException $e) {
    error_log($e->getMessage(), 0);
    $error = 'Whoops! Something really bad occured!'
}

Docs: http://php.net/manual/en/function.error-log.php

But what was the problem in my code that it didn't work? I compared your last code with the last one you gave and i tried and didn't work, but didn't find the difference that caused th problem. DO you know what was that problem?

Could you share your updated code? Last version I saw was before applying pagination. Here you can find a live example, just click Run:

http://runnable.com/VSpZzVic6P4hfniR/php-pdo-pagination-example-for-mysql

You can also fork the example and test your code, in that case the database table will be created by the setup.php script, the link will be visible when an error occurs, naturally this was created only for this example.

By the way, thank you for the endorsment, it was appreciated! ^_^

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

cereal 1,524 Nearly a Senior Poster Featured Poster · Answer 1 · 2015-04-10T08:40:54+00:00

Hi,

add a space after LIMIT, at the moment when executed it will print LIMIT20, 3 instead of LIMIT 20, 3. But validate and sanitize the number. For example:

$number = trim($_POST['number']);
$number = filter_var($number, FILTER_VALIDATE_INT, $options);

$sql = "SELECT ID, Title, Author, Content FROM Posts LIMIT $number, 3";

Otherwise use prepared statements, for example:

$stmt = $conn->prepare("SELECT ID, Title, Author, Content FROM Posts LIMIT :limit, 3");
$stmt->bindParam(':limit', trim($_POST['number']), PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetchAll();

foreach($result as $key => $row) {

Docs: http://php.net/manual/en/pdostatement.bindparam.php

Niloofar24 15 Posting Whiz · Answer 2 · 2015-04-10T15:22:29+00:00

I tried them @cereal, but they didn't work.

cereal 1,524 Nearly a Senior Poster Featured Poster · Answer 3 · 2015-04-10T15:39:31+00:00

Ok, it works fine for me. Can you show your current code?

Niloofar24 15 Posting Whiz · Answer 4 · 2015-04-10T15:58:47+00:00

Yes, please look at this, maybe this is my mistake somewhere:

<?php
$servername = "localhost";
$dbname = "mydbname";
$dbusername = "mydbusername";
$dbpassword = "mydbpassword";
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $dbusername, $dbpassword);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    if(isset($_POST['submit'])) {

        $stmt = $conn->prepare("SELECT ID, Title, Author, Content FROM Posts LIMIT :limit, 3");
        $stmt->bindParam(':limit', trim($_POST['number']), PDO::PARAM_INT);
        $stmt->execute();
        $result = $stmt->fetchAll();

        foreach($result as $key => $row) {
            echo $row['ID'] . ") ";
            echo "<a href='single-post-page.php?post=" . $row['ID'] . "'>" . $row['Title'] . "</a>";
            echo "<br>" . " by: " . $row['Author'];
            echo "<br>";
            echo $row['Content'];
            echo "<br><br><br>";
        }
    }

} catch(PDOException $e) {
    echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>


<html>
<head>
<title>User View Page</title>
</head>
<body>
<hr>

<form method="post">
<input type="text" name="number">
<input type="submit" value="Submit">
</form>

</body>
</html>

Niloofar24 15 Posting Whiz · Answer 5 · 2015-04-10T17:37:33+00:00

Yes it works, thank you @cereal.
Would you please explain these lines for me?

    //What does trim exactly do?
    $number = trim($_POST['number']);

    //What does this line say?
    $number = filter_var($number, FILTER_VALIDATE_INT);

And i didn't understand this part exactly:

    $data = array(
    'options' => array(
    'default' => 1,
    'min_range' => 1,
    'max_range' => 100
    )
    );
    $number = filter_var($number, FILTER_VALIDATE_INT, $data);

Niloofar24 15 Posting Whiz · Answer 6 · 2015-04-10T17:50:23+00:00

I want each page to show only 3 posts to the user, so posts 1-2-3 go into page 1, and posts 4-5-6 go into page 2, and posts 7-8-9 go into page 3.
For now i can enter the x value that choose the first post that must be shown on the page, by typing the number on the input box and then with clicking on the submit button, the value will be sent to the script.

But i want to change it.
I want to set 2 buttons; the previous button and the next button.
I want when i click on the next button, the script add 3 numbers into x value, and the first post that must be shown on the page start from that post.... if i click one more time, add 3 numbers to the x value again and .....
And when click the previous button, reduce x value 3 numbers and move back to show the post....
I'm not sure if i have explained what i want clear or not, sorry!
Now need your guidance, thank you.

Niloofar24 15 Posting Whiz · Answer 7 · 2015-04-11T12:34:46+00:00

Thank you @cereal for your clear explanation and for the links.

Yes it works fine, thank you again. But there is a little problem i tried to fix but i couldn't find the exact part needs to be changed.
the first page contains posts 4-5-6, and the second page contains posts 7-8-9. So where is the page for posts 1-2-3?
Please help me first with just pointing to the part i should change because i want to do it myself to find if i have understood the code you gave me or not, thank you.

Niloofar24 15 Posting Whiz · Answer 8 · 2015-04-11T14:35:47+00:00

I think the problem must be in these lines:
here:

$range = $perpage * $number;

here:

$stmt = $conn->prepare("SELECT ID, Title, Author, Content FROM Posts LIMIT :limit, :perpage");

And here:

$stmt->bindParam(':limit', $range, PDO::PARAM_INT);

$perpage is 3 wich means how many posts should be echo on the page, correct?
$number is the number of the page we are in to read it's posts, correct?
And the limit value that it's duty is setting the start post, will be filled with $range value.

$range = $perpage * $number;

3 = 3 x 1

So here if we are in the first page, the $range value will be set to 3, and then with this part (':limit', $range, the limit value will be set to 3 wich means the script should starting to echo posts from post number 4, correct?!

So how can i solve it?

Niloofar24 15 Posting Whiz · Answer 9 · 2015-04-11T14:42:38+00:00

Would you please explain this line for me?

//What does floor do here?
$pages = floor($posts / $perpage);


# get current page, define 1 as default value
// What does this line say?
$get_page = isset($_GET['page']) ? $_GET['page'] : 1;

// I didn't understand this part well.
$data = array(
    'options' => array(
        'default' => 1,
        'min_range' => 1,
        'max_range' => $pages
    )
);

Thank you.

Niloofar24 15 Posting Whiz · Answer 10 · 2015-04-11T18:55:18+00:00

Thank you @cereal, were good and very clear explanations.

I changed the code. There is still a problem. Now the only page i can see is page one with posts 1-2-3. When i click on the Next button, nothing happens and it won't go to the second page.
Does it back to these 2 parts?
$get_page = isset($_GET['page']) ? $_GET['page'] : 0;
and
'default' => 0,

When i changed them from 0 to 1 to test, then the only page i could access was the second page with posts 4-5-6 in it.

Niloofar24 15 Posting Whiz · Answer 11 · 2015-04-12T00:08:08+00:00

Yesss :) it work nice thank you again @cereal!
But what was the problem in my code that it didn't work? I compared your last code with the last one you gave and i tried and didn't work, but didn't find the difference that caused th problem.
DO you know what was that problem?

Niloofar24 15 Posting Whiz · Answer 12 · 2015-04-12T00:18:27+00:00

Why you set $error = FALSE; and $result = FALSE; to False?

You changed this part:

} catch(PDOException $e) {
    echo $sql . "<br>" . $e->getMessage();
}

to this:

} catch(PDOException $e) {
    $error = $e->getMessage();
}

Was the first model incorrect?! Should i change this part of every php files in to the second model?!

What does this if statment checke?

 if($error)
{
echo "<div class=\"error\"><strong>Database Error:</strong> $error</div>";
}

And would you please explain this too?

 if($result && count($result) > 0)
{

Why both result and count(result)?

Thank you, you helped me alot specialy with your clear explanation.
Good luck my friend.

Niloofar24 15 Posting Whiz · Answer 13 · 2015-04-12T18:57:39+00:00

Thank you @cereal for clear explanation.
Well, i have deleted that file unfortunatelly.

Now this is my last update for now: (ofcourse i have not changed the design of the page for now, but will redesign it later after i finished editing the php part of the page.)

Please look at it's php part and see how it is? I think it's not good or standard because of 2 more php part i have added to it, is it?

<?php

    $servername = "localhost";
    $dbname = "mydbname";
    $dbusername = "mydbusername";
    $dbpassword = "mydbpassword";
    $error = FALSE;
    $result = FALSE;

    try {
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $dbusername, $dbpassword);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $total = $conn->query("SELECT COUNT(id) as rows FROM Posts")
                 ->fetch(PDO::FETCH_OBJ);

        $perpage = 3;
        $posts = $total->rows;
        $pages = floor($posts / $perpage);

        # default
        $get_pages = isset($_GET['page']) ? $_GET['page'] : 0;

        $data = array(

            'options' => array(
                'default' => 0,
                'min_range' => 0,
                'max_range' => $pages
                )
        );

        $number = trim($get_pages);
        $number = filter_var($number, FILTER_VALIDATE_INT, $data);
        $range = $perpage * $number;

        $prev = $number - 1;
        $next = $number + 1;

        $stmt = $conn->prepare("SELECT ID, Title, Author, Content FROM Posts LIMIT :limit, :perpage");
        $stmt->bindParam(':perpage', $perpage, PDO::PARAM_INT);
        $stmt->bindParam(':limit', $range, PDO::PARAM_INT);
        $stmt->execute();

        $result = $stmt->fetchAll();

    } catch(PDOException $e) {
        $error = $e->getMessage();
    }

    $conn = null;



if(isset($_POST['submit-register'])) {
$servername = "localhost";
$dbname = "blenderl_CMS";
$dbusername = "blenderl_nilofar";
$dbpassword = "andishe#n";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $dbusername, $dbpassword);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $username = $_POST['username'];
    $password = $_POST['password'];
    $password = md5($password);

    $sql = "INSERT INTO Users (Username, Password)
    VALUES ('$username', '$password')";

    $conn->exec($sql);
    }
catch(PDOException $e)
    {
    echo $sql . "<br>" . $e->getMessage();
    }

$conn = null;
}




if(isset($_POST['submit-login'])) {
    if(isset($_POST['username'])){
    $servername = "localhost";
    $dbname = "blenderl_CMS";
    $dbusername = "blenderl_nilofar";
    $dbpassword = "andishe#n";
    try {
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $dbusername, $dbpassword);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $username = $_POST['username'];
        $password = $_POST['password'];
        $password = md5($password);

        $sql = "SELECT Password FROM Users WHERE Username = '$username'";
        $result = $conn->query($sql);

        if ($result->rowCount() > 0){
            foreach($result as $row) {
                if ($password == $row['Password']){
                    //echo "Password correct!";
                    header("Location: admin.php");
                }else{
                    header("Location: user-view-page.php");
                }
            }
        }

    } catch(PDOException $e) {
        echo $sql . "<br>" . $e->getMessage();
    }
    $conn = null;
}
}
?>

<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>User View Page</title>
        <style type="text/css">

            body {
                font:1rem Arial,sans-serif;
                color:#1a1a1a;
            }

            a {
                text-decoration:none;
                color:#4281A4;
                transition: .3s color;
            }

            a:hover {
                color: #314CB6;
            }

            .error {
                width:100%;
                padding:.5em;
                background-color: #D7F75B;
            }

            .navigation span,
            .navigation a {
                display: inline-block;
                padding:0.5rem;
            }

            #wrap {
                margin:50px auto;
                width: 960px;
            }

            table {
                width:100%;
                border-collapse:collapse;
            }

            th {
                text-align:left;
            }

            tbody > tr:nth-child(odd) {
                background:#f3faf1;
            }

            tbody > tr:nth-child(even) {
                border-top: 1px solid #e5e5e5;
                border-bottom: 1px solid #e5e5e5;
            }

            td:first-child {
                width:25px;
            }

            td:nth-child(2) {
                width:10%;
            }

            thead th,
            tbody td {
                padding:.5rem;
                line-height:1.4rem;
            }

            .top_nav {
                background:#f3faf1;
            }

            #register_message {
            position: absolute;
            z-index:5;
            margin-left:20px;
            margin-top:-16px;
            }

            #login_message {
            z-index:4;
            margin-left:860px;
        margin-top:-15px;
            }

            #register {
            position: absolute;
            z-index:2;
            margin-left:20px;
            }

            #login {
            margin-left:860px;
            }

            h4 {
            margin-left:10px;
            }

        </style>
    </head>
    <body>

        <div class="top_nav">
            <div id="register_message">
            <h3>Register if you are new user!</h3>
            </div>

            <div id="login_message">
            <h3>Login if you have registered before!</h3>
            </div>

            <form method="post" id="register">
        Your Username: <input type="text" name="username"><br>
        Your Password: <input type="text" name="password"><br>
        <input type="submit" name="submit-register" value="Register">
        </form>

        <form method="post" id="login">
        Username: <input type="text" name="username"><br>
        Password : <input type="text" name="password"><br>
        <input type="submit" name="submit-login" value="Log in">
        </form>

        </div>


        <div id="wrap">

            <?php
            if($error)
            {
                echo "<div class=\"error\"><strong>Database Error:</strong> $error</div>";
            }
            ?>

            <?php

                if($result && count($result) > 0)
                {
                    echo '
                    <div class="posts">
                        <h3>Posts</h3>
                        <table>
                    <thead>
                        <tr>
                            <th>ID
                        <th>Title
                        <th>Author
                        <th>Content
                    <tbody>
            ';
                    foreach($result as $key => $row)
                    {
                        echo "
                        <tr>
                            <td>$row[ID]
                            <td>$row[Title]
                        <td>$row[Author]
                        <td>$row[Content]
                        ";
                    } 


                    echo '
                    </table>
                </div>
                ';
                }

            ?>

            <div class="navigation">
            <?php

                if($result && count($result) > 0)
                {
                    echo "<h4>Total pages ($pages)</h4>";

                    # first page
                    if($number <= 0)
                        echo "<span>&laquo; prev</span> | <a href=\"?page=$next\">next &raquo;</a>";

                    # last page
                    elseif($number >= $pages)
                        echo "<a href=\"?page=$prev\">&laquo; prev</a> | <span>next &raquo;</span>";

                    # in range
                    else
                        echo "<a href=\"?page=$prev\">&laquo; prev</a> | <a href=\"?page=$next\">next &raquo;</a>";
                }

                else
                {
                    echo "<p>No results found.</p>";
                }

            ?>
            </div>

        </div>

    </body>
</html>

And something more...
When we click the next button for the last time, we will face with a blanck page with message "No resulf found.". I want to add a prev button below the message but when i typed this:

        else
        {
            echo "<p>No results found.</p>";
            echo "<a href=\"?page=$prev\">&laquo; prev</a>
        }

it didn't work and i faced with a complete white page.
Without putting prev button there, we have to use back button of the browser menu to get back to pervious page. What is your suggestion?

By the way, thank you for the endorsment, it was appreciated! ^_^

Your welcome:)

cereal 1,524 Nearly a Senior Poster Featured Poster · Answer 14 · 2015-04-13T00:09:49+00:00

Ok, I got it, your code is perfect, the issue is in my logic.

By starting everything by zero I wasn't considering that when the $pages variable was divisible by $perpage (e.g. 100 / 10) the result would be 10, but the last range would be LIMIT 90, 10 at page 9, the issue happens because there is an extra query that sets the range LIMIT 100, 10 at page 10 and because the ELSEIF stop condition was current_page >= total_pages, which at the end translates to 10 >= 10 when, by starting from zero, it should have been 10 >= 9.

I was tricked by the $perpage set at three: the last page is 33 which generates LIMIT 99, 3 and returns only the last row which, in appearance, is correct.

It can be fixed to continue the script starting at zero, but at this point I think it is a better to start everything from one. I updated your last code, you can try it here:

http://runnable.com/VSro1Svxgzwv3kMy/php-pdo-pagination-example-2-for-mysql

And here's your code (updated):

<?php

    $servername = "localhost";
    $dbname     = "test";
    $dbusername = "root";
    $dbpassword = "";
    $error      = FALSE;
    $result     = FALSE;

    try {
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $dbusername, $dbpassword);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $total = $conn->query("SELECT COUNT(id) as rows FROM Posts")
                 ->fetch(PDO::FETCH_OBJ);

        $perpage = 3;
        $posts   = $total->rows;
        $pages   = ceil($posts / $perpage);

        # default
        $get_pages = isset($_GET['page']) ? $_GET['page'] : 1;

        $data = array(

            'options' => array(
                'default'   => 1,
                'min_range' => 1,
                'max_range' => $pages
                )
        );

        $number = trim($get_pages);
        $number = filter_var($number, FILTER_VALIDATE_INT, $data);
        $range  = $perpage * ($number - 1);

        $prev = $number - 1;
        $next = $number + 1;

        $stmt = $conn->prepare("SELECT ID, Title, Author, Content FROM Posts LIMIT :limit, :perpage");
        $stmt->bindParam(':perpage', $perpage, PDO::PARAM_INT);
        $stmt->bindParam(':limit', $range, PDO::PARAM_INT);
        $stmt->execute();

        $result = $stmt->fetchAll();

    } catch(PDOException $e) {
        $error = $e->getMessage();
    }

    $conn = null;



if(isset($_POST['submit-register'])) {

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $dbusername, $dbpassword);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $username = $_POST['username'];
    $password = $_POST['password'];
    $password = md5($password);

    $sql = "INSERT INTO Users (Username, Password)
    VALUES ('$username', '$password')";

    $conn->exec($sql);
    }
catch(PDOException $e)
    {
    echo $sql . "<br>" . $e->getMessage();
    }

$conn = null;
}

if(isset($_POST['submit-login'])) {
    if(isset($_POST['username'])){

    try {
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $dbusername, $dbpassword);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $username = $_POST['username'];
        $password = $_POST['password'];
        $password = md5($password);

        $sql = "SELECT Password FROM Users WHERE Username = '$username'";
        $result = $conn->query($sql);

        if ($result->rowCount() > 0){
            foreach($result as $row) {
                if ($password == $row['Password']){
                    //echo "Password correct!";
                    header("Location: admin.php");
                }else{
                    header("Location: user-view-page.php");
                }
            }
        }

    } catch(PDOException $e) {
        echo $sql . "<br>" . $e->getMessage();
    }
    $conn = null;
}
}
?>

<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>User View Page</title>
        <style type="text/css">

            body {
                font:1rem Arial,sans-serif;
                color:#1a1a1a;
            }

            a {
                text-decoration:none;
                color:#4281A4;
                transition: .3s color;
            }

            a:hover {
                color: #314CB6;
            }

            .error {
                width:100%;
                padding:.5em;
                background-color: #D7F75B;
            }

            .navigation span,
            .navigation a {
                display: inline-block;
                padding:0.5rem;
            }

            #wrap {
                margin:50px auto;
                width: 960px;
            }

            table {
                width:100%;
                border-collapse:collapse;
            }

            th {
                text-align:left;
            }

            tbody > tr:nth-child(odd) {
                background:#f3faf1;
            }

            tbody > tr:nth-child(even) {
                border-top: 1px solid #e5e5e5;
                border-bottom: 1px solid #e5e5e5;
            }

            td:first-child {
                width:25px;
            }

            td:nth-child(3) {
                width:10%;
            }

            thead th,
            tbody td {
                padding:.5rem;
                line-height:1.4rem;
            }

            .top_nav {
                background:#f3faf1;
            }

            #register_message {
            position: absolute;
            z-index:5;
            margin-left:20px;
            margin-top:-16px;
            }

            #login_message {
            z-index:4;
            margin-left:860px;
            margin-top:-15px;
            }

            #register {
            position: absolute;
            z-index:2;
            margin-left:20px;
            }

            #login {
            margin-left:860px;
            }

            h4 {
            margin-left:10px;
            }

        </style>
    </head>
    <body>

        <div class="top_nav">
            <div id="register_message">
            <h3>Register if you are new user!</h3>
            </div>

            <div id="login_message">
            <h3>Login if you have registered before!</h3>
            </div>

            <form method="post" id="register">
        Your Username: <input type="text" name="username"><br>
        Your Password: <input type="text" name="password"><br>
        <input type="submit" name="submit-register" value="Register">
        </form>

        <form method="post" id="login">
        Username: <input type="text" name="username"><br>
        Password : <input type="text" name="password"><br>
        <input type="submit" name="submit-login" value="Log in">
        </form>

        </div>


        <div id="wrap">

            <?php
            if($error)
            {
                echo "<div class=\"error\"><strong>Database Error:</strong> $error</div>";
            }
            ?>

            <?php

                if($result && count($result) > 0)
                {
                    echo '
                    <div class="posts">
                        <h3>Posts</h3>
                        <table>
                    <thead>
                        <tr>
                            <th>ID
                        <th>Title
                        <th>Author
                        <th>Content
                    <tbody>
            ';
                    foreach($result as $key => $row)
                    {
                        echo "
                        <tr>
                            <td>$row[ID]
                            <td>$row[Title]
                        <td>$row[Author]
                        <td>$row[Content]
                        ";
                    } 


                    echo '
                    </table>
                </div>
                ';
                }

            ?>

            <div class="navigation">
            <?php

                if($result && count($result) > 0)
                {
                    echo "<h4>Total pages ($pages)</h4>";

                    # first page
                    if($number <= 1)
                        echo "<span>&laquo; prev</span> | <a href=\"?page=$next\">next &raquo;</a>";

                    # last page
                    elseif($number >= $pages)
                        echo "<a href=\"?page=$prev\">&laquo; prev</a> | <span>next &raquo;</span>";

                    # in range
                    else
                        echo "<a href=\"?page=$prev\">&laquo; prev</a> | <a href=\"?page=$next\">next &raquo;</a>";
                }

                else
                {
                    echo "<p>No results found.</p>";
                }

            ?>
            </div>

        </div>

    </body>
</html>

From the above I've only removed the extra database credentials, since these where set in the first lines of the script and for your privacy, because is tested on runnable.com.

For completeness here is the explanation of the changes:

To start, instead of floor() we will use ceil() to increment by one, when the result of the division is float:

$pages   = ceil($posts / $perpage);

Then we switch back the default values to one, instead of zero:

$get_pages = isset($_GET['page']) ? $_GET['page'] : 1;

$data = array(

    'options' => array(
        'default'    => 1,
        'min_range'  => 1,
        'max_range'  => $pages
        )
);

And fix the limit:

$range  = $perpage * ($number - 1);

Previously this was $range = $perpage * $number ;. This is where actually the count will continue to start from zero.

The last fix is for the IF statement:

# first page
if($number <= 1)

And this should fix everything (hopefully)!

Niloofar24 15 Posting Whiz · Answer 15 · 2015-04-13T18:00:31+00:00

Yes it work ok now. Thank you @cereal for clear explanation and for updating my code:) I understood it well.