Spliting Number of Results Returned

Anon

Hi all,

Back again! :-)

Okay, here's the background..

I'm currently using a fairly bog standard routine to return various contents of a table, basically an if/while/else loop. Which is coded pretty much as follows: (Ive taken out most of the formatting details to make it a bit clearer...

$result = mysql_query("SELECT * FROM ccentral_maps ORDER BY MAP",$db);
if ($myrow = mysql_fetch_array($result)) {

do {
printf("<a href=javascript:openWindow(\"../maps/maps.php?id=%s&detail=1\")>%s</a><br>", $myrow["id"], $myrow["MAP"]);
printf("<a href=\"%sid=s&edit=1\">EDIT</a> - ", $PHP_SELF, $myrow["id"]);
printf("<a href=\"%s?id=%s&verify=1\">DELETE</a>", $PHP_SELF, $myrow["id"]);

}

while ($myrow = mysql_fetch_array($result));

else {

echo "Sorry, no map records were found!";

As you can probably see this will return all the results from the table.. until there are no more. And it works great! But I got to thinking this table could contain A LOT of rows, and would be better if it could be chopped up a bit...

So with this in mind, as I havent got a clue.. how would I go about limiting the results to say the first 20 rows, then a link at the bottom of the resulting page to the next 20, and so on.. You've probably all seen the next/previous type things on forums and such things. Ive tried looking at the code for these but found it (at my level of knowledge - rock bottom) to be indecipherable.

So any advice.. or pointers in the right direction would be appreciated.

Regards

Lee

rrijnders

SELECT * FROM ccentral_maps ORDER BY MAP LIMIT 20

...will limit your query to returning only the first 20 rows. Then you can put the "next" link on the bottom of the page with a variable like so:

<A HREF="whatever.php?start=21">next</a>

...then when the "next" link is clicked, run the same query again, but this time with a beginning offset:

SELECT * FROM ccentral_maps ORDER BY MAP LIMIT 20 OFFSET 21

This will make the query return 20 more rows, but starting with the 21st record.

-- Rich

Anon

I can see how this works, but it isnt very..erm "dynamic".. I mean if this was a table with like 1000 entries.. and I wanted to provide a limit of 20, for each click of the "next" link.. that's an awful lot of times I'd have to type this out in the code.

What I'm after is something very similar to this, but "knows where its got to".. and in the coding there is just one iteration of the query, link as a whole.. and also for it to work in reverse.. as in a previous link..

My problem is I can't see the wood cos of the trees.. and cant seem to grasp how this works..

Somehow.. it needs to dynamically figure out what the offset is each time its clicked.. and it then loops back around to the original query, with this "dynamic" offset appended. I apologise if I'm not explaining this very well.. but if you want to see the kind of thing I mean..

take a look at the bottom of:
http://www.clan-rage.co.uk/rose/index.php?category=1.php

and you'll get the idea I'm thinking of, Ive trawled thru this code.. and basically cant make head nor tail of it.. ideally some simplified version of this would be ideal, so I can see how it's done.. and lodge that into the grey matter for future reference.

Regards,

Lee
(A bewildered Newbie)

rrijnders

Well, you would only need one page to do this, so I don't know why it would be too much code. Have the "next" and "previous" links link back to the same page, but pass the offsets in a variable. Then in the page, check to see if the offset variable is empty. If it is, then don't append OFFSET to the query. If it has a value, append OFFSET to the query:

if (!empty($offset)) {
 $offsetstring = "OFFSET " . $offset;
}
else {
 $offsetstring = "";
}

$sql = "SELECT * FROM ccentral_maps ORDER BY MAP LIMIT 20" . $offsetstring;

// execute the sql and print results

$nextoffset=$offset + 20;
$prevoffset=$offset - 20;

// print the "previous" and "next" links
print "<A HREF=$PHP_SELF?offset=$prevoffset>previous</a>";
print "<A HREF=$PHP_SELF?offset=$nextoffset>next</a>";

That's the general idea. One page. Not too much code this way, eh?

-- Rich

Anon

Sorry if I am being dumb here, but I just cant seem to grasp the concept.. I thought what you typed looked straightforward enough..

but.. after trying to get it to work in my code, well it all goes a bit pear shaped.

Here is the code I've tried.. and Ive tried several variations..

// ***** end of map editing

else {

// WHAT MAPS HAVE WE GOT?
// ***** show map list

if (!empty($offset)) {
$offsetstring = "OFFSET " . $offset;
}
else {
$offsetstring = "";
}

$result = mysql_query("SELECT * FROM ccentral_maps ORDER BY MAP LIMIT 20" . $offsetstring,$db); 
if ($myrow = mysql_fetch_array($result)) { //<--- IS LINE 214 (SEE BELOW)
echo "<table width=\"100%\" border=\"0\" cellpadding=\"3\" cellspacing=\"1\" bgcolor=\"#000000\">\n";
echo "<tr><td colspan=\"3\"><center><strong>Maps - Admin</strong></center></td>\n";
do {
printf("<tr><td><a href=javascript:openWindow(\"../maps/maps.php?id=%s&detail=1\")>%s</a><br>\n</td>", $myrow["id"], $myrow["MAP"]);
printf("<td><center><a href=\"%s?id=%s&edit=1\">EDIT</a></center></td>\n", $PHP_SELF, $myrow["id"]);
printf("<td><center><a href=\"%s?id=%s&verify=1\">DELETE</a></center></td></tr>\n", $PHP_SELF, $myrow["id"]);

 } while ($myrow = mysql_fetch_array($result));



echo "</table>\n";
$nextoffset=$offset + 20; 
$prevoffset=$offset - 20;
print "<A HREF=$PHP_SELF?&offset=$prevoffset>previous</a>"; 
print "<A HREF=$PHP_SELF?offset=$nextoffset>next</a>"; 

echo "<br><br><center><table width=\"100%\" border=\"0\" cellpadding=\"3\" cellspacing=\"1\" bgcolor=\"#000000\">\n";
printf("<tr><td><center><strong><a href=\"%s?addmap=1\">Add A New Map!</a></strong></td></tr></table></center>\n", $PHP_SELF);

}



else {

// ***** no records to display

echo "<center><table width=\"50%\" border=\"0\" cellpadding=\"3\" cellspacing=\"1\" bgcolor=\"#000000\">\n";
echo "<tr><td><center><strong>Sorry, no map records were found!</strong></td></tr></table></center>";
echo "<br><br><center><table width=\"100%\" border=\"0\" cellpadding=\"3\" cellspacing=\"1\" bgcolor=\"#000000\">\n";
printf("<tr><td><center><strong><a href=\"%s?addmap=1\">Add A New Map!</a></strong></td></tr></table></center>\n", $PHP_SELF);

}
}

All that happens is.. yes initially on loading the page 20 results are printed, and both links are printed also..

However if I click on either of them I get a:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in g:\easyphp\www\clan_central\admin\maps_admin.php on line 214 and the page reverts to the "Sorry, no map records found" which comes after the final else {}.

Sorry but its now 6.15am, and my brain is turning to mush.. so please bare with me, while I try and make sense of this!

Thanks,

Lee

rrijnders

Try changing:

print "<A HREF=$PHP_SELF?&offset=$prevoffset>previous</a>";
print "<A HREF=$PHP_SELF?offset=$nextoffset>next</a>";

To:

print "<A HREF=\"$PHP_SELF?offset=$prevoffset\">previous</a>";
print "<A HREF=\"$PHP_SELF?offset=$nextoffset\">next</a>";

I think you need the quotes around the HREF target or else the vars won't be passed.

-- Rich

swampster

I'm afraid to say that adding the quotes didnt do anything either 🙁

Same error, and same result as I mentioned in my previous posting! 🙁

And there's me thinking after a little sleep I might be able to see this more clearly..

I think I'll be crying myself to sleep if later if I cant get this figured out!

swampster

What's more on the resulting page in the browser, both the "previous" and "next" links are being printed after the initial return of 20 results.. and equate to something like:

Next : http://localhost/clan_central/admin/maps_admin.php?offset=20

Previous : http://localhost/clan_central/admin/maps_admin.php?offset=-20

The warning, and the revert to "Sorry, no map records found" appear after clicking on either link..

So I presume there is something wrong with how the link is being handled by the page.

Also forgive me if I'm mistaken, but shouldnt the "Previous" link not be printed if there no previous entries.. for example on the first page of results?

Regards

Lee

swampster

Is there anybody else willing to give me some pointers/advice here..

Despite Richard's best efforts I cant get this to work.. and its giving me a headache! 🙁

Cheers

Lee

rrijnders

Well, first off, fix the problem with the negative 20 by checking the value of $offset before doing the math:

If ($offset > 20) {
 $prevoffset=$offset - 20;
}
else {
 $prevoffset=0;
}

Also, I think the OFFSET in MySQL is specified differently. Change the relevant code to:

if (!empty($offset)) {
 $offsetstring = $offset . ",";
}
else {
 $offsetstring = "";
}

You can control whether the "previous" link shows up on the page simply by checking if $offset is greater than 0.

Likewise, you can control if the "next" link prints by retrieving 21 records instead of 20. If you actually get 21, print the "next" link, otherwise don't. Leave the offset increment to 20 though.

-- Rich

swampster

Hi again Richard,

The changes you suggested didnt make any difference to the output at all.

The links appear exactly the same as they did,
I get the same warning, and it reverts back to the "Sorry, no map records found" after clicking either link. 🙁

swampster

OK, Ive had enough trying to sort this out for the time being,

I'm off out to the pub for some Vodka, beer, more Vodka, some beer and maybe a few Aftershocks, with some JD thrown in for good measure! 😃

Back 2moro with a hangover...

In fact thinking about it.. if I come back drunk.. I might be able to make more sense of it all...lol

swampster

Wooohooo I fixed it 🙂

$result = mysql_query("SELECT * FROM ccentral_maps ORDER BY MAP LIMIT " . $offsetstring . " 20");

was all that was needed,