Using Limits and then links at the bottom to continue the query 1

[cfithian]

I am running a query that has over 100 records in it, I want to limit the queries to 20 per page and then put a link at the bottom to continue till it comes to the end of the query. HOw the do I do this? I know how to use the LIMIT, but what more do I need to add to my query to make this happen -

Thanks for anybodys help, banging my head over this...

-C

 

[r937]

you only need LIMIT in the query

the bulk of the logic to do paging has to be in your application code

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[cfithian]

Could you give me an example of the code I need?
Thanks!

 

[r937]

<cfparam name="url.startat" default="0">

<cfquery name="get20rows" datasource="aaa">
  select foo, bar
    from yourtable
   where qux = 937
  order by foo
  limit #url.startat# , 20
</cfquery>

<cfoutput query="get20rows">
<br /> #foo# #bar#
</cfoutput>

<a href="samepage.cfm?startat=#foo#">next 20</a>

note that the default to start at is 0, i.e. the first row

after 20 rows have been sent to output, the #foo# variable has the number of the next one wanted, automatically adjusted to account for the fact that LIMIT starts at 0, not 1



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[cfithian]

Ok, but IM using PHP and not Coldfusion - Sorry if I wasnt clear --- I think I understand but colfusion syntax is different from PHP - Do you know PHP at all?

Thanks for your help!

 

[r937]

nope, sorry, i don't

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[chessbot]

<?php

// ... load mysql contact

$lim = $_GET['limit'];
if (!isset($lim))
  $lim = 0;

$r = mysql_query("select a, b, c from atable limit $lim, 20");
echo "<table>";
while ($row = mysql_fetch_array($r))
{
  echo "<tr>";
  foreach ($row as $val)
  {
    echo "<td>$val</td>";
  }
  echo "</tr>";
}
echo "</table>";

if ($lim > 0)
  echo '<a href="?limit=' . ($lim - 20) . '">Back</a><br>';
echo '<a href="?limit=' . ($lim + 20) . '">Forward</a>';

--Chessbot

There is a level of Hell reserved for probability theorists in which every monkey that types on a typewriter produces a Shakespearean sonnet.