RE: [PHP] Paging and permissions
On Tue, 2011-02-08 at 14:36 +0200, Arno Kuhl wrote: I'm hoping some clever php gurus have been here before and are willing to share some ideas. I have a site where articles are assigned to categories in containers. An article can be assigned to only one category per container, but one or more containers. Access permissions can be set per article, per category and/or per container, for one or more users and/or user groups. If an article is assigned to 10 categories and only one of those has a permission denying access, then the article can't be accessed even if browsing through one of the other 9 categories. Currently everything works fine, with article titles showing when browsing through category or search result lists, and a message is displayed when the article is clicked if it cannot be viewed because of a permission. Now there's a requirement to not display the article title in category lists and search results if it cannot be viewed. I'm stuck with how to determine the number of results for paging at the start of the list or search. The site is quite large (20,000+ articles and growing) so reading the entire result set and sifting through it with permission rules for each request is not an option. But it might be an option if done once at the start of each search or list request, and then use that temporary modified result set for subsequent requests on the same set. I thought of saving the set to a temporary db table or file (not sure about overhead of serializing/unserializing large arrays). A sizing exercise based on the recordset returned for searches and lists shows a max of about 150MB for 20,000 articles and 380MB for 50,000 articles that needs to be saved temporarily per search or list request - in the vast majority of cases the set will be *much* smaller but it needs to cope with the worst case, and still do so a year down the line. All this extra work because I can't simply get an accurate number of results for paging, because of permissions! So my questions are: 1. Which is better (performance) for this situation: file or db? 2. How do I prepare a potentially very large data set for file or fast writing to a new table (ie I obviously don't want to write it record by record) 3. Are there any other alternatives worth looking at? TIA Cheers Arno How are you determining (logically, not in code) when an article is allowed to be read? Assume an article on user permissions in mysql is in a container called 'databases' and in a second one called 'security' and both containers are in a category called 'computers' Now get a user called John who is in a group called 'db admins' and that group gives him permissions to view all articles in the 'databases' container and any articles in any container in the 'computers' category. Now assume John also has explicit user permissions revoking that right to view the article in any container. What I'm getting at is what's the order of privilege for rights? Do group rights for categories win out over those for containers, or do individual user rights trump all of them overall? I think once that's figured out, a lot can be done inside the query itself to minimise the impact on the script getting the results. Thanks, Ash http://www.ashleysheridan.co.uk --- The simple structure is articles in categories, categories in containers, only one article per container/category, in one or more containers. If an article permission explicitly allows or denies access then the permission applies, otherwise the container/s and category/s permissions are checked. The permission checks user access first then group. A user can belong to multiple groups. There's no query to handle this that can return a neat recordset for paging. Currently the complete checks are only done for an article request. The category list only checks access to the category and the container it belongs to, so the list is either displayed in its entirety (including titles of articles that can't be viewed) or not at all, and obviously the paging works perfectly because the total number of titles is known up front and remains constant for subsequent requests. If I use read-ahead to make allowance for permissions and remove paging (just keep prev/next) the problem goes away. Or I could use best-guess paging, which could range from 100% accurate to 99% wrong. At first glance that's not really acceptable, but I noticed recently Google does the same thing with their search results. First prize is to work out a proper solution that is fast and accurate and works on fairly large results, and I'm still hoping for some
RE: [PHP] Paging and permissions
On Wed, 2011-02-09 at 13:03 +0200, Arno Kuhl wrote: On Tue, 2011-02-08 at 14:36 +0200, Arno Kuhl wrote: I'm hoping some clever php gurus have been here before and are willing to share some ideas. I have a site where articles are assigned to categories in containers. An article can be assigned to only one category per container, but one or more containers. Access permissions can be set per article, per category and/or per container, for one or more users and/or user groups. If an article is assigned to 10 categories and only one of those has a permission denying access, then the article can't be accessed even if browsing through one of the other 9 categories. Currently everything works fine, with article titles showing when browsing through category or search result lists, and a message is displayed when the article is clicked if it cannot be viewed because of a permission. Now there's a requirement to not display the article title in category lists and search results if it cannot be viewed. I'm stuck with how to determine the number of results for paging at the start of the list or search. The site is quite large (20,000+ articles and growing) so reading the entire result set and sifting through it with permission rules for each request is not an option. But it might be an option if done once at the start of each search or list request, and then use that temporary modified result set for subsequent requests on the same set. I thought of saving the set to a temporary db table or file (not sure about overhead of serializing/unserializing large arrays). A sizing exercise based on the recordset returned for searches and lists shows a max of about 150MB for 20,000 articles and 380MB for 50,000 articles that needs to be saved temporarily per search or list request - in the vast majority of cases the set will be *much* smaller but it needs to cope with the worst case, and still do so a year down the line. All this extra work because I can't simply get an accurate number of results for paging, because of permissions! So my questions are: 1. Which is better (performance) for this situation: file or db? 2. How do I prepare a potentially very large data set for file or fast writing to a new table (ie I obviously don't want to write it record by record) 3. Are there any other alternatives worth looking at? TIA Cheers Arno How are you determining (logically, not in code) when an article is allowed to be read? Assume an article on user permissions in mysql is in a container called 'databases' and in a second one called 'security' and both containers are in a category called 'computers' Now get a user called John who is in a group called 'db admins' and that group gives him permissions to view all articles in the 'databases' container and any articles in any container in the 'computers' category. Now assume John also has explicit user permissions revoking that right to view the article in any container. What I'm getting at is what's the order of privilege for rights? Do group rights for categories win out over those for containers, or do individual user rights trump all of them overall? I think once that's figured out, a lot can be done inside the query itself to minimise the impact on the script getting the results. Thanks, Ash http://www.ashleysheridan.co.uk --- The simple structure is articles in categories, categories in containers, only one article per container/category, in one or more containers. If an article permission explicitly allows or denies access then the permission applies, otherwise the container/s and category/s permissions are checked. The permission checks user access first then group. A user can belong to multiple groups. There's no query to handle this that can return a neat recordset for paging. Currently the complete checks are only done for an article request. The category list only checks access to the category and the container it belongs to, so the list is either displayed in its entirety (including titles of articles that can't be viewed) or not at all, and obviously the paging works perfectly because the total number of titles is known up front and remains constant for subsequent requests. If I use read-ahead to make allowance for permissions and remove paging (just keep prev/next) the problem goes away. Or I could use best-guess paging, which could range from 100% accurate to 99% wrong. At first glance that's not really acceptable, but I noticed recently Google does the same thing with their search results. First prize is to work out a proper solution that is fast
RE: [PHP] Paging and permissions
Instead of serializing the articles, you only need their IDs. Using
$sql .= ' where id in (' . implode(',', $ids) . ')';
you can load the data for a page of results in a single query. Storing the
IDs is much cheaper than the articles.
If the permissions are fairly static (i.e. access for user X to article Y
doesn't change every two minutes) you could create a calculated permission
table as a many-to-many between user and article. Here's the logic flow for
a query:
1. Run the query to find matching article IDs
2. Load permissions from table for all IDs
3. For each article without a calculated permission, calculate it and insert
a row (do a batch insert to save time)
If you flag the query in the middle tier as having been processed as above,
you can join to the calculated permissions each time you need another page.
The downside is that the code that runs the queries has to operate in two
modes: raw and joined to the permissions. If most users end up querying for
all articles, the table could grow. Plus you need to purge rows any time the
permissions for an article/user changes which could get fairly complicated.
David
---
Storing only the IDs is way cheaper than storing the entire resultset, and
I'd been thinking along the same lines. Getting a complete list of valid IDs
in the first place is turning out to be a different matter. The permissions
for article/user aren't that straight-forward, and in fact the most common
permissions are group/category and group/container, where an article can be
assigned to one or more category/containers. Using a temporary permission
table could be the solution. Thanks.
Cheers
Arno
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Paging and permissions
On Wed, 2011-02-09 at 13:27 +0200, Arno Kuhl wrote:
Instead of serializing the articles, you only need their IDs. Using
$sql .= ' where id in (' . implode(',', $ids) . ')';
you can load the data for a page of results in a single query. Storing the
IDs is much cheaper than the articles.
If the permissions are fairly static (i.e. access for user X to article Y
doesn't change every two minutes) you could create a calculated permission
table as a many-to-many between user and article. Here's the logic flow for
a query:
1. Run the query to find matching article IDs
2. Load permissions from table for all IDs
3. For each article without a calculated permission, calculate it and insert
a row (do a batch insert to save time)
If you flag the query in the middle tier as having been processed as above,
you can join to the calculated permissions each time you need another page.
The downside is that the code that runs the queries has to operate in two
modes: raw and joined to the permissions. If most users end up querying for
all articles, the table could grow. Plus you need to purge rows any time the
permissions for an article/user changes which could get fairly complicated.
David
---
Storing only the IDs is way cheaper than storing the entire resultset, and
I'd been thinking along the same lines. Getting a complete list of valid IDs
in the first place is turning out to be a different matter. The permissions
for article/user aren't that straight-forward, and in fact the most common
permissions are group/category and group/container, where an article can be
assigned to one or more category/containers. Using a temporary permission
table could be the solution. Thanks.
Cheers
Arno
You can get the same set of results with a join betwixt the tables, and
it should be slightly faster than creating a temporary table if you've
got your indexes right.
Thanks,
Ash
http://www.ashleysheridan.co.uk
Re: [PHP] Paging and permissions
On Tue, Feb 8, 2011 at 7:36 AM, Arno Kuhl ak...@telkomsa.net wrote: I'm hoping some clever php gurus have been here before and are willing to share some ideas. I have a site where articles are assigned to categories in containers. An article can be assigned to only one category per container, but one or more containers. Access permissions can be set per article, per category and/or per container, for one or more users and/or user groups. If an article is assigned to 10 categories and only one of those has a permission denying access, then the article can't be accessed even if browsing through one of the other 9 categories. Currently everything works fine, with article titles showing when browsing through category or search result lists, and a message is displayed when the article is clicked if it cannot be viewed because of a permission. Now there's a requirement to not display the article title in category lists and search results if it cannot be viewed. I'm stuck with how to determine the number of results for paging at the start of the list or search. The site is quite large (20,000+ articles and growing) so reading the entire result set and sifting through it with permission rules for each request is not an option. But it might be an option if done once at the start of each search or list request, and then use that temporary modified result set for subsequent requests on the same set. I thought of saving the set to a temporary db table or file (not sure about overhead of serializing/unserializing large arrays). A sizing exercise based on the recordset returned for searches and lists shows a max of about 150MB for 20,000 articles and 380MB for 50,000 articles that needs to be saved temporarily per search or list request - in the vast majority of cases the set will be *much* smaller but it needs to cope with the worst case, and still do so a year down the line. All this extra work because I can't simply get an accurate number of results for paging, because of permissions! So my questions are: 1. Which is better (performance) for this situation: file or db? have you timed it yourself? 2. How do I prepare a potentially very large data set for file or fast writing to a new table (ie I obviously don't want to write it record by record) Even the db's cant insert as fast as the function is presented to it, and it can respond, so again...timeit 3. Are there any other alternatives worth looking at? This is a question for the experienced php developers. But the above is applicable. TIA Cheers Arno -- According to theoretical physics, the division of spatial intervals as the universe evolves gives rise to the fact that in another timeline, your interdimensional counterpart received helpful advice from me...so be eternally pleased for them. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Paging and permissions
On Tue, 2011-02-08 at 14:36 +0200, Arno Kuhl wrote: I'm hoping some clever php gurus have been here before and are willing to share some ideas. I have a site where articles are assigned to categories in containers. An article can be assigned to only one category per container, but one or more containers. Access permissions can be set per article, per category and/or per container, for one or more users and/or user groups. If an article is assigned to 10 categories and only one of those has a permission denying access, then the article can't be accessed even if browsing through one of the other 9 categories. Currently everything works fine, with article titles showing when browsing through category or search result lists, and a message is displayed when the article is clicked if it cannot be viewed because of a permission. Now there's a requirement to not display the article title in category lists and search results if it cannot be viewed. I'm stuck with how to determine the number of results for paging at the start of the list or search. The site is quite large (20,000+ articles and growing) so reading the entire result set and sifting through it with permission rules for each request is not an option. But it might be an option if done once at the start of each search or list request, and then use that temporary modified result set for subsequent requests on the same set. I thought of saving the set to a temporary db table or file (not sure about overhead of serializing/unserializing large arrays). A sizing exercise based on the recordset returned for searches and lists shows a max of about 150MB for 20,000 articles and 380MB for 50,000 articles that needs to be saved temporarily per search or list request - in the vast majority of cases the set will be *much* smaller but it needs to cope with the worst case, and still do so a year down the line. All this extra work because I can't simply get an accurate number of results for paging, because of permissions! So my questions are: 1. Which is better (performance) for this situation: file or db? 2. How do I prepare a potentially very large data set for file or fast writing to a new table (ie I obviously don't want to write it record by record) 3. Are there any other alternatives worth looking at? TIA Cheers Arno How are you determining (logically, not in code) when an article is allowed to be read? Assume an article on user permissions in mysql is in a container called 'databases' and in a second one called 'security' and both containers are in a category called 'computers' Now get a user called John who is in a group called 'db admins' and that group gives him permissions to view all articles in the 'databases' container and any articles in any container in the 'computers' category. Now assume John also has explicit user permissions revoking that right to view the article in any container. What I'm getting at is what's the order of privilege for rights? Do group rights for categories win out over those for containers, or do individual user rights trump all of them overall? I think once that's figured out, a lot can be done inside the query itself to minimise the impact on the script getting the results. Thanks, Ash http://www.ashleysheridan.co.uk
Re: [PHP] Paging and permissions
On Tue, Feb 8, 2011 at 1:36 PM, Arno Kuhl ak...@telkomsa.net wrote: I'm hoping some clever php gurus have been here before and are willing to share some ideas. I have a site where articles are assigned to categories in containers. An article can be assigned to only one category per container, but one or more containers. Access permissions can be set per article, per category and/or per container, for one or more users and/or user groups. If an article is assigned to 10 categories and only one of those has a permission denying access, then the article can't be accessed even if browsing through one of the other 9 categories. Currently everything works fine, with article titles showing when browsing through category or search result lists, and a message is displayed when the article is clicked if it cannot be viewed because of a permission. Now there's a requirement to not display the article title in category lists and search results if it cannot be viewed. I'm stuck with how to determine the number of results for paging at the start of the list or search. The site is quite large (20,000+ articles and growing) so reading the entire result set and sifting through it with permission rules for each request is not an option. But it might be an option if done once at the start of each search or list request, and then use that temporary modified result set for subsequent requests on the same set. I thought of saving the set to a temporary db table or file (not sure about overhead of serializing/unserializing large arrays). A sizing exercise based on the recordset returned for searches and lists shows a max of about 150MB for 20,000 articles and 380MB for 50,000 articles that needs to be saved temporarily per search or list request - in the vast majority of cases the set will be *much* smaller but it needs to cope with the worst case, and still do so a year down the line. All this extra work because I can't simply get an accurate number of results for paging, because of permissions! So my questions are: 1. Which is better (performance) for this situation: file or db? 2. How do I prepare a potentially very large data set for file or fast writing to a new table (ie I obviously don't want to write it record by record) 3. Are there any other alternatives worth looking at? TIA Cheers Arno Seems to me you make your setup needlessly complicated and restrictive. And it's bad form to display articles in search results that aren't allowed to be viewed.. Tell us more about why you want it to be so restrictive, i just don't understand it.
Re: [PHP] Paging and permissions
On Tue, Feb 8, 2011 at 4:36 AM, Arno Kuhl ak...@telkomsa.net wrote:
But it might be an option if done once at the start of each
search or list request, and then use that temporary modified result set for
subsequent requests on the same set.
Instead of serializing the articles, you only need their IDs. Using
$sql .= ' where id in (' . implode(',', $ids) . ')';
you can load the data for a page of results in a single query. Storing the
IDs is much cheaper than the articles.
If the permissions are fairly static (i.e. access for user X to article Y
doesn't change every two minutes) you could create a calculated permission
table as a many-to-many between user and article. Here's the logic flow for
a query:
1. Run the query to find matching article IDs
2. Load permissions from table for all IDs
3. For each article without a calculated permission, calculate it and insert
a row (do a batch insert to save time)
If you flag the query in the middle tier as having been processed as above,
you can join to the calculated permissions each time you need another page.
The downside is that the code that runs the queries has to operate in two
modes: raw and joined to the permissions. If most users end up querying for
all articles, the table could grow. Plus you need to purge rows any time the
permissions for an article/user changes which could get fairly complicated.
On Tue, Feb 8, 2011 at 5:17 AM, Tolas Anon tolas...@gmail.com wrote:
And it's bad form to display articles in search results that aren't allowed
to be viewed.
On Tue, Feb 8, 2011 at 4:36 AM, Arno Kuhl ak...@telkomsa.net wrote:
Now there's a requirement to not display the article title in category
lists
and search results if it cannot be viewed.
:)
David
RE: [PHP] Paging script
Sorry but I dont understand what you mean. Can you be more specific?
{1} 2 3 ... 12 (Startup / Total 12 pages)
When you click 7
1 ... 6 {7} 8 ... 12
When you click 11 or 12
1 ... 10 11 {12}
Do you mean something like this?
-Original Message-
From: Angelo Zanetti [mailto:ang...@elemental.co.za]
Sent: Wednesday, September 30, 2009 1:21 PM
To: php-general@lists.php.net
Subject: [PHP] Paging script
Hi All,
I am looking for a paging script, not the normal type but slightly
different. It must show as follows:
1 2 3 ... 12
Then:
1 ... 678 ... 12
And
1 ... 10 11 12
I have googled and not really found much and don't really want to reinvent
the wheel. I am sure I can write my own but due to time constraints ill like
to get a script that will assist me.
Thanks in advance
http://www.wapit.co.za
http://www.elemental.co.za
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Bu mesaj ve ekleri, mesajda g?nderildi?i belirtilen ki?i/ki?ilere ?zeldir ve
gizlidir. Size yanl??l?kla ula?m??sa l?tfen g?nderen kisiyi bilgilendiriniz ve
mesaj? sisteminizden siliniz. Mesaj ve eklerinin i?eri?i ile ilgili olarak
?irketimizin herhangi bir hukuki sorumlulu?u bulunmamaktad?r. ?irketimiz
mesaj?n ve bilgilerinin size de?i?ikli?e u?rayarak veya ge? ula?mas?ndan,
b?t?nl???n?n ve gizlili?inin korunamamas?ndan, vir?s i?ermesinden ve bilgisayar
sisteminize verebilece?i herhangi bir zarardan sorumlu tutulamaz.
This message and attachments are confidential and intended for the
individual(s) stated in this message. If you received this message in error,
please immediately notify the sender and delete it from your system. Our
company has no legal responsibility for the contents of the message and its
attachments. Our company shall have no liability for any changes or late
receiving, loss of integrity and confidentiality, viruses and any damages
caused in anyway to your computer system.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Paging script
-Original Message-
From: Mert Oztekin [mailto:mozte...@anadolusigorta.com.tr]
Sent: 30 September 2009 12:40 PM
To: 'Angelo Zanetti'; php-general@lists.php.net
Subject: RE: [PHP] Paging script
Sorry but I dont understand what you mean. Can you be more specific?
{1} 2 3 ... 12 (Startup / Total 12 pages)
When you click 7
1 ... 6 {7} 8 ... 12
When you click 11 or 12
1 ... 10 11 {12}
Do you mean something like this?
Hi Mert,
Yes I mean exactly like you have it there,
Regards
Angelo
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Paging script
On Wed, 2009-09-30 at 12:59 +0200, Angelo Zanetti wrote:
-Original Message-
From: Mert Oztekin [mailto:mozte...@anadolusigorta.com.tr]
Sent: 30 September 2009 12:40 PM
To: 'Angelo Zanetti'; php-general@lists.php.net
Subject: RE: [PHP] Paging script
Sorry but I dont understand what you mean. Can you be more specific?
{1} 2 3 ... 12 (Startup / Total 12 pages)
When you click 7
1 ... 6 {7} 8 ... 12
When you click 11 or 12
1 ... 10 11 {12}
Do you mean something like this?
Hi Mert,
Yes I mean exactly like you have it there,
Regards
Angelo
I'd just code it yourself, the logic behind it is pretty simple.
* The numbers 1 and 12 (the total) will always display
* The page you are on will display the number to either side of
it, unless that is 1 or 12 (the total)
* If the page you are on is 2 or 11 (one less than the total) then
add in '...' on either side of the middle block.
Thanks,
Ash
http://www.ashleysheridan.co.uk
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Paging script
Hi all,
Maybe it is not just what you need but could be helpfull.
Here you have the code:
//---paginator.php
?php
function paginator ($current, $total, $per_page, $link, $limit) {
if ($current == '')
{
$current = 1;
}else{
$current = $current;
}
$total_pages = ceil($total/$per_page);
$previous = $current - 1;
$next = $current + 1;
/*traduction*/
$toFrom = pPage .$current. of .$total_pages./p;
$Goto = Go to Page;
$go = Go;
$texto =
div id='paginacion'.$toFrom.
ul;
if ($current 1)
$texto .= lia href=\$link$previous\laquo;/a/li;
else
$texto .= liblaquo;/b/li;
//Se lista hasta llegar a la posición actual
if ($current $limit) {
$start = ($current - $limit) + 1;
$texto .= lib.../b/li;
} else {
$start = 1;
}
for ($start; $start $current; $start++) {
$texto .= lia href=\$link$start\$start/a/li;
}
//mostramos posicion actual
$texto .= lib$current/b/li ;
//mostramos resto de registros
for ($i=$current+1; $i=$total_pages; $i++) {
if ($i $limit)
{
$texto .= lib.../b/li;
$i = $total_pages;
}else{
$texto .= lia href=\$link$i\$i/a/li ;
}
}
if ($current $total_pages)
{
$texto .= lia href=\$link$next\raquo;/a/li;
}else{
$texto .= libraquo;/b/li;
}
$texto .= /ul;
//a form to go directly to the page you want
$texto .= '!--FORMULARIO PAGINADOR--
div id=form_ir
form action='.$_SERVER[PHP_SELF].' method=get
fieldset
p'.$Goto.' input type=text size=3 name=page value= /
input type=submit name=ir value='.$go.' //p
/fieldset
/form
/div';
$texto .= 'br class=break /
/div
';
return $texto;
}
?
//---paginator.php
//---example.php
?php
include (paginator.php);
for ($n=0; $n=100; $n++)
{
$myArray [$n] = $n;
}
$total = count ($myArray); //the total elements in the array
$per_page = 5; //elements the script show
$link = example.php?page=; //link
$limit = 3; //number of items you show in the paginator list
if ($_GET[page])
{
$current = $_GET[page];
}else{
$current = $_POST[page];
}
$start = ($current-1) * $per_page;
$end = $start + $per_page;
//call to the paginator function ...
echo paginator ($current, $total, $per_page, $link, $limit);
//Show the results in different pages
for ($i = $start; $i=$end ;$i++)
{
echo $myArray[$i].br /;
}
?
//---example.php
2009/9/30 Angelo Zanetti ang...@elemental.co.za
Hi All,
I am looking for a paging script, not the normal type but slightly
different. It must show as follows:
1 2 3 ... 12
Then:
1 ... 678 ... 12
And
1 ... 10 11 12
I have googled and not really found much and don't really want to reinvent
the wheel. I am sure I can write my own but due to time constraints ill
like
to get a script that will assist me.
Thanks in advance
http://www.wapit.co.za
http://www.elemental.co.za
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
http://peachep.wordpress.com
RE: [PHP] Paging script
_
From: Ruben Crespo [mailto:rumails...@gmail.com]
Sent: 30 September 2009 01:23 PM
To: Angelo Zanetti
Cc: php-general@lists.php.net
Subject: Re: [PHP] Paging script
Hi all,
Maybe it is not just what you need but could be helpfull.
Here you have the code:
//---paginator.php--
--
?php
function paginator ($current, $total, $per_page, $link, $limit) {
if ($current == '')
{
$current = 1;
}else{
$current = $current;
}
$total_pages = ceil($total/$per_page);
$previous = $current - 1;
$next = $current + 1;
/*traduction*/
$toFrom = pPage .$current. of .$total_pages./p;
$Goto = Go to Page;
$go = Go;
$texto =
div id='paginacion'.$toFrom.
ul;
if ($current 1)
$texto .= lia href=\$link$previous\laquo;/a/li;
else
$texto .= liblaquo;/b/li;
//Se lista hasta llegar a la posición actual
if ($current $limit) {
$start = ($current - $limit) + 1;
$texto .= lib.../b/li;
} else {
$start = 1;
}
for ($start; $start $current; $start++) {
$texto .= lia href=\$link$start\$start/a/li;
}
//mostramos posicion actual
$texto .= lib$current/b/li ;
//mostramos resto de registros
for ($i=$current+1; $i=$total_pages; $i++) {
if ($i $limit)
{
$texto .= lib.../b/li;
$i = $total_pages;
}else{
$texto .= lia href=\$link$i\$i/a/li ;
}
}
if ($current $total_pages)
{
$texto .= lia href=\$link$next\raquo;/a/li;
}else{
$texto .= libraquo;/b/li;
}
$texto .= /ul;
//a form to go directly to the page you want
$texto .= '!--FORMULARIO PAGINADOR--
div id=form_ir
form action='.$_SERVER[PHP_SELF].' method=get
fieldset
p'.$Goto.' input type=text size=3 name=page value= /
input type=submit name=ir value='.$go.' //p
/fieldset
/form
/div';
$texto .= 'br class=break /
/div
';
return $texto;
}
?
//---paginator.php--
--
//---example.php
?php
include (paginator.php);
for ($n=0; $n=100; $n++)
{
$myArray [$n] = $n;
}
$total = count ($myArray); //the total elements in the array
$per_page = 5; //elements the script show
$link = example.php?page=; //link
$limit = 3; //number of items you show in the paginator list
if ($_GET[page])
{
$current = $_GET[page];
}else{
$current = $_POST[page];
}
$start = ($current-1) * $per_page;
$end = $start + $per_page;
//call to the paginator function ...
echo paginator ($current, $total, $per_page, $link, $limit);
//Show the results in different pages
for ($i = $start; $i=$end ;$i++)
{
echo $myArray[$i].br /;
}
?
//---example.php
Thanks ruben very much :-)
Regards
Angelo
http://www.wapit.co.za
http://www.elemental.co.za
RE: [PHP] Paging script
This should do it. i didnt have time to test so there may be some bugs.
function pageit($limit , $total, $page, $link)
{
$last = intval($total / $limit) + ($total % $limit ==0 ? 0:1);
if(!is_numeric($page) || $page $last)
$page = 1;
$pages[] = 1;
if($page == 1)
{
$pages[] = 2;
$pages[] = 3;
}
else
if($page == $last)
{
$pages[]= $last - 2;
$pages[] = $last -1;
}
else
{
if($page - 1 1)
$pages[] = $page -1;
$pages[] = $page;
if($page +1 $last)
$pages[] = $page +1;
}
$pages[] = $last;
for($i=0; $i count($pages) ; $i++)
{
if($pages[$i] 1 || $pages[$i] $last)
continue;
if($i0)
if($pages[$i] - $pages[$i-1] 1 )
echo '...';
echo 'a href=' . $link . '?page=' . $pages[$i]. '' .
$pages[$i] . '/a';
}
}
-Original Message-
From: Angelo Zanetti [mailto:ang...@elemental.co.za]
Sent: Wednesday, September 30, 2009 1:59 PM
To: Mert Oztekin; php-general@lists.php.net
Subject: RE: [PHP] Paging script
-Original Message-
From: Mert Oztekin [mailto:mozte...@anadolusigorta.com.tr]
Sent: 30 September 2009 12:40 PM
To: 'Angelo Zanetti'; php-general@lists.php.net
Subject: RE: [PHP] Paging script
Sorry but I dont understand what you mean. Can you be more specific?
{1} 2 3 ... 12 (Startup / Total 12 pages)
When you click 7
1 ... 6 {7} 8 ... 12
When you click 11 or 12
1 ... 10 11 {12}
Do you mean something like this?
Hi Mert,
Yes I mean exactly like you have it there,
Regards
Angelo
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Bu mesaj ve ekleri, mesajda g?nderildi?i belirtilen ki?i/ki?ilere ?zeldir ve
gizlidir. Size yanl??l?kla ula?m??sa l?tfen g?nderen kisiyi bilgilendiriniz ve
mesaj? sisteminizden siliniz. Mesaj ve eklerinin i?eri?i ile ilgili olarak
?irketimizin herhangi bir hukuki sorumlulu?u bulunmamaktad?r. ?irketimiz
mesaj?n ve bilgilerinin size de?i?ikli?e u?rayarak veya ge? ula?mas?ndan,
b?t?nl???n?n ve gizlili?inin korunamamas?ndan, vir?s i?ermesinden ve bilgisayar
sisteminize verebilece?i herhangi bir zarardan sorumlu tutulamaz.
This message and attachments are confidential and intended for the
individual(s) stated in this message. If you received this message in error,
please immediately notify the sender and delete it from your system. Our
company has no legal responsibility for the contents of the message and its
attachments. Our company shall have no liability for any changes or late
receiving, loss of integrity and confidentiality, viruses and any damages
caused in anyway to your computer system.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Paging script
O.K Angelo,
I wish you that you find the code useful for your projects.
Best Regards !!
Rubén Crespo
2009/9/30 Angelo Zanetti ang...@elemental.co.za
_
From: Ruben Crespo [mailto:rumails...@gmail.com]
Sent: 30 September 2009 01:23 PM
To: Angelo Zanetti
Cc: php-general@lists.php.net
Subject: Re: [PHP] Paging script
Hi all,
Maybe it is not just what you need but could be helpfull.
Here you have the code:
//---paginator.php--
--
?php
function paginator ($current, $total, $per_page, $link, $limit) {
if ($current == '')
{
$current = 1;
}else{
$current = $current;
}
$total_pages = ceil($total/$per_page);
$previous = $current - 1;
$next = $current + 1;
/*traduction*/
$toFrom = pPage .$current. of .$total_pages./p;
$Goto = Go to Page;
$go = Go;
$texto =
div id='paginacion'.$toFrom.
ul;
if ($current 1)
$texto .= lia href=\$link$previous\laquo;/a/li;
else
$texto .= liblaquo;/b/li;
//Se lista hasta llegar a la posición actual
if ($current $limit) {
$start = ($current - $limit) + 1;
$texto .= lib.../b/li;
} else {
$start = 1;
}
for ($start; $start $current; $start++) {
$texto .= lia href=\$link$start\$start/a/li;
}
//mostramos posicion actual
$texto .= lib$current/b/li ;
//mostramos resto de registros
for ($i=$current+1; $i=$total_pages; $i++) {
if ($i $limit)
{
$texto .= lib.../b/li;
$i = $total_pages;
}else{
$texto .= lia href=\$link$i\$i/a/li ;
}
}
if ($current $total_pages)
{
$texto .= lia href=\$link$next\raquo;/a/li;
}else{
$texto .= libraquo;/b/li;
}
$texto .= /ul;
//a form to go directly to the page you want
$texto .= '!--FORMULARIO PAGINADOR--
div id=form_ir
form action='.$_SERVER[PHP_SELF].' method=get
fieldset
p'.$Goto.' input type=text size=3 name=page value= /
input type=submit name=ir value='.$go.' //p
/fieldset
/form
/div';
$texto .= 'br class=break /
/div
';
return $texto;
}
?
//---paginator.php--
--
//---example.php
?php
include (paginator.php);
for ($n=0; $n=100; $n++)
{
$myArray [$n] = $n;
}
$total = count ($myArray); //the total elements in the array
$per_page = 5; //elements the script show
$link = example.php?page=; //link
$limit = 3; //number of items you show in the paginator list
if ($_GET[page])
{
$current = $_GET[page];
}else{
$current = $_POST[page];
}
$start = ($current-1) * $per_page;
$end = $start + $per_page;
//call to the paginator function ...
echo paginator ($current, $total, $per_page, $link, $limit);
//Show the results in different pages
for ($i = $start; $i=$end ;$i++)
{
echo $myArray[$i].br /;
}
?
//---example.php
Thanks ruben very much :-)
Regards
Angelo
http://www.wapit.co.za
http://www.elemental.co.za
--
http://peachep.wordpress.com
Re: [PHP] paging
At 3:26 AM + 2/10/09, Jim Douglas wrote: Does anyone have a link to any examples of paging? Jim: Sure. http://webbytedd.com/bbb/paging/ -- the code is there different examples here: http://webbytedd.com/ccc/pagination Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] paging
... Hi, Too lazy to actually read the email (tsk), but there's rather a nice paging library in PEAR that may help. Imaginitively called Pager. -- Richard Heyes HTML5 Canvas graphing for Firefox, Chrome, Opera and Safari: http://www.rgraph.org (Updated January 31st) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] paging
I saw your site originally. I have paging working and I also have this example working, http://www.w3schools.com/php/php_ajax_database.asp My problem is not getting paging to work, it's getting paging to work outputting the results of clicking on the page number to the content pane. I have a CSS that has left, right, header, footer and a content pane. After I click on the drop down it outputs to the content pane just fine. Thanks, Jim Date: Tue, 10 Feb 2009 08:26:23 -0500 To: jd...@hotmail.com; php-general@lists.php.net From: tedd.sperl...@gmail.com Subject: Re: [PHP] paging At 3:26 AM + 2/10/09, Jim Douglas wrote: Does anyone have a link to any examples of paging? Jim: Sure. http://webbytedd.com/bbb/paging/ -- the code is there different examples here: http://webbytedd.com/ccc/pagination Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php _ Windows Live™: E-mail. Chat. Share. Get more ways to connect. http://windowslive.com/online/hotmail?ocid=TXT_TAGLM_WL_HM_AE_Faster_022009
Re: [PHP] paging
On Tue, 2009-02-10 at 03:26 +, Jim Douglas wrote: http://phpro.org/tutorials/Pagination-with-PHP-and-PDO.html Does anyone have a link to any examples of paging? Kevin http://phpro.org -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] paging at which level
I'm still a little wet behind the ears, nih?! Not Invented Here -- Richard Heyes HTML5 Graphing for FF, Chrome, Opera and Safari: http://www.rgraph.org -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] paging at which level
I've not used a library to achieve paging NIH syndrome? ;-) -- Richard Heyes HTML5 Graphing for FF, Chrome, Opera and Safari: http://www.rgraph.org -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] paging at which level
On Sun, 2008-10-19 at 10:23 +0100, Richard Heyes wrote: I've not used a library to achieve paging NIH syndrome? ;-) I'm still a little wet behind the ears, nih?! Ash www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] paging at which level
usually i use PEAR and page thanks their table library Table library? Y'know there's a dedicated paging library in PEAR, imaginitively called Pager. , but to avoid high transfer of data from DB to PHP page it is better to do the paging at database level. I would like to know what is your point of view on this topic and what do you use to do ? My Datagrid does this for you. You simply give it a database connection, along with an SQL query, and it does the rest: http://www.phpguru.org/downloads/datagrid/latest/ -- Richard Heyes HTML5 Graphing for FF, Chrome, Opera and Safari: http://www.rgraph.org -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] paging at which level
On Sat, 2008-10-18 at 12:54 +0200, Alain Roger wrote: Hi, i would like to know what is the best approach for paging ? usually i use PEAR and page thanks their table library, but to avoid high transfer of data from DB to PHP page it is better to do the paging at database level. If you want top page data then you absolutely should NOT retrieve the entire set of results (unless they are less than your paging length). I would like to know what is your point of view on this topic and what do you use to do ? Use a paging system that takes a query or can build the query itself. Most decent libraries or frameworks have something already built to do so. Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] paging at which level
On Sat, 2008-10-18 at 12:54 +0200, Alain Roger wrote: Hi, i would like to know what is the best approach for paging ? usually i use PEAR and page thanks their table library, but to avoid high transfer of data from DB to PHP page it is better to do the paging at database level. I would like to know what is your point of view on this topic and what do you use to do ? thx. I've not used a library to achieve paging but doing it at the database level is a must really, as you don't want to retrieve large data sets, only to work on a small sub-section of them. As I've no experience of using libraries for this, I've always coded the queries myself. LIMIT in MySQL comes in real handy, but if you're using an older version of MSSQL, then you will have to use nested selects like this: SELECT * FROM ( SELECT TOP 10 * FROM ( SELECT TOP 20 * FROM table1 ORDER BY column1 ) ORDER BY column1 DESC ) ORDER BY column1 Obviously the syntax is not entirely right, but it should help you get the general idea for a query that returns results 10-20. Ash www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Paging Help
?php
// Num of records each time
$NUM_RECORDS = 10;
if ($_GET[pag] == ) {
$ini = 0;
$pag = 1;
}
else {
$ini = ($pag - 1) * $NUM_RECORDS;
}
// Query to show
$query_rsData = SELECT * FROM table LIMIT . $ini . , . $NUM_RECORDS;
$rsData = mysql_query($query_rsData, $DB_CONECTION);
// Total records
$query_rsData2 = SELECT * FROM table;
$rsData2 = mysql_query($query_rsData2, $DB_CONECTION);
$num_total_records = mysql_num_rows($rsData2);
// Total pages
$total_pag = ceil($num_total_records / $NUM_RECORDS);
$previous_page = $pag - 1;
$next_page = $pag + 1;
?
Links for the pages
---
First page: this_page.php?pag=1
Previous page: this_page?pag=?php echo previous_page; ?
Next page: this_page?pag=?php echo next_page; ?
Last page: this_page?pag=?php echo total_pag; ?
Rodrigo de Oliveira Costa escribió:
I have the following problem, I need to make a paging system to show
results from search on a Mysql database. I found a class that
should do the job but couldn't figure it out how to use it. Does
anybody has an Idea of hos to do this? I thank in advance for any help
you guys can provide me.
Rodrigo
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Paging Help
Juanjo Pascual wrote:
?php
// Num of records each time
$NUM_RECORDS = 10;
if ($_GET[pag] == ) {
$ini = 0;
$pag = 1;
}
else {
$ini = ($pag - 1) * $NUM_RECORDS;
}
// Query to show
$query_rsData = SELECT * FROM table LIMIT . $ini . , . $NUM_RECORDS;
$rsData = mysql_query($query_rsData, $DB_CONECTION);
// Total records
$query_rsData2 = SELECT * FROM table;
$rsData2 = mysql_query($query_rsData2, $DB_CONECTION);
$num_total_records = mysql_num_rows($rsData2);
Since you query all enregs from table why not query all first and
the do mysql_data_seek on result?
// Query to show
$query_rsData = SELECT * FROM {table} ORDER BY {Whatever field};
$rsData = mysql_query($query_rsData, $DB_CONECTION);
$num_total_records = mysql_num_rows( $rsData );
$total_pag = ceil($num_total_records / $NUM_RECORDS);
if( $pag 1 )
$pag = 1;
elseif( $pag $total_pag )
$pag = $total_pag;
$start_enreg = ($pag-1) * $NUM_RECORDS;
@mysql_data_seek( $rsData, $start_enreg );
while( ($result = mysql_fetch_object( $rsData )) )
{
// display data...
}
// Total pages
$total_pag = ceil($num_total_records / $NUM_RECORDS);
$previous_page = $pag - 1;
$next_page = $pag + 1;
?
Links for the pages
---
First page: this_page.php?pag=1
Previous page: this_page?pag=?php echo previous_page; ?
Next page: this_page?pag=?php echo next_page; ?
Last page: this_page?pag=?php echo total_pag; ?
Rodrigo de Oliveira Costa escribió:
I have the following problem, I need to make a paging system to show
results from search on a Mysql database. I found a class that
should do the job but couldn't figure it out how to use it. Does
anybody has an Idea of hos to do this? I thank in advance for any help
you guys can provide me.
Rodrigo
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Paging Help
Andrei wrote:
Since you query all enregs from table why not query all first and
the do mysql_data_seek on result?
// Query to show
$query_rsData = SELECT * FROM {table} ORDER BY {Whatever field};
$rsData = mysql_query($query_rsData, $DB_CONECTION);
$num_total_records = mysql_num_rows( $rsData );
$total_pag = ceil($num_total_records / $NUM_RECORDS);
if( $pag 1 )
$pag = 1;
elseif( $pag $total_pag )
$pag = $total_pag;
$start_enreg = ($pag-1) * $NUM_RECORDS;
@mysql_data_seek( $rsData, $start_enreg );
while( ($result = mysql_fetch_object( $rsData )) )
{
// display data...
}
Querying all data in the first place is a waste, and the practice of
seeking through a dataset seems silly when MySQL has the LIMIT feature
to specify which data you want to return.
I believe the proper way to do this would be something like this
(Simplified example, of course):
$query = SELECT SQL_CALC_FOUND_FOWS * FROM foo LIMIT 30, 10;
$result = mysql_query($query);
$query = SELECT found_rows();
$num_rows = mysql_result(mysql_query($query), 0);
That should be much faster than the methods used by either of you guys.
As a comment, it would have been better to do SELECT COUNT(*) FROM foo
and reading the result rather than doing SELECT * FROM foo and then
mysql_num_rows(). Don't ask MySQL to collect data if you're not going to
use it! That actually applies to why you shouldn't use mysql_data_seek
either.
Regards, Adam.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] paging results in large resultsets: mysql vs postgresql?
At 11:11 AM -0500 6/15/06, D. Dante Lorenso wrote: I can't seem to find the equivalent of it in PostgreSQL! The only options I see are: 1. TWO queries. The first query will perform a SELECT COUNT(*) ...; and the second query performs the actualy SELECT ... LIMIT x OFFSET y; -snip- I hate having to write 2 queries to get one set of data ... especially when those queries start getting complex. I do exactly number 1 in http://ancientstones.com/catalog.php Keep in mind that the first query will tell you how many record are in that specific sort and then the second will present just those items. You have to work out the pageNumber, maxPageNumber, and numberOfPages and then it's simple. If you're concerned about the complexity of the query, then it makes sense to break it down to simpler steps. You should see the back-end of my site where if you are give a specific item, then where do you place it in a page given the user's search criteria. It made for an interesting exorcise. tedd -- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] paging results in large resultsets: mysql vs postgresql?
On Thu, June 15, 2006 11:11 am, D. Dante Lorenso wrote: I just discovered this neat little gem in MySQL which makes it easy to page large result sets: * SELECT SQL_CALC_FOUND_ROWS * * SELECT FOUND_ROWS() I can't seem to find the equivalent of it in PostgreSQL! The only options I see are: 2. Using PHP row seek and only selecting the number of rows I need. 3. use the built-in cursor of PostgreSQL which pre-dates MySQL LIMIT and OFFSET clauses, which are non-standard hacks Rasmus introduced back in the day. -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] paging results in large resultsets: mysql vs postgresql?
Richard Lynch wrote: 3. use the built-in cursor of PostgreSQL which pre-dates MySQL LIMIT and OFFSET clauses, which are non-standard hacks Rasmus introduced back in the day. Care to elaborate? Cast into context of PDO if you can...? Dante -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] paging results in large resultsets: mysql vs postgresql?
On Thu, June 15, 2006 7:15 pm, D. Dante Lorenso wrote: Richard Lynch wrote: 3. use the built-in cursor of PostgreSQL which pre-dates MySQL LIMIT and OFFSET clauses, which are non-standard hacks Rasmus introduced back in the day. Care to elaborate? Cast into context of PDO if you can...? I've forgotten the exact cursor syntax, but it's like: $query = declare cursor foo as select complicated query here; $foo = pg_exec($connection, $query); $query = select 10 from foo; $ten_rows = pg_exec($connection, $query); I have completely forgotten the cursor query to get the number of rows, but it's in the docs. You are so totally on your own with that bleeding-edge PDO [bleep]. Sorry. -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] paging methodology
On Tue, May 04, 2004 at 02:37:50PM -0700, Chris W. Parker wrote:
I don't follow what $_GET record count from initial query performed
above; is for, but that's the basic methodology.
well that just meant that after the initial count of records is found it
will be retrieved from the querystring instead of through a select
statement (because it had already been performed once before).
I never bother with getting the initial record count. Unless you want
to display the total number of available pages, of course (in the vein
of Google search result set).
If all you need is to include Previous and Next buttons in the right
places, you could simply go with:
$length=40; // or whatever
if ($_GET['offset'])
$offset=$_GET['offset']);
else
$offset=0;
// do the query
$q=SELECT colums FROM table WHERE yadda LIMIT $offset, . 1+$length;
$r=mysql_query($q);
// make Prev/Next links as required
if ($offset 0)
$prev=a href='?offset= . $offset-$length . ';
else
$prev=;
if (mysql_num_rows($r) $length)
$next=a href='?offset= . $offset+$length . ';
else
$next=;
// show the head
print td align=left$prev/td\n;
print td align=left$next/td\n;
print /trtr\n;
// show the page
for ($i=0; $i$length; $i++) {
$row=mysql_fetch_array($r);
print $row['blah']; // wrapped in stuff, of course
}
The idea here is that we always try to SELECT one more entry than we can
display on the page. If mysql_num_rows() sees that many rows, then
there's a page after the current one. And of course, if $offset is 0
then there's a page before the current one.
I find that this method simplifies my code. I don't need to store the
result of a COUNT() in a session variable, so the count remains
valid even if the number of records changes while someone's browsing.
Note that this is not safe code as is. :) At the very least, you
should format-check $_GET['offset'] before using it for anything.
p
--
Paul Chvostek [EMAIL PROTECTED]
it.canadahttp://www.it.ca/
Free PHP web hosting!http://www.it.ca/web/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] paging methodology
Chris W. Parker wrote: ok. this is what i was already doing in my previous app but i was just looking to see if it could be streamlined some how. You could perform the full (without LIMIT) query once, Cache the results array (using PEAR Cache) and move back-and-forth through this Cached array. Depending on the size of your resultset and the srength of your MySQL Server, this may or may not provide a mild performance boost. -brian -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] paging methodology
- Original Message - From: Paul Chvostek [EMAIL PROTECTED] On Tue, May 04, 2004 at 02:37:50PM -0700, Chris W. Parker wrote: well that just meant that after the initial count of records is found it will be retrieved from the querystring instead of through a select statement (because it had already been performed once before). I never bother with getting the initial record count. Unless you want to display the total number of available pages, of course (in the vein of Google search result set). Personal preference here, I guess, but I don't care for searches that don't tell you how many records were matched (or how many pages you'll have). I think the additional query is well worth it. The number of results tells me how successful my search was and whether I need to expand or shrink my search criteria. ---John Holmes... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] paging methodology
John W. Holmes mailto:[EMAIL PROTECTED] on Wednesday, May 05, 2004 7:32 AM said: Personal preference here, I guess, but I don't care for searches that don't tell you how many records were matched (or how many pages you'll have). I think the additional query is well worth it. The number of results tells me how successful my search was and whether I need to expand or shrink my search criteria. i agree. and as an update to this thread: i'm pretty much done with the whole paging thing. having written my code in a much OO manner than the previous incantation it was much easier to implement. in fact i only had to add a few lines to the actual page code. most of it went into the object itself. and you can sort on all the columns too. i still need to add some other things like text searching (right now it just returns all records) and the ability to adjust the number of records per page. but other than that i think i'm all set. chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] paging methodology
If you're using MySQL 4+ you could use the SQL_CALC_FOUND_ROWS flag in the select statement. It causes the query to count the number of rows it *would have* returned if there had been no limit clause. So after executing the query, you would then run another query to retrieve the number if found rows. Chris -Original Message- From: Chris W. Parker [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 05, 2004 9:33 AM To: [EMAIL PROTECTED] Subject: RE: [PHP] paging methodology John W. Holmes mailto:[EMAIL PROTECTED] on Wednesday, May 05, 2004 7:32 AM said: Personal preference here, I guess, but I don't care for searches that don't tell you how many records were matched (or how many pages you'll have). I think the additional query is well worth it. The number of results tells me how successful my search was and whether I need to expand or shrink my search criteria. i agree. and as an update to this thread: i'm pretty much done with the whole paging thing. having written my code in a much OO manner than the previous incantation it was much easier to implement. in fact i only had to add a few lines to the actual page code. most of it went into the object itself. and you can sort on all the columns too. i still need to add some other things like text searching (right now it just returns all records) and the ability to adjust the number of records per page. but other than that i think i'm all set. chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] paging methodology
Chris mailto:[EMAIL PROTECTED] on Wednesday, May 05, 2004 10:56 AM said: If you're using MySQL 4+ you could use the SQL_CALC_FOUND_ROWS flag in the select statement. It causes the query to count the number of rows it *would have* returned if there had been no limit clause. do you know if that's any faster than doing a SELECT COUNT(*) FROM table WHERE ... ? chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] paging methodology
It's certainly faster than SELECT COUNT(*) FROM sometable WHERE . . .; It might not be faster than SELECT COUNT(*) FROM sometable, as that is designed to run very quickly. The documentation for the FOUND_ROWS() function is here: http://dev.mysql.com/doc/mysql/en/Information_functions.html Chris -Original Message- From: Chris W. Parker [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 05, 2004 11:07 AM To: [EMAIL PROTECTED] Subject: RE: [PHP] paging methodology Chris mailto:[EMAIL PROTECTED] on Wednesday, May 05, 2004 10:56 AM said: If you're using MySQL 4+ you could use the SQL_CALC_FOUND_ROWS flag in the select statement. It causes the query to count the number of rows it *would have* returned if there had been no limit clause. do you know if that's any faster than doing a SELECT COUNT(*) FROM table WHERE ... ? chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] paging methodology
Chris mailto:[EMAIL PROTECTED] on Wednesday, May 05, 2004 11:24 AM said: The documentation for the FOUND_ROWS() function is here: http://dev.mysql.com/doc/mysql/en/Information_functions.html ahh... i see. so it essentially allows you to execute one query while achieving the same two query result. on a related note... how does BENCHMARK() work? i don't understand their explanation. the reason i ask is because i'd like to test our the efficiency of what you are suggesting compared to what i am currently doing. SELECT BENCHMARK(500, 'SELECT * FROM table'); although that didn't throw an error, it didn't seem to do anything either. thanks, chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] paging methodology
On Wed, 2004-05-05 at 12:23, Chris wrote: It's certainly faster than SELECT COUNT(*) FROM sometable WHERE . . .; It might not be faster than SELECT COUNT(*) FROM sometable, as that is designed to run very quickly. I believe it will also depend on the type of table you are using. SELECT COUNT(*) FROM sometable is much faster with MyISAM tables than with InnoDB tables because InnoDB tables need to actually count all the rows where MyISAM tables keep that information handy. Someone please correct me if I got that wrong. - Brad -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] paging methodology
From: Chris W. Parker [EMAIL PROTECTED] do you know if that's any faster than doing a SELECT COUNT(*) FROM table WHERE ... ? I did a couple (unscientific) tests and there doesn't seem to be much of a difference. I'd use the COUNT(*) method just because it's more portable, though. ---John Holmes... mysql select count(id) from status_history where date between 2004010100 AND 2004050100; +---+ | count(id) | +---+ | 27791 | +---+ 1 row in set (0.13 sec) mysql select * from status_history where date between 2004010100 AND 2004050100 LIMIT 2000,100; 100 rows in set (0.02 sec) mysql select SQL_CALC_FOUND_ROWS * from status_history where date between 2004010100 AND 2004050100 LIMIT 2000,100; 100 rows in set (0.14 sec) mysql select found_rows(); +--+ | found_rows() | +--+ |27791 | +--+ 1 row in set (0.00 sec) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] paging methodology
From: Chris W. Parker [EMAIL PROTECTED] on a related note... how does BENCHMARK() work? i don't understand their explanation. the reason i ask is because i'd like to test our the efficiency of what you are suggesting compared to what i am currently doing. SELECT BENCHMARK(500, 'SELECT * FROM table'); although that didn't throw an error, it didn't seem to do anything either. Getting OT here... From what I understand, you can't use complete queries in benchmark, only expressions. So, what you're actually benchmarking there is setting a string, I guess. You can use it for something like this, though: SELECT BENCHMARK(50,5 BETWEEN 0 AND 10); and... SELECT BENCHMARK(50,0 5 AND 5 10); to see which method MySQL handles faster (between benchmarks faster, although I've always been lead to believe the and method is faster). I don't know if i'd really interpret much from the results, though.. ---John Holmes... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] paging methodology
John W. Holmes mailto:[EMAIL PROTECTED] on Tuesday, May 04, 2004 1:30 PM said: I don't follow what $_GET record count from initial query performed above; is for, but that's the basic methodology. well that just meant that after the initial count of records is found it will be retrieved from the querystring instead of through a select statement (because it had already been performed once before). The only thing to note is that your first query should only use COUNT() and return the number, not actually return the entire result set. ok. this is what i was already doing in my previous app but i was just looking to see if it could be streamlined some how. With databases that do not support a LIMIT feature, then you will have to return the entire result set and use a seek() function to jump to the row you need (mssql_data_seek(), pg_data_seek(), etc). working with mysql so that's no problem. Oh, you'll want to use ?php instead of ?chris... ;) Or is this [EMAIL PROTECTED] i was working with the fabulous but little known chris scripting language. 'wget http://awesomewebsite.com.org/downloads/chris-1.0.rpm' thanks, chris. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Paging / Navigation Help
Ummm... because that's exactly what you're asking for. Look over your code
again. COUNT(*) returns one record in one column, one row.
---John Holmes...
- Original Message -
From: William Martell [EMAIL PROTECTED]
To: phplist [EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED] [EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Thursday, December 12, 2002 2:56 PM
Subject: [PHP] Paging / Navigation Help
Hello ALL.
Can anyone tell me why the number of rows is always 1 and count is the
total
number of records in my database.
Thanks in Advance,
William
[snip]
HTML
HEAD
TITLE/TITLE
/HEAD
BODY
?php
$db = mysql_connect(localhost, root);
mysql_select_db(dallas, $db);
$result = mysql_query(SELECT COUNT(*) as count FROM partscatalog having
count 0;,$db);
//get number of rows returned
$number_of_rows = mysql_num_rows($result);
if ($number_of_rows != '0')
{
while ($myrow = mysql_fetch_array($result))
{
echo \$number_of_rows is $number_of_rows;
echo count: .$myrow[count].br\n;
}
}
?
/body
/html
[/snip]
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] paging through results of mysql query
I know I'm trying to re-invent the wheel, but it is because I don't know how they do it. I set up a script to pull a ruleset from mysql and then loop through each row in the set. I then check each row as it loops until I get to the row number I want and start echoing rows. I create the row numbers by a $m=0; outside the loop; $m++; inside the loop. Then I stop echoing rows when it reaches 50 iterations. The total iterations achieved is passed on to the next script that does the same thing but starting where the total left off. This causes a lot of overhead as all of the resultset are pulled for each script. Does anyone have a magic bullet for this? This functionality is what I take search engines to be demonstrating. If anyone can help, I could wipe the sweat of my processors brow! (and mine for that matter) SELECT * FROM tbl WHERE ... LIMIT $start, $per_page; Adjust $start and $per_page accordingly to get your paging... ---John Holmes... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] paging through results of mysql query
Much Gratefulness on my part! Finally, my server and I can catch our breath. Larry S. Brown Dimension Networks, Inc. (727) 723-8388 -Original Message- From: John W. Holmes [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 05, 2002 12:47 PM To: 'Larry Brown'; 'PHP List' Subject: RE: [PHP] paging through results of mysql query I know I'm trying to re-invent the wheel, but it is because I don't know how they do it. I set up a script to pull a ruleset from mysql and then loop through each row in the set. I then check each row as it loops until I get to the row number I want and start echoing rows. I create the row numbers by a $m=0; outside the loop; $m++; inside the loop. Then I stop echoing rows when it reaches 50 iterations. The total iterations achieved is passed on to the next script that does the same thing but starting where the total left off. This causes a lot of overhead as all of the resultset are pulled for each script. Does anyone have a magic bullet for this? This functionality is what I take search engines to be demonstrating. If anyone can help, I could wipe the sweat of my processors brow! (and mine for that matter) SELECT * FROM tbl WHERE ... LIMIT $start, $per_page; Adjust $start and $per_page accordingly to get your paging... ---John Holmes... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Paging HOWTO for PHP n' MYSQL
Just pass the current position from one page to the next and use a LIMIT start,num clause. -Rasmus On Wed, 2 Oct 2002, Francisco Vaucher wrote: Hi people, i've been searching for a while in google and vivisimo for docs that treats paging techniques for php and mysql. And so far i seek nothing. I always get stuck with empty documents (if you know what I mean). Can U give me a hand with this. If you already have a doc of this nature, or the URL to get one ? Regards, and thanks in advance... Francisco M. Vaucher -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] paging MySQL layout
What kind of documents? If you are referring to Word or Excel documents, in most cases those applications determine the paging dynamically (usually by looking at the current assigned or default printer, fonts available, any font substitutions where not available, etc. Only permanent page ejects are the one's inserted by the author, assuming he bothered to do that. You should also be aware there is a size limitation on the mysql TEXT data type (I believe about 65K, MEDIUM TEXT and LONG TEXT are bigger), although I am not sure if they are supported by the mysql php api). To avoid all this, most developers store document files in a special directory (only accessible to the userid of the web site) with the unique filename stored in the db (perhaps with the original filename, which may not be unique). hope this helps, Warren Vail Tools, Metrics Quality Processes (415) 667-7814 Pager (877) 774-9891 215 Fremont 02-658 -Original Message- From: Juan Pablo Aqueveque [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 23, 2002 9:43 AM To: [EMAIL PROTECTED] Subject: [PHP] paging MySQL layout Hi everyone. I know, this is a off topic. My always nice boss wants to put a series of documents in our web site. I want to save myself I work and I want to put them in a DB. Somebody can help me with the layout of my DB?. I have some idea like this. create table documents ( id in(11) not null auto_increment primary key, document text default null) it is a very basic, simple idea (I want to show something links like Pag 1 | pag.2 | etc.) When it finishes, I will share the result. thanks! -Jp -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Paging through MySQL results
select * from some_table Limit 10,30; syntaxis limit start,offset Andrey Hristov IcyGEN Corporation Building Solutions On Friday 05 October 2001 18:52, you wrote: I am a bit new to PHP -- normally use ASP (no booing, please) -- and trying to find an elegant solution to paging x number of records at a time through a result set returned to a php page from MySQL. Any ideas? TIA, Bill -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Paging through MySQL results
- Original Message - From: Andrey Hristov [EMAIL PROTECTED] select * from some_table Limit 10,30; Something to point out, in some of the older versions of MySQL, Offset starts counting at 0, while in the newer versions, it starts counting at 1. You'll want to check the documentation for your version. Joel syntaxis limit start,offset Andrey Hristov IcyGEN Corporation Building Solutions On Friday 05 October 2001 18:52, you wrote: I am a bit new to PHP -- normally use ASP (no booing, please) -- and trying to find an elegant solution to paging x number of records at a time through a result set returned to a php page from MySQL. Any ideas? TIA, Bill -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Paging through MySQL results
Question: Does the pagination method using mysql_data_seek method pull in the entire result set, only to be used to display x number of rows? Joel Ricker [EMAIL PROTECTED] wrote in message 00b501c14db9$84e4a4f0$04a3d6d1@joeltklrijxxms">news:00b501c14db9$84e4a4f0$04a3d6d1@joeltklrijxxms... - Original Message - From: Andrey Hristov [EMAIL PROTECTED] select * from some_table Limit 10,30; Something to point out, in some of the older versions of MySQL, Offset starts counting at 0, while in the newer versions, it starts counting at 1. You'll want to check the documentation for your version. Joel syntaxis limit start,offset Andrey Hristov IcyGEN Corporation Building Solutions On Friday 05 October 2001 18:52, you wrote: I am a bit new to PHP -- normally use ASP (no booing, please) -- and trying to find an elegant solution to paging x number of records at a time through a result set returned to a php page from MySQL. Any ideas? TIA, Bill -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Paging a Recordset
you can use 'limit x,y' in MySql. James Crowley wrote: Hi, I want to spread the results of a recordset over a number of pages. In ASP, I would have opened a connection with a client-side cursor... but with PHP (using it's ODBC functions), it only seems to be able to execute an SQL statement, and return all the results... which isn't very efficient if I have 500 results with 10 per page! Also, how do I change the cursor location? Regards, - James Editor, VB Web == Web - http://www.vbweb.co.uk Email - [EMAIL PROTECTED] ICQ# - 60612011 == -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Paging a Recordset
Hello, James Crowley wrote: Hi, I want to spread the results of a recordset over a number of pages. In ASP, I would have opened a connection with a client-side cursor... but with PHP (using it's ODBC functions), it only seems to be able to execute an SQL statement, and return all the results... which isn't very efficient if I have 500 results with 10 per page! Also, how do I change the cursor location? You may want to look at Metabase, a PHP database abstraction package does that working similarly when using MySQL LIMIT clause, but it works with all supported databases including those using ODBC. It is as simple as calling the function MetabaseSetSelectedRowRange($database,$first,$limit) before each query. You may find Metabase here: http://phpclasses.UpperDesign.com/browse.html/package/20 You may also want to try this PHP Class that displays query results in HTML tables with links to go back and forth between pages. http://phpclasses.UpperDesign.com/browse.html/package/130 Manuel Lemos -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Paging results
On Thu, 15 Feb 2001 07:12, Randy Johnson wrote: Can somebody give me a url of an example or an example of how to page results so they can be view by clicking page 1 page 2 or back and next? results obtained from mysql database. Thanks Randy http://px.sklar.com/code.html?code_id=77 and leave out the require at the beginning. You'll need to hack it around a bit (lot?) to suit your circumstances, but the code to keep track of where you are is in there. -- David Robley| WEBMASTER Mail List Admin RESEARCH CENTRE FOR INJURY STUDIES | http://www.nisu.flinders.edu.au/ AusEinet| http://auseinet.flinders.edu.au/ Flinders University, ADELAIDE, SOUTH AUSTRALIA -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] Paging results
I guess there are good articles on this at phpbuilder.com http://www.phpbuilder.com/columns/rod20001214.php3 http://www.phpbuilder.com/columns/rod2221.php3 hope that helps best regards Ankur Verma HCL Technologies A1CD, Sec -16 Noida, UP India - Original Message - From: "Randy Johnson" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, February 15, 2001 2:12 AM Subject: [PHP] Paging results Can somebody give me a url of an example or an example of how to page results so they can be view by clicking page 1 page 2 or back and next? results obtained from mysql database. Thanks Randy -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
