result set in php pagination

result set in php pagination

am 23.01.2010 19:15:55 von Joe Keenan

I am using php and mysql.

The problem which I am hoping someone can help me with concerns
pagination of the result set of a search of a single table.

I got the pagination script I am using from a Sitepoint tutorial and
it works just as it should for this query

"SELECT article_id, category_name, author_name, title,
article_caption, date_format(magazine_date, '%M, %Y') as newdate FROM
articles where article_id > 0 ORDER BY magazine_date DESC LIMIT
$offset, $rowsperpage"

The result set is return over many pages with no problem at all.

When it comes to this next query however it delivers the first page of
results and sets up the linked pages but those are empty.

"SELECT article_id, author_name, category_name, title,
article_caption, date_format(magazine_date, '%M, %Y') as newdate FROM
articles WHERE author_name = '$author_name' ORDER BY magazine_date ASC
LIMIT $offset, $rowsperpage"

I assume the problem must have to do with '$author_name' which is
passed to the search script as '$author_name = $_POST["author_name"];'
from a php form.

Is there something I should be doing to ensure that the result set
from the query persists though the change in pagination from the first
page of results to the second page.

In both cases the search scripts are includes in other php pages.

I think I need to understand how the result set is carried through or
reacquired in the pagination process.

Sorry if this is phrased very clumsily.

Thank you,

Joe Keenan

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: result set in php pagination

am 23.01.2010 20:46:28 von Phpster

The easiest thing to do is either

A) store the author name in a session for the duration of the searches

B) add the name to the pagination url so that its passed along withe
the page number



On 1/23/10, Joe Keenan wrote:
> I am using php and mysql.
>
> The problem which I am hoping someone can help me with concerns
> pagination of the result set of a search of a single table.
>
> I got the pagination script I am using from a Sitepoint tutorial and
> it works just as it should for this query
>
> "SELECT article_id, category_name, author_name, title,
> article_caption, date_format(magazine_date, '%M, %Y') as newdate FROM
> articles where article_id > 0 ORDER BY magazine_date DESC LIMIT
> $offset, $rowsperpage"
>
> The result set is return over many pages with no problem at all.
>
> When it comes to this next query however it delivers the first page of
> results and sets up the linked pages but those are empty.
>
> "SELECT article_id, author_name, category_name, title,
> article_caption, date_format(magazine_date, '%M, %Y') as newdate FROM
> articles WHERE author_name = '$author_name' ORDER BY magazine_date ASC
> LIMIT $offset, $rowsperpage"
>
> I assume the problem must have to do with '$author_name' which is
> passed to the search script as '$author_name = $_POST["author_name"];'
> from a php form.
>
> Is there something I should be doing to ensure that the result set
> from the query persists though the change in pagination from the first
> page of results to the second page.
>
> In both cases the search scripts are includes in other php pages.
>
> I think I need to understand how the result set is carried through or
> reacquired in the pagination process.
>
> Sorry if this is phrased very clumsily.
>
> Thank you,
>
> Joe Keenan
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

--
Sent from my mobile device


Bastien

Cat, the other other white meat

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: result set in php pagination

am 23.01.2010 21:16:33 von Joe Keenan

I have tried adding the name to the pagination url which gave me
"http://..........results_1.php?currentpage=2&&author_name=E ditorial"
Once again no further results were returned. The pagination urls were
in place but empty, no pages existed for them.

coding sessions in this context is beyond me.
Thanks
Joe.
On 23 Jan 2010, at 19:46, Bastien Koert wrote:

