1 Topic by towelie (edited by towelie 2007-05-22 23:43)

  • Registered: 2007-04-15
  • Posts: 96

Topic: pagination

Okay I want to have a new page for every x number of results that are queried in a table. The page can be seen here.

Here is how I coded the thing

<?php
// Perform the query
 $query = 'SELECT id, username, iwins, ilosses, ipoints FROM xusers' . ' ORDER by ipoints DESC' ;
 $result = mysql_query($query) or die('Query failed: ' . mysql_error());
    
    $id = '<a href="profile.php?id='.$line['id'].'">'.pun_htmlspecialchars($line['username']).'</a>' ;

if ($db->num_rows($result))
{
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
     echo "\t<tr>\n";


            
        
?>

    <td class="tcl" scope="col"><?php echo '<a href="iprofile.php?id='.$line['id'].'">'.pun_htmlspecialchars($line['username']).'</a>' ?></td>
    <td class="tc2" scope="col"><?php echo $line['iwins']; ?></td>
    <td class="tc3" scope="col"><?php echo $line['ilosses']; ?></td>
    <td class="tc4" scope="col"><?php echo $line['ipoints']; ?></td>        

<?php
          }
    echo "\t</tr>\n";
}
echo "</table>\n";

?>

*note if you are looking through the features on the site you will notice that the ladder is incomplete, specifically the iprofile pages which I am currently working on aswell and fixing up db for it.

edited: hid some of query

  • quaker
  • quaker
  • Senior Member
  • Offline
  • From: USA
  • Registered: 2006-02-22
  • Posts: 1,783

Re: pagination

on a side note.. put a index or a default.html or a php file that redirects you to any location on ur site in the main folder.



Q

My stuff or my style might sux, but atleast I'm willing to help when I can.
Don't be stupid and help ! We are the stupid one's !!!

quaker's Website

  • Registered: 2007-04-15
  • Posts: 96

Re: pagination

Not sure exactly what you mean. I don't know if this is it but, you actually can see certain links like iladder there when you log in.

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: pagination

He means here:
http://thpsvids.com/
and here
http://thpsvids.com/users/

5 Reply by towelie (edited by towelie 2007-05-23 01:41)

  • Registered: 2007-04-15
  • Posts: 96

Re: pagination

Well there is a reason I am not doing that. It is not my website to do that with. Once I finish coding this site  I plan on getting a new url.

Any advice on the whole paginate thing? I think I need to add a LIST x,#  clause to the query but I don't know where to go from there.

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: pagination

LIMIT #, x
where x is the number of entries per page and # is the entry to start at, either calculated from a page number (so ($_GET['page'] - 1) * 50) or just in terms of the starting entry #

7 Reply by towelie (edited by towelie 2007-05-23 00:31)

  • Registered: 2007-04-15
  • Posts: 96

Re: pagination

oops I meant to say limit heh..
Are you sure it wouldnt be LIMIT x, #
or will it make a difference

also I would need to assign a variable to your little $_GET(...) global there wouldnt I?

Can I see an example of how you would write this query?

LIMIT 15,X WHERE X = $X

$X = $_GET( blah blah )? ? ?

hmm confused

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: pagination

towelie wrote:

oops I meant to say limit heh..
Are you sure it wouldnt be LIMIT x, #
or will it make a difference

Yes, I'm sure. When you have two numbers, the first specifies the record to start at (0 being the first) and the second specifies the number of records to return

towelie wrote:

also I would need to assign a variable to your little $_GET(...) global there wouldnt I?

Can I see an example of how you would write this query?

LIMIT 15,X WHERE X = $X

$X = $_GET( blah blah )? ? ?

hmm confused

Again, you have the limit backwards, and I gave you the formula for calculating the index to use based on the page number. I would recommend assigning the input page number into a variable so you can check it (eg: it's a number, it's > 0, it's not too big, etc)

  • Registered: 2007-04-15
  • Posts: 96

Re: pagination

Mild progress is underway. I need a little help with the "record to start at". How do I tell it to use that $_GET statement?

10 Reply by towelie (edited by towelie 2007-05-23 20:25)

  • Registered: 2007-04-15
  • Posts: 96

Re: pagination

Okay, I got a lot of help from pogenwurst.
The new code is as follows..
- 2 problems: Every page has shows the same user results & last page is labeled 6.2 instead of 6 or 7

<?php
// PERFORM QUERY & POPULATE TABLE
 $query = 'SELECT id, username, iwins, ilosses, ipoints FROM punbb2_users' . ' ORDER by ipoints DESC LIMIT 0 , 15' ;
 $result = mysql_query($query) or die('Query failed: ' . mysql_error());

if ($db->num_rows($result))
{
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
     echo "\t<tr>\n";
?>

    <td class="tcl" scope="col"><?php echo '<a href="iprofile.php?id='.$line['id'].'">'.pun_htmlspecialchars($line['username']).'</a>' ?></td>
    <td class="tc2" scope="col"><?php echo $line['iwins']; ?></td>
    <td class="tc3" scope="col"><?php echo $line['ilosses']; ?></td>
    <td class="tc4" scope="col"><center><?php echo $line['ipoints']; ?></center></td>        

<?php
          }
    echo "\t</tr>\n";
}
echo "</table>\n";
//END TABLE


