SELECTING only so many rows??

Maleko

ok...I havent worked out how to expand rows over multiple pages, SO! can anyone tell me how to display just #so many# rows from a table?? the lastest rows, so if it was a guest book, only the latest 5 entired would be viewed, for example....thanks!

Sxooter

select * from table order by field limit 5;

to offset it, you can use this syntax in mysql:

select * from table order by field limit 10,5;

where you get 5 starting with an offset of 10.

In postgresql, it's the more easily readable, slightly more verbose:

select * from table order by field limit 5 offset 10;

Maleko

Ok, got that working...but now it wont let me order them in ID descening....here:

SELECT FROM ORDER BY id limit 5", $connection

that works....what I had before:

SELECT FROM ORDER BY id DESC", $connection

how can I combine them??

Maleko

Oh, Also, if i wanted to display the rest of them on a seperate page...
So on the main page only 5 are dispalyed...how can i select from 6 onwards on another page?

Sxooter

Use desc or not to get the right set, and if it's backwards just go through the result set in reverse:

$c = num_rows($res);
for ($i=$c;$i>0;$i--){
$row = fetch_row($res,$i);
}

To go to the next page put a think like "offset=5" in the link to the next page and put it where I showed the offset in the SQL query earlier.

Maleko

ok...what is offsetting?? is that skipping the first 10 say then start to display the rows after row 10?

Also, i cant get it revsesing in order....

Maleko

ok....
What I need is to be able to show the latest messages on the main page, as well as limit 5 to a page....havin trouble on that...

wanna use both:

SELECT FROM ORDER BY id limit 5

SELECT FROM ORDER BY id DESC

but it will only let me use one...i get errors when I use both

Best way to combine them?

Sxooter

Have you tried:

select * from table order by id DESC limit 5???

Or does that get you the "wrong end" of the result set. If so, then drop the desc, and just reverse the order when you're displaying, or if you're using a database that supports subselects, do:

select from (select from table order by id limit 5) as a order by a.id desc;

Maleko

LOL, of all the possible ways of typing it, i must have not tried that one! it works!
using
select * from table order by id DESC limit 5???

Im sure i trie dit! lol

Just one final thing...

I have just displaying 5 posts on the main screen...then i got a link at the bottom to view older posts...
Now, i had to copy the entire page scrpt, and just change

select * from table order by id DESC limit 5

select * from table order by id DESC limit 20,5???

Whats the way to instead of having a page with the same code on, with just one line changed, to just a simple link??? do you understaans what im saying??? just so i havent gotta have anew page...can i do that script on the same page?

Sxooter

Yep. what you do is put the prev / next links on the bottom.

The following assumes you're either using auto global vars thingie, or you're grabbing them somewhere from $POST ir $GET

<?php
# This will set offset to 0 if it hasn't been set yet
if (!isset($offset)){
  $offset=0;
} else {
  if (!$is_int($offset)) die("invalid offset: $offset");
}
$numrows = 5;
$query = "select * from table order by id limit $offset,$numrows";
#
# run your query here, display it etc...
#
if ($offset>0){
  print "<A HREF=\"?offset=";
  print $offset-$numrows;
  print ""\">Previous</A>";
if (testformorepages()){
  print "<A  HREF=\"?offset=";
  print $offset+$numrows;
  print ""\">Next</A>";
}

Something like that.

Maleko

hhmm...i get a parse error right at the end of my script...like where i put

?>

i changed the select tables etc to suite...so i keep all my script for b4? also where do i add the part that sets the offset to 0??

and whats that bit about if more pages are left??

Sorry for the question

Maleko

cant get that code working.....:S
just get errors...or just a Next Link...with

?offset=".$offset+$numrows

that in the address bar...

Sxooter

Made a few changes, see if that helps. You're gonna have to do some stuff on your own though. I'm off for the night, so I'll check back in tomorrow morning on ya.