> The easiest thing to do is either
>
> A) store the author name in a session for the duration of the searches
>
> B) add the name to the pagination url so that its passed along withe
> the page number
>
>
>
> On 1/23/10, Joe Keenan wrote:
>> I am using php and mysql.
>>
>> The problem which I am hoping someone can help me with concerns
>> pagination of the result set of a search of a single table.
>>
>> I got the pagination script I am using from a Sitepoint tutorial and
>> it works just as it should for this query
>>
>> "SELECT article_id, category_name, author_name, title,
>> article_caption, date_format(magazine_date, '%M, %Y') as newdate FROM
>> articles where article_id > 0 ORDER BY magazine_date DESC LIMIT
>> $offset, $rowsperpage"
>>
>> The result set is return over many pages with no problem at all.
>>
>> When it comes to this next query however it delivers the first page
>> of
>> results and sets up the linked pages but those are empty.
>>
>> "SELECT article_id, author_name, category_name, title,
>> article_caption, date_format(magazine_date, '%M, %Y') as newdate FROM
>> articles WHERE author_name = '$author_name' ORDER BY magazine_date
>> ASC
>> LIMIT $offset, $rowsperpage"
>>
>> I assume the problem must have to do with '$author_name' which is
>> passed to the search script as '$author_name =
>> $_POST["author_name"];'
>> from a php form.
>>
>> Is there something I should be doing to ensure that the result set
>> from the query persists though the change in pagination from the
>> first
>> page of results to the second page.
>>
>> In both cases the search scripts are includes in other php pages.
>>
>> I think I need to understand how the result set is carried through or
>> reacquired in the pagination process.
>>
>> Sorry if this is phrased very clumsily.
>>
>> Thank you,
>>
>> Joe Keenan
>>
>> --
>> PHP Database Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>>
>>
>
> --
> Sent from my mobile device
>
>
> Bastien
>
> Cat, the other other white meat
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: result set in php pagination

am 23.01.2010 21:23:23 von Joe Keenan

I have tested the script with echo() which shows that the correct
$numrows is returned and then
$totalpages = ceil($numrows / $rowsperpage);

Everything is correct on the first page of results. The pagination
links are displayed correctly but when clicked on the resulting page
is empty.

For some reason I do not understand the result set does not carry
through to the succeeding links in the pagination sequence.

Thanks,
Joe
On 23 Jan 2010, at 19:16, Edward Brookhouse wrote:

> Isn't there a matching select later that sets up the number of pages?
>
> I start with:
>
> $offset = ($pageNum - 1) * $rowsPerPage;
> db_connect_ecelerity(DBUSER, DBUSERPW);
> $query1 = "select * FROM `bouncelog` ORDER BY `insert_date` DESC
> LIMIT $offset, $rowsPerPage";
> $numresults=mysql_query($query1);
> $numrows=mysql_num_rows($numresults);
> $result = mysql_query($query1) or die('Error, lame query failed');
>
>
> Then a bunch of display the data stuff but ending with:
>
> $query = "SELECT COUNT(insert_date) AS numrows FROM bouncelog";
> $result = mysql_query($query) or die('Error, query lamefailed');
> $row = mysql_fetch_array($result, MYSQL_ASSOC);
> $numrows = $row['numrows'];
>
> // how many pages we have when using paging?
> $maxPage = ceil($numrows/$rowsPerPage);
>
> $self = $_SERVER['PHP_SELF'];
>
> if ($pageNum > 1)
> {
> $page = $pageNum - 1;
> $prev = " ";
>
> $first = " ";
> }
> else
> {
> $prev = ' [Prev] '; // we're on page one, don't enable
> 'previous' link
> $first = ' [First Page] '; // nor 'first page' link
> }
>
> if ($pageNum < $maxPage)
> {
> $page = $pageNum + 1;
> $next = " ";
>
> $last = " ";
> }
> else
> {
> $next = ' [Next] '; // we're on the last page, don't enable
> 'next' link
> $last = ' [Last Page] '; // nor 'last page' link
> }
>
> echo $first . $prev . " Showing page $pageNum of
> $maxPage pages " . $next . $last;
>
> echo "";
> echo $first . $prev . " Showing page $pageNum of
> $maxPage pages " . $next . $last;
> ?>
>
>
>
>
> -----Original Message-----
> From: Joe Keenan [mailto:heresiarch@btopenworld.com]
> Sent: Saturday, January 23, 2010 1:16 PM
> To: php-db@lists.php.net
> Subject: [PHP-DB] result set in php pagination
>
> I am using php and mysql.
>
> The problem which I am hoping someone can help me with concerns
> pagination of the result set of a search of a single table.
>
> I got the pagination script I am using from a Sitepoint tutorial and
> it works just as it should for this query
>
> "SELECT article_id, category_name, author_name, title,
> article_caption, date_format(magazine_date, '%M, %Y') as newdate FROM
> articles where article_id > 0 ORDER BY magazine_date DESC LIMIT
> $offset, $rowsperpage"
>
> The result set is return over many pages with no problem at all.
>
> When it comes to this next query however it delivers the first page of
> results and sets up the linked pages but those are empty.
>
> "SELECT article_id, author_name, category_name, title,
> article_caption, date_format(magazine_date, '%M, %Y') as newdate FROM
> articles WHERE author_name = '$author_name' ORDER BY magazine_date ASC
> LIMIT $offset, $rowsperpage"
>
> I assume the problem must have to do with '$author_name' which is
> passed to the search script as '$author_name = $_POST["author_name"];'
> from a php form.
>
> Is there something I should be doing to ensure that the result set
> from the query persists though the change in pagination from the first
> page of results to the second page.
>
> In both cases the search scripts are includes in other php pages.
>
> I think I need to understand how the result set is carried through or
> reacquired in the pagination process.
>
> Sorry if this is phrased very clumsily.
>
> Thank you,
>
> Joe Keenan
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
>
>


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: result set in php pagination

