Paginate from group by

I'm having some difficulty paginating from the $_GET variable,
because in this instance the string being passed is of a group by
query (one letter). Currently I'm limiting the results using limit,
Here's what I currently have and it works just fine at displaying, I
just need to add pagination.


if( !isset( $_GET['title'] ) )
{
print 'No Record was Selected';
}
else
{
$query = mysql_query("SELECT ID,Title FROM table WHERE Title LIKE
'" . $_GET['title'] . "%' LIMIT 0, 100")or die(mysql_error());
}
if( mysql_num_rows($query) == 0 )
{
echo 'Sorry no records';
}
else
{
while($row = mysql_fetch_array($query ))
{


$title=htmlspecialchars($row['Title']);

echo "
\n
";
?>

Re: Paginate from group by

If I understand your problem correctly; you're having problem
distributing the SQL query on several pages.

By using ORDER BY, you can keep track of what was displayed already.

SELECT ID,Title
FROM table
WHERE Title LIKE '" . $_GET['title'] . "%'
ORDER BY title
LIMIT 0, 100

Depending on how many results you want to display per page (let's say
100). You can modify the LIMIT. That is for page 1 'LIMIT 0, 100', for
page 2 'LIMIT 100, 200' and so on

>> echo "
This here looks like a bad design idea. You should instead have a
variable (which you can pass through $_GET), which tells you which
page you're on.

$params = "?title=" . $title . "&page=" . $page;

Hope this helps

Re: Paginate from group by

On Oct 28, 7:53 am, bmic...@gmail.com wrote:
> If I understand your problem correctly; you're having problem
> distributing the SQL query on several pages.
>
> By using ORDER BY, you can keep track of what was displayed already.
>
> SELECT ID,Title
> FROM table
> WHERE Title LIKE '" . $_GET['title'] . "%'
> ORDER BY title
> LIMIT 0, 100
>
> Depending on how many results you want to display per page (let's say
> 100). You can modify the LIMIT. That is for page 1 'LIMIT 0, 100', for
> page 2 'LIMIT 100, 200' and so on
>
> >> echo "
>
> This here looks like a bad design idea. You should instead have a
> variable (which you can pass through $_GET), which tells you which
> page you're on.
>
> $params = "?title=" . $title . "&page=" . $page;
>
> Hope this helps

It kind of helps, the order by is something that was needed. However,
maybe I wasn't clear, but I'm having a tough time trying to paginate
the rest of the results as in a "next" and "previous" link at the
bottom. The results are in the thousands and doing a manual page for
each 100 results would be too much.

Re: Paginate from group by

On Oct 28, 7:53 am, bmic...@gmail.com wrote:
> If I understand your problem correctly; you're having problem
> distributing the SQL query on several pages.
>
> By using ORDER BY, you can keep track of what was displayed already.
>
> SELECT ID,Title
> FROM table
> WHERE Title LIKE '" . $_GET['title'] . "%'
> ORDER BY title
> LIMIT 0, 100
>
> Depending on how many results you want to display per page (let's say
> 100). You can modify the LIMIT. That is for page 1 'LIMIT 0, 100', for
> page 2 'LIMIT 100, 200' and so on
>
> >> echo "
>
> This here looks like a bad design idea. You should instead have a
> variable (which you can pass through $_GET), which tells you which
> page you're on.
>
> $params = "?title=" . $title . "&page=" . $page;
>
> Hope this helps

It kind of helps, the order by is something that was needed. However,
maybe I wasn't clear, but I'm having a tough time trying to paginate
the rest of the results as in a "next" and "previous" link at the
bottom. The results are in the thousands and doing a manual page for
each 100 results would be too much.

Re: Paginate from group by

joanna wrote:
> On Oct 28, 7:53 am, bmic...@gmail.com wrote:
>> If I understand your problem correctly; you're having problem
>> distributing the SQL query on several pages.
>>
>> By using ORDER BY, you can keep track of what was displayed already.
>>
>> SELECT ID,Title
>> FROM table
>> WHERE Title LIKE '" . $_GET['title'] . "%'
>> ORDER BY title
>> LIMIT 0, 100
>>
>> Depending on how many results you want to display per page (let's say
>> 100). You can modify the LIMIT. That is for page 1 'LIMIT 0, 100', for
>> page 2 'LIMIT 100, 200' and so on
>>
>>>> echo "
>> This here looks like a bad design idea. You should instead have a
>> variable (which you can pass through $_GET), which tells you which
>> page you're on.
>>
>> $params = "?title=" . $title . "&page=" . $page;
>>
>> Hope this helps
>
> It kind of helps, the order by is something that was needed. However,
> maybe I wasn't clear, but I'm having a tough time trying to paginate
> the rest of the results as in a "next" and "previous" link at the
> bottom. The results are in the thousands and doing a manual page for
> each 100 results would be too much.
>
>

No, please reread bmichel's comments. His answer is correct on how you
should be paginating your results.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Re: Paginate from group by

On Oct 28, 2:15 pm, Jerry Stuckle wrote:
> joanna wrote:
> > On Oct 28, 7:53 am, bmic...@gmail.com wrote:
> >> If I understand your problem correctly; you're having problem
> >> distributing the SQL query on several pages.
>
> >> By using ORDER BY, you can keep track of what was displayed already.
>
> >> SELECT ID,Title
> >> FROM table
> >> WHERE Title LIKE '" . $_GET['title'] . "%'
> >> ORDER BY title
> >> LIMIT 0, 100
>
> >> Depending on how many results you want to display per page (let's say
> >> 100). You can modify the LIMIT. That is for page 1 'LIMIT 0, 100', for
> >> page 2 'LIMIT 100, 200' and so on
>
> >>>> echo "
> >> This here looks like a bad design idea. You should instead have a
> >> variable (which you can pass through $_GET), which tells you which
> >> page you're on.
>
> >> $params = "?title=" . $title . "&page=" . $page;
>
> >> Hope this helps
>
> > It kind of helps, the order by is something that was needed. However,
> > maybe I wasn't clear, but I'm having a tough time trying to paginate
> > the rest of the results as in a "next" and "previous" link at the
> > bottom. The results are in the thousands and doing a manual page for
> > each 100 results would be too much.
>
> No, please reread bmichel's comments. His answer is correct on how you
> should be paginating your results.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================

Here's how your code might look like:

$record_per_page = 100;

$title = $_GET['title'];
$page = $_GET['page']; // first page is 1 or not set, second page is
2,...

if(!isset($title)) {
echo 'No Record was Selected';
exit(); // this will stop the execution, modfiy as needed
}

if (!isset($page)) {
$page = 1;
}

// Record to look up.
$start_limit = ($page - 1) * $record_per_page;
$end_limit = $page * $record_per_page;

$query = mysql_query("SELECT ID,Title
FROM table WHERE Title LIKE'$title%'
ORDER BY Title
LIMIT $start_limit, $end_limit")
or die(mysql_error());

if( mysql_num_rows($query) == 0) {
echo 'Sorry no records';
exit();
}

while($row = mysql_fetch_array($query )) {

$doc_title = htmlspecialchars($row['Title']); // stores the title
of each row.

// I'm not sure what you're doing after that, so I'm leaving that
for you.


// Links for the pagination.
$next_link = "index.php?title=$title&page=" . ($page + 1);

$prev_link = "";
if ($page != 1) {
$prev_link = "index.php?title=$title&page=" . ($page - 1);
}
?>


Some notes:
Replace 'index.php' by the name of the page the script is executed on.
You might need to do some checking on $next_link also in order not to
get to a page where no results will appear.
exit() here will just stop the execution, so replace it by whatever
you need; or get back to your if - else nested solution.

Re: Paginate from group by

On Oct 28, 10:11 am, bmic...@gmail.com wrote:
> On Oct 28, 2:15 pm, Jerry Stuckle wrote:
>
>
>
> > joanna wrote:
> > > On Oct 28, 7:53 am, bmic...@gmail.com wrote:
> > >> If I understand your problem correctly; you're having problem
> > >> distributing the SQL query on several pages.
>
> > >> By using ORDER BY, you can keep track of what was displayed already.
>
> > >> SELECT ID,Title
> > >> FROM table
> > >> WHERE Title LIKE '" . $_GET['title'] . "%'
> > >> ORDER BY title
> > >> LIMIT 0, 100
>
> > >> Depending on how many results you want to display per page (let's say
> > >> 100). You can modify the LIMIT. That is for page 1 'LIMIT 0, 100', for
> > >> page 2 'LIMIT 100, 200' and so on
>
> > >>>> echo "
> > >> This here looks like a bad design idea. You should instead have a
> > >> variable (which you can pass through $_GET), which tells you which
> > >> page you're on.
>
> > >> $params = "?title=" . $title . "&page=" . $page;
>
> > >> Hope this helps
>
> > > It kind of helps, the order by is something that was needed. However,
> > > maybe I wasn't clear, but I'm having a tough time trying to paginate
> > > the rest of the results as in a "next" and "previous" link at the
> > > bottom. The results are in the thousands and doing a manual page for
> > > each 100 results would be too much.
>
> > No, please reread bmichel's comments. His answer is correct on how you
> > should be paginating your results.
>
> > --
> > ==================
> > Remove the "x" from my email address
> > Jerry Stuckle
> > JDS Computer Training Corp.
> > jstuck...@attglobal.net
> > ==================
>
> Here's how your code might look like:
> >
> $record_per_page = 100;
>
> $title = $_GET['title'];
> $page = $_GET['page']; // first page is 1 or not set, second page is
> 2,...
>
> if(!isset($title)) {
> echo 'No Record was Selected';
> exit(); // this will stop the execution, modfiy as needed
>
> }
>
> if (!isset($page)) {
> $page = 1;
>
> }
>
> // Record to look up.
> $start_limit = ($page - 1) * $record_per_page;
> $end_limit = $page * $record_per_page;
>
> $query = mysql_query("SELECT ID,Title
> FROM table WHERE Title LIKE'$title%'
> ORDER BY Title
> LIMIT $start_limit, $end_limit")
> or die(mysql_error());
>
> if( mysql_num_rows($query) == 0) {
> echo 'Sorry no records';
> exit();
>
> }
>
> while($row = mysql_fetch_array($query )) {
>
> $doc_title = htmlspecialchars($row['Title']); // stores the title
> of each row.
>
> // I'm not sure what you're doing after that, so I'm leaving that
> for you.
>
> // Links for the pagination.
> $next_link = "index.php?title=$title&page=" . ($page + 1);
>
> $prev_link = "";
> if ($page != 1) {
> $prev_link = "index.php?title=$title&page=" . ($page - 1);
> }
> ?>
>
> Some notes:
> Replace 'index.php' by the name of the page the script is executed on.
> You might need to do some checking on $next_link also in order not to
> get to a page where no results will appear.
> exit() here will just stop the execution, so replace it by whatever
> you need; or get back to your if - else nested solution.

Thank you so much, that did it. I added a few echos and it works!