Efficient way of paging querys PHP

Question

I've got this really small table I need to run showing some data about registered users(Name, Username).

$query = mysql_query("SELECT * FROM users ORDER BY Username ASC") or die(mysql_error());
                        while($user = mysql_fetch_assoc($query))
                        {
                            echo '<tr><td>' . $user['Username'] . '</td>';
                            echo '<td>' . $user['FullName'] . '</td>';
                            echo "<td><a href=\"deleteUser.php?id=" . $user['ID'] . "\">Delete</a><br /><a href=\"editUser.php?id=". $user['ID'] . "\">Edit</a></td></tr>";

                        }

Anyway, let's say I'll have 50+ users, the all page will be flooded with users.

So I thought of a solution, for every 15 rows pulled up from the database they will have their own "page" (using $_GET). A friend of mine told me it is something called paging or pagination I don't really remember. I did find some solutions online but none helped me, some looked so long and I just looked out for another tutorial.

Is there an efficient way of doing this without getting into so much stuff around it?

So how can I paginate(right way of saying this ?) my querys? — zarko
– zarko, Commented Dec 8, 2013 at 18:41

Keeper · Accepted Answer · 2013-12-08 19:07:53Z

1

It's called pagination: you can use it in MySQL with the LIMIT statement.

$page = (int) $_GET['page'];
$limit = 15;
$offset = $page * $limit;

$query = mysql_query("SELECT * FROM users ORDER BY Username ASC LIMIT $offset,$limit");

Also you need to calculate the total number of pages using the same query without LIMIT:

$query = mysql_query("SELECT count(1) AS total FROM users");
$total = mysql_fetch_assoc($query)['total'];

Then you can calculate how many pages you have:

$pages = range(0, ceil($total/$limit));
foreach($pages as $p)
    echo '<a href="?page=' . $p . '">' . $p . '</a>';

Obviously you should adapt this code to your own needs.

edited Dec 8, 2013 at 19:07

answered Dec 8, 2013 at 18:41

Keeper

3,5661 gold badge20 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

zarko Over a year ago

It's working perfectly, now to make a next and previous buttons I only have to: $next = $_GET['page'] + 1; echo '<a href="users.php?page='.$next.'">Next</a>'

Keeper Over a year ago

Yes, I've also added a more complete example with every page available.

Collectives™ on Stack Overflow

Efficient way of paging querys PHP

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related