am 23.01.2010 22:26:52 von Joe Keenan

This is all that results from the included script on the 2nd display
page:




Articles by


(There are 0 articles in the database.)





Next a>



Given that the where clause in the original query is "WHERE
author_name = '$author_name' " I cannot see how passing the
author_name in the pagination url will help.

Should I somehow pass $author name which was originally picked up from
a form as

'$author_name = '$author_name = $_POST["author_name"];'

Clearly I am at a loss here. Could anyone suggest anything in the PHP
Manual, or any tutorial or anything at all on the web about sustaining
a result set through pagination links when the SELECT clause of the
query is modified by a WHERE clause containing a variable.

As it stands I am unable to construct a search engine query to cover
the matter.

Thanks
Joe.


On 23 Jan 2010, at 20:45, lists-php wrote:

> You haven't actually indicated what's on the 2nd display page, but I
> suspect that it's just the select statement, with the (url passed)
> pagination substituted in. [one can pass the results in an array,
> but it's unlikely that you're doing that, so your subsequent pages
> simply re-issue the query] if you're doing that, then you'll also
> have code that picks up the pagination parameters from the url.
> you'll need to add coding to pick up the "author" too, otherwise the
> 2nd page won't pick it up and use it.
>
> also, in our link example in an earlier message:
>
> http://...results_1.php?currentpage=2&&author_name=Editorial
>
> you show two ampersands before "author_name". there should only be
> one. also, if your author's name includes a space, you'll need to
> deal with that.
>
>
> - Rick
>
>
>
> ------------ Original Message ------------
>> Date: Saturday, January 23, 2010 08:23:23 PM +0000
>> From: Joe Keenan
>> To: Edward Brookhouse
>> Cc: php-db@lists.php.net
>> Subject: Re: [PHP-DB] result set in php pagination
>>
>> I have tested the script with echo() which shows that the correct
>> $numrows is returned and then
>> $totalpages = ceil($numrows / $rowsperpage);
>>
>> Everything is correct on the first page of results. The pagination
>> links are displayed correctly but when clicked on the resulting
>> page is empty.
>>
>> For some reason I do not understand the result set does not carry
>> through to the succeeding links in the pagination sequence.
>>
>> Thanks,
>> Joe
>> On 23 Jan 2010, at 19:16, Edward Brookhouse wrote:
>>
>>> Isn't there a matching select later that sets up the number of
>>> pages?
>>>
>>> I start with:
>>>
>>> $offset = ($pageNum - 1) * $rowsPerPage;
>>> db_connect_ecelerity(DBUSER, DBUSERPW);
>>> $query1 = "select * FROM `bouncelog` ORDER BY `insert_date` DESC
>>> LIMIT $offset, $rowsPerPage";
>>> $numresults=mysql_query($query1);
>>> $numrows=mysql_num_rows($numresults);
>>> $result = mysql_query($query1) or die('Error, lame query failed');
>>>
>>>
>>> Then a bunch of display the data stuff but ending with:
>>>
>>> $query = "SELECT COUNT(insert_date) AS numrows FROM bouncelog";
>>> $result = mysql_query($query) or die('Error, query lamefailed');
>>> $row = mysql_fetch_array($result, MYSQL_ASSOC);
>>> $numrows = $row['numrows'];
>>>
>>> // how many pages we have when using paging?
>>> $maxPage = ceil($numrows/$rowsPerPage);
>>>
>>> $self = $_SERVER['PHP_SELF'];
>>>
>>> if ($pageNum > 1)
>>> {
>>> $page = $pageNum - 1;
>>> $prev = " ";
>>>
>>> $first = " ";
>>> }
>>> else
>>> {
>>> $prev = ' [Prev] '; // we're on page one, don't enable
>>> 'previous' link
>>> $first = ' [First Page] '; // nor 'first page' link
>>> }
>>>
>>> if ($pageNum < $maxPage)
>>> {
>>> $page = $pageNum + 1;
>>> $next = " ";
>>>
>>> $last = " ";
>>> }
>>> else
>>> {
>>> $next = ' [Next] '; // we're on the last page, don't
>>> enable 'next' link
>>> $last = ' [Last Page] '; // nor 'last page' link
>>> }
>>>
>>> echo $first . $prev . " Showing page $pageNum of
>>> $maxPage pages " . $next . $last;
>>>
>>> echo "";
>>> echo $first . $prev . " Showing page $pageNum of
>>> $maxPage pages " . $next . $last; ?>
>>>
>>>
>>>
>>>
>>> -----Original Message-----
>>> From: Joe Keenan [mailto:heresiarch@btopenworld.com]
>>> Sent: Saturday, January 23, 2010 1:16 PM
>>> To: php-db@lists.php.net
>>> Subject: [PHP-DB] result set in php pagination
>>>
>>> I am using php and mysql.
>>>
>>> The problem which I am hoping someone can help me with concerns
>>> pagination of the result set of a search of a single table.
>>>
>>> I got the pagination script I am using from a Sitepoint tutorial
>>> and it works just as it should for this query
>>>
>>> "SELECT article_id, category_name, author_name, title,
>>> article_caption, date_format(magazine_date, '%M, %Y') as newdate
>>> FROM articles where article_id > 0 ORDER BY magazine_date DESC
>>> LIMIT $offset, $rowsperpage"
>>>
>>> The result set is return over many pages with no problem at all.
>>>
>>> When it comes to this next query however it delivers the first
>>> page of results and sets up the linked pages but those are empty.
>>>
>>> "SELECT article_id, author_name, category_name, title,
>>> article_caption, date_format(magazine_date, '%M, %Y') as newdate
>>> FROM articles WHERE author_name = '$author_name' ORDER BY
>>> magazine_date ASC LIMIT $offset, $rowsperpage"
>>>
>>> I assume the problem must have to do with '$author_name' which is
>>> passed to the search script as '$author_name =
>>> $_POST["author_name"];' from a php form.
>>>
>>> Is there something I should be doing to ensure that the result set
>>> from the query persists though the change in pagination from the
>>> first page of results to the second page.
>>>
>>> In both cases the search scripts are includes in other php pages.
>>>
>>> I think I need to understand how the result set is carried
>>> through or reacquired in the pagination process.
>>>
>>> Sorry if this is phrased very clumsily.
>>>
>>> Thank you,
>>>
>>> Joe Keenan
>>>
>>> --
>>> PHP Database Mailing List (http://www.php.net/)
>>> To unsubscribe, visit: http://www.php.net/unsub.php
>>>
>>>
>>>
>
> ------------ End Original Message ------------
>
>


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: result set in php pagination

am 23.01.2010 22:32:48 von Phpster

If you change the $_POST to &_REQUEST in this piece of code

> $author_name = $_POST["author_name"];'

To make it

> $author_name = $_REQUEST["author_name"];'

Should fix that issue

Bastien

Sent from my iPod

On Jan 23, 2010, at 4:26 PM, Joe Keenan
wrote:

> This is all that results from the included script on the 2nd display
> page:
>


>
> Given that the where clause in the original query is "WHERE
> author_name = '$author_name' " I cannot see how passing the
> author_name in the pagination url will help.
>
> Should I somehow pass $author name which was originally picked up
> from a form as
>
> '$author_name = '$author_name = $_POST["author_name"];'
>
> Clearly I am at a loss here. Could anyone suggest anything in the
> PHP Manual, or any tutorial or anything at all on the web about
> sustaining a result set through pagination links when the SELECT
> clause of the query is modified by a WHERE clause containing a
> variable.
>
> As it stands I am unable to construct a search engine query to cover
> the matter.
>
> Thanks
> Joe.
>
>
> On 23 Jan 2010, at 20:45, lists-php wrote:
>
>> You haven't actually indicated what's on the 2nd display page, but I
>> suspect that it's just the select statement, with the (url passed)
>> pagination substituted in. [one can pass the results in an array,
>> but it's unlikely that you're doing that, so your subsequent pages
>> simply re-issue the query] if you're doing that, then you'll also
>> have code that picks up the pagination parameters from the url.
>> you'll need to add coding to pick up the "author" too, otherwise the
>> 2nd page won't pick it up and use it.
>>
>> also, in our link example in an earlier message:
>>
>> http://...results_1.php?currentpage=2&&author_name=Editorial
>>
>> you show two ampersands before "author_name". there should only be
>> one. also, if your author's name includes a space, you'll need to
>> deal with that.
>>
>>
>> - Rick
>>
>>
>>
>> ------------ Original Message ------------
>>> Date: Saturday, January 23, 2010 08:23:23 PM +0000
>>> From: Joe Keenan
>>> To: Edward Brookhouse
>>> Cc: php-db@lists.php.net
>>> Subject: Re: [PHP-DB] result set in php pagination
>>>
>>> I have tested the script with echo() which shows that the correct
>>> $numrows is returned and then
>>> $totalpages = ceil($numrows / $rowsperpage);
>>>
>>> Everything is correct on the first page of results. The pagination
>>> links are displayed correctly but when clicked on the resulting
>>> page is empty.
>>>
>>> For some reason I do not understand the result set does not carry
>>> through to the succeeding links in the pagination sequence.
>>>
>>> Thanks,
>>> Joe
>>> On 23 Jan 2010, at 19:16, Edward Brookhouse wrote:
>>>
>>>> Isn't there a matching select later that sets up the number of
>>>> pages?
>>>>
>>>> I start with:
>>>>
>>>> $offset = ($pageNum - 1) * $rowsPerPage;
>>>> db_connect_ecelerity(DBUSER, DBUSERPW);
>>>> $query1 = "select * FROM `bouncelog` ORDER BY `insert_date` DESC
>>>> LIMIT $offset, $rowsPerPage";
>>>> $numresults=mysql_query($query1);
>>>> $numrows=mysql_num_rows($numresults);
>>>> $result = mysql_query($query1) or die('Error, lame query failed');
>>>>
>>>>
>>>> Then a bunch of display the data stuff but ending with:
>>>>
>>>> $query = "SELECT COUNT(insert_date) AS numrows FROM bouncelog";
>>>> $result = mysql_query($query) or die('Error, query lamefailed');
>>>> $row = mysql_fetch_array($result, MYSQL_ASSOC);
>>>> $numrows = $row['numrows'];
>>>>
>>>> // how many pages we have when using paging?
>>>> $maxPage = ceil($numrows/$rowsPerPage);
>>>>
>>>> $self = $_SERVER['PHP_SELF'];
>>>>
>>>> if ($pageNum > 1)
>>>> {
>>>> $page = $pageNum - 1;
>>>> $prev = " ";
>>>>
>>>> $first = " ";
>>>> }
>>>> else
>>>> {
>>>> $prev = ' [Prev] '; // we're on page one, don't enable
>>>> 'previous' link
>>>> $first = ' [First Page] '; // nor 'first page' link
>>>> }
>>>>
>>>> if ($pageNum < $maxPage)
>>>> {
>>>> $page = $pageNum + 1;
>>>> $next = " ";
>>>>
>>>> $last = " ";
>>>> }
>>>> else
>>>> {
>>>> $next = ' [Next] '; // we're on the last page, don't
>>>> enable 'next' link
>>>> $last = ' [Last Page] '; // nor 'last page' link
>>>> }
>>>>
>>>> echo $first . $prev . " Showing page $pageNum of
>>>> $maxPage pages " . $next . $last;
>>>>
>>>> echo "";
>>>> echo $first . $prev . " Showing page $pageNum of
>>>> $maxPage pages " . $next . $last; ?>
>>>>
>>>>
>>>>
>>>>
>>>> -----Original Message-----
>>>> From: Joe Keenan [mailto:heresiarch@btopenworld.com]
>>>> Sent: Saturday, January 23, 2010 1:16 PM
>>>> To: php-db@lists.php.net
>>>> Subject: [PHP-DB] result set in php pagination
>>>>
>>>> I am using php and mysql.
>>>>
>>>> The problem which I am hoping someone can help me with concerns
>>>> pagination of the result set of a search of a single table.
>>>>
>>>> I got the pagination script I am using from a Sitepoint tutorial
>>>> and it works just as it should for this query
>>>>
>>>> "SELECT article_id, category_name, author_name, title,
>>>> article_caption, date_format(magazine_date, '%M, %Y') as newdate
>>>> FROM articles where article_id > 0 ORDER BY magazine_date DESC
>>>> LIMIT $offset, $rowsperpage"
>>>>
>>>> The result set is return over many pages with no problem at all.
>>>>
>>>> When it comes to this next query however it delivers the first
>>>> page of results and sets up the linked pages but those are empty.
>>>>
>>>> "SELECT article_id, author_name, category_name, title,
>>>> article_caption, date_format(magazine_date, '%M, %Y') as newdate
>>>> FROM articles WHERE author_name = '$author_name' ORDER BY
>>>> magazine_date ASC LIMIT $offset, $rowsperpage"
>>>>
>>>> I assume the problem must have to do with '$author_name' which is
>>>> passed to the search script as '$author_name =
>>>> $_POST["author_name"];' from a php form.
>>>>
>>>> Is there something I should be doing to ensure that the result set
>>>> from the query persists though the change in pagination from the
>>>> first page of results to the second page.
>>>>
>>>> In both cases the search scripts are includes in other php pages.
>>>>
>>>> I think I need to understand how the result set is carried
>>>> through or reacquired in the pagination process.
>>>>
>>>> Sorry if this is phrased very clumsily.
>>>>
>>>> Thank you,
>>>>
>>>> Joe Keenan
>>>>
>>>> --
>>>> PHP Database Mailing List (http://www.php.net/)
>>>> To unsubscribe, visit: http://www.php.net/unsub.php
>>>>
>>>>
>>>>
>>
>> ------------ End Original Message ------------
>>
>>
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: result set in php pagination

am 23.01.2010 23:04:45 von Joe Keenan

Thanks very much Bastien. I haven't the faintest idea why but that
simple change in the code sorted all of this. The pagination links now
follow through with the complete result set of my query.

I will now have to read around this to discover why the change works.

And thanks very much to everyone who took the trouble to reply to my
cry for help. It is all very much appreciated.

Joe.
On 23 Jan 2010, at 21:32, Phpster wrote:

> If you change the $_POST to &_REQUEST in this piece of code
>
>> $author_name = $_POST["author_name"];'
>
> To make it
>
>> $author_name = $_REQUEST["author_name"];'
>
> Should fix that issue
>
> Bastien
>
> Sent from my iPod
>
> On Jan 23, 2010, at 4:26 PM, Joe Keenan
> wrote:
>
>> This is all that results from the included script on the 2nd
>> display page:
>>


>>
>> Given that the where clause in the original query is "WHERE
>> author_name = '$author_name' " I cannot see how passing the
>> author_name in the pagination url will help.
>>
>> Should I somehow pass $author name which was originally picked up
>> from a form as
>>
>> '$author_name = '$author_name = $_POST["author_name"];'
>>
>> Clearly I am at a loss here. Could anyone suggest anything in the
>> PHP Manual, or any tutorial or anything at all on the web about
>> sustaining a result set through pagination links when the SELECT
>> clause of the query is modified by a WHERE clause containing a
>> variable.
>>
>> As it stands I am unable to construct a search engine query to
>> cover the matter.
>>
>> Thanks
>> Joe.
>>
>>
>> On 23 Jan 2010, at 20:45, lists-php wrote:
>>
>>> You haven't actually indicated what's on the 2nd display page, but I
>>> suspect that it's just the select statement, with the (url passed)
>>> pagination substituted in. [one can pass the results in an array,
>>> but it's unlikely that you're doing that, so your subsequent pages
>>> simply re-issue the query] if you're doing that, then you'll also
>>> have code that picks up the pagination parameters from the url.
>>> you'll need to add coding to pick up the "author" too, otherwise the
>>> 2nd page won't pick it up and use it.
>>>
>>> also, in our link example in an earlier message:
>>>
>>> http://...results_1.php?currentpage=2&&author_name=Editorial
>>>
>>> you show two ampersands before "author_name". there should only be
>>> one. also, if your author's name includes a space, you'll need to
>>> deal with that.
>>>
>>>
>>> - Rick
>>>
>>>
>>>
>>> ------------ Original Message ------------
>>>> Date: Saturday, January 23, 2010 08:23:23 PM +0000
>>>> From: Joe Keenan
>>>> To: Edward Brookhouse
>>>> Cc: php-db@lists.php.net
>>>> Subject: Re: [PHP-DB] result set in php pagination
>>>>
>>>> I have tested the script with echo() which shows that the correct
>>>> $numrows is returned and then
>>>> $totalpages = ceil($numrows / $rowsperpage);
>>>>
>>>> Everything is correct on the first page of results. The pagination
>>>> links are displayed correctly but when clicked on the resulting
>>>> page is empty.
>>>>
>>>> For some reason I do not understand the result set does not carry
>>>> through to the succeeding links in the pagination sequence.
>>>>
>>>> Thanks,
>>>> Joe
>>>> On 23 Jan 2010, at 19:16, Edward Brookhouse wrote:
>>>>
>>>>> Isn't there a matching select later that sets up the number of
>>>>> pages?
>>>>>
>>>>> I start with:
>>>>>
>>>>> $offset = ($pageNum - 1) * $rowsPerPage;
>>>>> db_connect_ecelerity(DBUSER, DBUSERPW);
>>>>> $query1 = "select * FROM `bouncelog` ORDER BY `insert_date` DESC
>>>>> LIMIT $offset, $rowsPerPage";
>>>>> $numresults=mysql_query($query1);
>>>>> $numrows=mysql_num_rows($numresults);
>>>>> $result = mysql_query($query1) or die('Error, lame query failed');
>>>>>
>>>>>
>>>>> Then a bunch of display the data stuff but ending with:
>>>>>
>>>>> $query = "SELECT COUNT(insert_date) AS numrows FROM bouncelog";
>>>>> $result = mysql_query($query) or die('Error, query lamefailed');
>>>>> $row = mysql_fetch_array($result, MYSQL_ASSOC);
>>>>> $numrows = $row['numrows'];
>>>>>
>>>>> // how many pages we have when using paging?
>>>>> $maxPage = ceil($numrows/$rowsPerPage);
>>>>>
>>>>> $self = $_SERVER['PHP_SELF'];
>>>>>
>>>>> if ($pageNum > 1)
>>>>> {
>>>>> $page = $pageNum - 1;
>>>>> $prev = " ";
>>>>>
>>>>> $first = " ";
>>>>> }
>>>>> else
>>>>> {
>>>>> $prev = ' [Prev] '; // we're on page one, don't enable
>>>>> 'previous' link
>>>>> $first = ' [First Page] '; // nor 'first page' link
>>>>> }
>>>>>
>>>>> if ($pageNum < $maxPage)
>>>>> {
>>>>> $page = $pageNum + 1;
>>>>> $next = " ";
>>>>>
>>>>> $last = " ";
>>>>> }
>>>>> else
>>>>> {
>>>>> $next = ' [Next] '; // we're on the last page, don't
>>>>> enable 'next' link
>>>>> $last = ' [Last Page] '; // nor 'last page' link
>>>>> }
>>>>>
>>>>> echo $first . $prev . " Showing page $pageNum of
>>>>> $maxPage pages " . $next . $last;
>>>>>
>>>>> echo "";
>>>>> echo $first . $prev . " Showing page $pageNum of
>>>>> $maxPage pages " . $next . $last; ?>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> -----Original Message-----
>>>>> From: Joe Keenan [mailto:heresiarch@btopenworld.com]
>>>>> Sent: Saturday, January 23, 2010 1:16 PM
>>>>> To: php-db@lists.php.net
>>>>> Subject: [PHP-DB] result set in php pagination
>>>>>
>>>>> I am using php and mysql.
>>>>>
>>>>> The problem which I am hoping someone can help me with concerns
>>>>> pagination of the result set of a search of a single table.
>>>>>
>>>>> I got the pagination script I am using from a Sitepoint tutorial
>>>>> and it works just as it should for this query
>>>>>
>>>>> "SELECT article_id, category_name, author_name, title,
>>>>> article_caption, date_format(magazine_date, '%M, %Y') as newdate
>>>>> FROM articles where article_id > 0 ORDER BY magazine_date DESC
>>>>> LIMIT $offset, $rowsperpage"
>>>>>
>>>>> The result set is return over many pages with no problem at all.
>>>>>
>>>>> When it comes to this next query however it delivers the first
>>>>> page of results and sets up the linked pages but those are empty.
>>>>>
>>>>> "SELECT article_id, author_name, category_name, title,
>>>>> article_caption, date_format(magazine_date, '%M, %Y') as newdate
>>>>> FROM articles WHERE author_name = '$author_name' ORDER BY
>>>>> magazine_date ASC LIMIT $offset, $rowsperpage"
>>>>>
>>>>> I assume the problem must have to do with '$author_name' which is
>>>>> passed to the search script as '$author_name =
>>>>> $_POST["author_name"];' from a php form.
>>>>>
>>>>> Is there something I should be doing to ensure that the result set
>>>>> from the query persists though the change in pagination from the
>>>>> first page of results to the second page.
>>>>>
>>>>> In both cases the search scripts are includes in other php pages.
>>>>>
>>>>> I think I need to understand how the result set is carried
>>>>> through or reacquired in the pagination process.
>>>>>
>>>>> Sorry if this is phrased very clumsily.
>>>>>
>>>>> Thank you,
>>>>>
>>>>> Joe Keenan
>>>>>
>>>>> --
>>>>> PHP Database Mailing List (http://www.php.net/)
>>>>> To unsubscribe, visit: http://www.php.net/unsub.php
>>>>>
>>>>>
>>>>>
>>>
>>> ------------ End Original Message ------------
>>>
>>>
>>
>>
>> --
>> PHP Database Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>>


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: result set in php pagination

am 24.01.2010 23:32:10 von dmagick

Joe Keenan wrote:
> Thanks very much Bastien. I haven't the faintest idea why but that
> simple change in the code sorted all of this. The pagination links now
> follow through with the complete result set of my query.
>
> I will now have to read around this to discover why the change works.

By putting it in the url means it's not a POST variable any more, it is
now a GET variable.

REQUEST covers both (and more) - so if it appears in GET, it will be set
to that. If it appears in POST, it'll use that instead.

Also nobody's mentioned you should use mysql_real_escape_string when you
do your query otherwise your site will (eventually) have problems with
sql injection.

$query = "SELECT article_id, author_name, category_name, title,
article_caption, date_format(magazine_date, '%M, %Y') as newdate
FROM articles WHERE author_name = '$author_name' ORDER BY
magazine_date ASC LIMIT $offset, $rowsperpage";

becomes:

$query = "SELECT article_id, author_name, category_name, title,
article_caption, date_format(magazine_date, '%M, %Y') as newdate
FROM articles WHERE author_name = '" .
mysql_real_escape_string($author_name) . "' ORDER BY
magazine_date ASC LIMIT $offset, $rowsperpage";

You won't see much difference if you print it out but if you put things
like a single quote in your name search, it will be handled now and not
cause a mysql error.


--
Postgresql & php tutorials
http://www.designmagick.com/


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php