//BEGIN PAGINATE
$result = $db->query('SELECT COUNT(id)-1 FROM '.$db->prefix.'users') or error('Unable to fetch total user count', __FILE__, __LINE__, $db->error());
 $query_rows = $db->result($result);

$num_pages = ceil($query_rows) / 15;

if (intval($_GET['p']) && $_GET['p'] <= $num_pages) 
$cur_page = $_GET['p'];

$link_to = 'ladder2.php?foo=bar';

echo '<center>Page:'.paginate($num_pages, $cur_page, $link_to).'</center>';
//END PAGINATE

?>

*the page can be seen here, different url from first post. (testing page)

  • Registered: 2007-04-15
  • Posts: 96

Re: pagination

Can I please get a reply on this?

  • From: Germany
  • Registered: 2007-02-14
  • Posts: 644

Re: pagination

it shows up as 6.2 because the variable $num_pages is a decimal number... you cant just divide by 15... you must do some number formatting... can't help you with that...sorry...just google it!!!

FluxBB - v1.4.8

lie2815's Website

  • Registered: 2007-04-15
  • Posts: 96

Re: pagination

Okay I will check that out thanks, and the main thing is the different results per page. Any ideas?

14 Reply by towelie (edited by towelie 2007-05-24 23:57)

  • Registered: 2007-04-15
  • Posts: 96

Re: pagination

I have this working almost properly, the only problem is that page one doesnt actually show the first 0 - 15 results, it shows 15-30

New code

 $p = (int)$_GET['p']*15;
 $query = 'SELECT id, username, iwins, ilosses, ipoints FROM punbb2_users' . ' ORDER by ipoints DESC, username ASC LIMIT '.$p.', 15' ;
$num_pages = ceil($query_rows / 15);

if (intval($_GET['p']) && $_GET['p'] <= $num_pages) 
$cur_page = $_GET['p'];
  • Registered: 2007-04-15
  • Posts: 96

Re: pagination

I figured it out. I changed..


$p = (int)$_GET['p']*15;
To
$p=isset($_GET['p'])?(int)($_GET['p']-1)*15:0;

  • From: Germany
  • Registered: 2007-02-14
  • Posts: 644

Re: pagination

could you post the final working code?

FluxBB - v1.4.8

lie2815's Website

17 Reply by towelie (edited by towelie 2007-05-25 00:48)

  • Registered: 2007-04-15
  • Posts: 96

Re: pagination

Sure, here you go:

<?php
// PERFORM QUERY & POPULATE TABLE
 $p=isset($_GET['p'])?(int)($_GET['p']-1)*15:0;
 $query = 'SELECT id, username, iwins, ilosses, ipoints FROM punbb2_users' . ' ORDER by ipoints DESC, username ASC LIMIT '.$p.', 15' ;
 $result = mysql_query($query) or die('Query failed: ' . mysql_error());
    
    $id = '<a href="profile.php?id='.$line['id'].'">'.pun_htmlspecialchars($line['username']).'</a>' ;

if ($db->num_rows($result))
{
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
     echo "\t<tr>\n";


            
        
?>

    <td class="tcl" scope="col"><?php echo '<a href="iprofile.php?id='.$line['id'].'">'.pun_htmlspecialchars($line['username']).'</a>' ?></td>
    <td class="tc2" scope="col"><?php echo $line['iwins']; ?></td>
    <td class="tc3" scope="col"><?php echo $line['ilosses']; ?></td>
    <td class="tc4" scope="col"><center><?php echo $line['ipoints']; ?></center></td>        

<?php
          }
    echo "\t</tr>\n";
}
echo "</table>\n";

//BEGIN PAGINATE
$result = $db->query('SELECT COUNT(id)-1 FROM '.$db->prefix.'users') or error('Unable to fetch total user count', __FILE__, __LINE__, $db->error());
 $query_rows = $db->result($result);

$num_pages = ceil($query_rows / 15);

if (intval($_GET['p']) && $_GET['p'] <= $num_pages) 
$cur_page = $_GET['p'];

$link_to = 'iladder.php?';

echo '<center>Page:'.paginate($num_pages, $cur_page, $link_to).'</center>';
//END PAGINATE


?>

There is a bit more to it than that, what's not there is some style stuff and the right block with links.

*updated link (this is now the link that users can use to see the ladder) Link