How Do I split up an array into pages

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
RedMage
Forum Newbie
Posts: 3
Joined: Fri Oct 25, 2002 11:26 am
Location: ROY UTAH

How Do I split up an array into pages

Post 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?
daynah
Forum Newbie
Posts: 6
Joined: Fri Oct 25, 2002 12:48 pm
Location: USA

Post 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
RedMage
Forum Newbie
Posts: 3
Joined: Fri Oct 25, 2002 11:26 am
Location: ROY UTAH

Post 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" );
?>
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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]]
User avatar
hob_goblin
Forum Regular
Posts: 978
Joined: Sun Apr 28, 2002 9:53 pm
Contact:

Post 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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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
User avatar
hob_goblin
Forum Regular
Posts: 978
Joined: Sun Apr 28, 2002 9:53 pm
Contact:

Post 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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

that's one reason why I let php perform that task ;)
User avatar
Takuma
Forum Regular
Posts: 931
Joined: Sun Aug 04, 2002 10:24 am
Location: UK
Contact:

Post 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;
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

hmmm...I'm still curious wether I did learn something new today or OFFSET is no mysql-keyword ;)
User avatar
Takuma
Forum Regular
Posts: 931
Joined: Sun Aug 04, 2002 10:24 am
Location: UK
Contact:

Post 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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

hmmm....can't find it in the mysql-manual and my mysql-server rejects it.
Maybe another DB? Or pear?
RedMage
Forum Newbie
Posts: 3
Joined: Fri Oct 25, 2002 11:26 am
Location: ROY UTAH

Post 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:
thoughtriot
Forum Commoner
Posts: 26
Joined: Thu Nov 07, 2002 9:32 pm

Post 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.
Post Reply