Member Avatar for BlueCharge

Hello everyone.

I currently have a while loop that pulls all members to a page if the username begins with the selected letter. We have a lot of members now and the "usernames beginning with "a" are quite alot. So i need to be able to have a way to LIMIT to about 20 usernames then add a "new page" at the bottom. e.g i want like this at the bottom:

Page: 1 - 2 - 3 - 4 - 5 - 6, etc

And these are linked to the same page so the php code can list the next 20 members...

Here is my code at the minute and thank you very much in advance:

<?php
$letter = $_GET['sort'];
if(!isset($letter)) {
$letter = "a";
}
$lettercombo = "$letter%";
$select = mysql_query("SELECT * FROM users WHERE username LIKE '$lettercombo' ORDER by username");
while($fetch = mysql_fetch_array($select)) {
?>
  
  <tr>
   <td><a style="color:#5b9600; font-weight:bold; text-shadow:0px 1px 0px #fff;" href="viewuser.php?user=<? echo $fetch['username']; ?>"><? echo $fetch['username']; ?></a></td>
   <td><? echo $fetch['gamertag']; ?></td>
   <td><? echo $fetch['joined']; ?></td>
  </tr>
 
<? } ?>
Edited by BlueCharge because: n/a
  • 4 Contributors
  • 3 Replies
  • 201 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by Member #334542

Dani AI

Generated

This thread needs simple, robust pagination rather than loading every matching user. ’s loop will become slow as the table grows. ’s "load into an array" idea uses a lot of memory for large result sets. was on the right track but used incorrect LIMIT syntax and didn’t show input sanitization. A safer approach: 1) sanitize the sort letter, 2) COUNT matching rows to compute total pages, 3) SELECT only the needed columns for the current page using LIMIT/OFFSET, and 4) render page links that preserve sort.

Example (PDO, safe for large tables):

<?php
// assume $pdo is a valid PDO connection
$letter = isset($_GET['sort']) ? substr($_GET['sort'], 0, 1) : 'a';
$letter = preg_replace('/[^A-Za-z0-9]/', '', $letter);

$perPage = 20;
$page = max(1, (int)($_GET['page'] ?? 1));
$offset = ($page - 1) * $perPage;

// total matching rows
$stmt = $pdo->prepare('SELECT COUNT(*) FROM users WHERE username LIKE :like');
$stmt->execute([':like' => $letter . '%']);
$total = (int)$stmt->fetchColumn();
$pages = max(1, (int)ceil($total / $perPage));

// fetch page (bind LIMIT parameters as integers)
$stmt = $pdo->prepare('SELECT username, gamertag, joined FROM users WHERE username LIKE :like ORDER BY username LIMIT ?, ?');
$stmt->bindValue(1, $offset, PDO::PARAM_INT);
$stmt->bindValue(2, $perPage, PDO::PARAM_INT);
$stmt->execute([$letter . '%']);

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // output row HTML (username, gamertag, joined)
}
?>

Simple page-link generation:

for ($p = 1; $p <= $pages; $p++) {
    if ($p === $page) echo "<strong>$p</strong> ";
    else echo '<a href="?sort='.urlencode($letter).'&page='.$p.'">'.$p.'</a> ';
}

Notes and troubleshooting: use SELECT username, ... instead of SELECT *; add an index on the username column to speed LIKE 'a%' queries; avoid leading wildcards (eg. '%a') which disable index use; cache the COUNT if necessary for massive tables; and always sanitize sort and page inputs to prevent injection or malformed queries. The correct LIMIT forms are LIMIT offset, count or LIMIT count OFFSET offset — not the pattern shown in earlier replies.

Recommended Answers

All 3 Replies

Member Avatar for anony

why dont you retrieve the contents of the while loop into an array and check if its greater than the limit 20.

Edited by anony because: n/a
Member Avatar for Karthik.gautham

use queries like this
$res="mysql_query("SELECT * FROM users WHERE username LIKE '$lettercombo' ORDER by username");
$num=mysql_num_rows($res);
$limit=0;
$select = mysql_query("SELECT * FROM users WHERE username LIKE '$lettercombo' ORDER by username limit $limit-20");
while($fetch = mysql_fetch_array($select)) {

?>

<tr>
<td><a style="color:#5b9600; font-weight:bold; text-shadow:0px 1px 0px fff;" href="viewuser.php?user=<? echo $fetch; ?>"><? echo $fetch; ?></a></td>
<td><? echo $fetch; ?></td>

<td><? echo $fetch; ?></td>

</tr>


<? $limit+=20} ?>

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.