Page 1 of 1

How Do I split up an array into pages

Posted: Fri Oct 25, 2002 11:26 am
by RedMage
I am extracting data from a database into an array and I want to know how I can split it into pages, having only a certain amount of entries per page.

I Tried something like this:

Code: Select all

<?php
   if(!isSet($Count)) { $Count = 0; } // If not set give initial value
   if(!isSet($View)) { $View = 5; } // If not set give initial value

   if (!isSet($result))
    {
     $result = mysql_query( "SELECT ID, DATE_FORMAT(E_DATE, '%m.%d.%y %H:%i'), E_ADDR, E_NAME, E_MAIL, E_HTTP, E_COMM FROM TABLE_NAME ORDER BY E_DATE DESC" ); // Perform query
    }
   if (!$result)
    {
      print("Query failed.");
      exit();
    }
   else
    {
     while ( $Count < $View & $entry = mysql_fetch_array($result) ) 
      {
       $COUNT++;
       $DATE = $entryї"DATE_FORMAT(E_DATE, '%m.%d.%y %H:%i')"];
       $ADDR = $entryї"E_ADDR"];
       $NAME = $entryї"E_NAME"];
       $MAIL = $entryї"E_MAIL"];
       $HTTP = $entryї"E_HTTP"];
       $COMM = $entryї"E_COMM"];

       print(" $DATE $ADDR $NAME $MAIL $HTTP $COMM <BR> ");
       $Count++
      }
   $View+= 5;
   print(" <A HREF="$PHP_SELF?View=$View">Next</A> ");
?>
I can get it to print he first five but once I click next, it just adds the next five on. Can anyone help?

Posted: Fri Oct 25, 2002 12:54 pm
by daynah
Try offsetting your data rows by using the LIMIT function.

Here's a nice article on it. :)
http://www.phpfreaks.com/postgresqlmanu ... limit.html

Posted: Sat Oct 26, 2002 12:23 pm
by RedMage
LIMIT works but when I add an OFFSET clause it doesn't. This is what I have

Code: Select all

<?php
     $result = mysql_query( "SELECT JUNK FROM TABLE ORDER BY DATE DESC LIMIT 5 OFFSET 1" );
?>

Posted: Sat Oct 26, 2002 9:36 pm
by volka
what's an OFFSET-clause? ;)
http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#SELECT
SELECT [STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]
[DISTINCT | DISTINCTROW | ALL]
select_expression,...
[INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
[FROM table_references
[WHERE where_definition]
[GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC], ...
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
[LIMIT [offset,] rows]
[PROCEDURE procedure_name(argument_list)]
[FOR UPDATE | LOCK IN SHARE MODE]]

Posted: Sat Oct 26, 2002 11:28 pm
by hob_goblin
its more like

LIMIT 0, 5

will start at 0, and get 5 rows

LIMIT 5, 10

will start at 5, and grab up to 10, get it? so try:

LIMIT '$count', '$count+$view'

or something of that sort.

Posted: Sat Oct 26, 2002 11:38 pm
by volka
since these are numerical values you don't need the quotes
LIMIT 50, 10
I suggest to use the pagenumber like

Code: Select all

$query = 'SELECT .... LIMIT '.$page*$rowsPerPage.','.$rowsPerPage;
but that's only matter of opinion

Posted: Sun Oct 27, 2002 12:22 am
by hob_goblin
volka wrote:since these are numerical values you don't need the quotes
LIMIT 50, 10
I wasn't sure if that would work because of $var+$var - i've had problems with it in the past.

Like, I know LIMIT $var, $var2 works, but i've had problems with LIMIT $var, $var+$var2

Posted: Sun Oct 27, 2002 12:38 am
by volka
that's one reason why I let php perform that task ;)

Posted: Sun Oct 27, 2002 2:00 am
by Takuma
Hi, use this code, I found it on my book :D

Code: Select all

SELECT * FROM table WHERE id<=$id ORDER BY id LIMIT 10 OFFSET 1;

Posted: Sun Oct 27, 2002 2:24 am
by volka
hmmm...I'm still curious wether I did learn something new today or OFFSET is no mysql-keyword ;)

Posted: Sun Oct 27, 2002 4:48 am
by Takuma
OFFSET is a Keyword. I think if you set OFFSET as 3 and limit the result to 7 you will find that you will get 3,4,5,6,7,8,9,10.

Code: Select all

1.  Result skipped
2.  Result skipped
3.  Result returned
4.  Result returned
5.  Result returned
6.  Result returned
7.  Result returned
8.  Result returned
9.  Result returned
10. Result returned

Posted: Sun Oct 27, 2002 5:14 am
by volka
hmmm....can't find it in the mysql-manual and my mysql-server rejects it.
Maybe another DB? Or pear?

Posted: Sun Oct 27, 2002 10:05 pm
by RedMage

Code: Select all

$QUERY = mysql_query( "SELECT * FROM whatEver ORDER BY whatEver LIMIT $OFFSET,$LIMIT" ); // What I was looking for
The article daynah provided portrayed the use of LIMIT and OFFSET like this

Code: Select all

SELECT select_list FROM table_expression &#1111;LIMIT &#123; number | ALL &#125;] &#1111;OFFSET number]
It made it seem like OFFSET was literal and followed the LIMIT clause, so I was confused.
But I got it to work! Thanks everyone [o:

Posted: Sun Nov 10, 2002 8:22 pm
by thoughtriot
Can I ask what the full result was then? The full php script I mean. I need to do this for a guestbook. I need to display 10 entries per page.