Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
RedMage
Forum Newbie
Posts: 3 Joined: Fri Oct 25, 2002 11:26 am
Location: ROY UTAH
Post
by RedMage » Fri Oct 25, 2002 11:26 am
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?
RedMage
Forum Newbie
Posts: 3 Joined: Fri Oct 25, 2002 11:26 am
Location: ROY UTAH
Post
by RedMage » Sat Oct 26, 2002 12:23 pm
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" );
?>
volka
DevNet Evangelist
Posts: 8391 Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger
Post
by volka » Sat Oct 26, 2002 9:36 pm
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]]
hob_goblin
Forum Regular
Posts: 978 Joined: Sun Apr 28, 2002 9:53 pm
Contact:
Post
by hob_goblin » Sat Oct 26, 2002 11:28 pm
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.
volka
DevNet Evangelist
Posts: 8391 Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger
Post
by volka » Sat Oct 26, 2002 11:38 pm
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
hob_goblin
Forum Regular
Posts: 978 Joined: Sun Apr 28, 2002 9:53 pm
Contact:
Post
by hob_goblin » Sun Oct 27, 2002 12:22 am
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
volka
DevNet Evangelist
Posts: 8391 Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger
Post
by volka » Sun Oct 27, 2002 12:38 am
that's one reason why I let php perform that task
Takuma
Forum Regular
Posts: 931 Joined: Sun Aug 04, 2002 10:24 am
Location: UK
Contact:
Post
by Takuma » Sun Oct 27, 2002 2:00 am
Hi, use this code, I found it on my book
Code: Select all
SELECT * FROM table WHERE id<=$id ORDER BY id LIMIT 10 OFFSET 1;
volka
DevNet Evangelist
Posts: 8391 Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger
Post
by volka » Sun Oct 27, 2002 2:24 am
hmmm...I'm still curious wether I did learn something new today or OFFSET is no mysql-keyword
Takuma
Forum Regular
Posts: 931 Joined: Sun Aug 04, 2002 10:24 am
Location: UK
Contact:
Post
by Takuma » Sun Oct 27, 2002 4:48 am
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
volka
DevNet Evangelist
Posts: 8391 Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger
Post
by volka » Sun Oct 27, 2002 5:14 am
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 » Sun Oct 27, 2002 10:05 pm
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 їLIMIT { number | ALL }] ї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 » Sun Nov 10, 2002 8:22 pm
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.