Re: [PHP] Modifying the sort order of a query
Thanks Jay, Well, I went back to work on it, and with the combined suggestions from the list, it's working now. I just had to insert the sub-select on the called page as well. That seemed to be the problem. Thanks for your suggestion -- I used part of it. Regards, Andre On Thursday 06 June 2002 07:39 am, you wrote: > [snip] > I've just tried the sub-select approach. Works great on the first page - > shows names listed alphabetically sorted by country. However, once I click > on > 'Next 20' both sorts go bye-bye (neither by name nor country). > [/snip] > > I am late to the thread but I wanted to offer another suggestion. Retrieve > the data results into an array and sort the array. I like Peter's idea > though, using a case statement for the appropriate sort order information. > You could also set a variable ($lastSortOrder) to 'default', 'country', > 'city', 'whatever' and place this variable within the query. Pseudocode > follows; > > $lastSortOrder = "default" > > $query = "SELECT name, address, city, state, zip FROM table "; > $query .= "ORDER BY " . $lastSortOrder . "; > > > > > switch($action){ > case "Sort By Country": > $lastSortOrder = "country" > break; > case "Sort By City": > $lastSortOrder = "city" > break; > } > > I know this isn't complete but I HTH! > > Jay -- Please pray the Holy Rosary to end the holocaust of abortion. Remember in your prayers the Holy Souls in Purgatory. May God bless you abundantly in His love! For a free Cenacle Scriptural Rosary Booklet: http://www.webhart.net/csrb/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Modifying the sort order of a query
[snip] I've just tried the sub-select approach. Works great on the first page - shows names listed alphabetically sorted by country. However, once I click on 'Next 20' both sorts go bye-bye (neither by name nor country). [/snip] I am late to the thread but I wanted to offer another suggestion. Retrieve the data results into an array and sort the array. I like Peter's idea though, using a case statement for the appropriate sort order information. You could also set a variable ($lastSortOrder) to 'default', 'country', 'city', 'whatever' and place this variable within the query. Pseudocode follows; $lastSortOrder = "default" $query = "SELECT name, address, city, state, zip FROM table "; $query .= "ORDER BY " . $lastSortOrder . "; switch($action){ case "Sort By Country": $lastSortOrder = "country" break; case "Sort By City": $lastSortOrder = "city" break; } I know this isn't complete but I HTH! Jay -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Modifying the sort order of a query
Hi Peter, I've just tried the sub-select approach. Works great on the first page - shows names listed alphabetically sorted by country. However, once I click on 'Next 20' both sorts go bye-bye (neither by name nor country). Going to try what you suggest tomorrow. I think it'll work. Thanks for the advice, Regards, Andre On Wednesday 05 June 2002 11:37 pm, you wrote: > Andre > > Don't know how useful this will be to you .. but was following thru this > and thought why wouldn't you include a page with a switch statement or the > like into all the pages... so that all pages know about but not always use > the queries ... so that you can have it sorted by any and everything you > want... > > > any way just a thought > > > cheers > Peter > > -Original Message- > From: Andre Dubuc [mailto:[EMAIL PROTECTED]] > Sent: Thursday, 6 June 2002 1:27 PM > To: [EMAIL PROTECTED] > Subject: Re: [PHP] Modifying the sort order of a query > > > Hi John, > > Oh, my apologies again. I haven't thought this through enough. I'm sorry > that > I've wasted your time. > > >"Is it messing up when you click Next 20? Does it go > > back to the old sorting, or what? If you're looking at page 4 sorted by > > name, then you want to sort by country, you can't go back to page 4 . . > > ." > > Yes, that's what I was attempting to do. Perhaps I was too close to the > code to really visualize the basic flow. It went back to the first > page, and froze -- that's because the first page had no reference to the > "new" query -- it had no reference to a new query being made since "Page > 1" has it's own code/query set that did not include the restructuring of > the query that Page 4 attemped to do. So, I don't think it's possible to > accomplish an "in-process" sort without re-paginating the whole thing. I'll > probably have to do it ONLY on the first page, and not offer a continuous > option on every page. > > [Btw, nothing is public yet . . . I'm still putting the pieces together -- > hopefully sometime in September.] Further, I've changed most of the code > that I first wrote, attempting to tryout the concepts. > > Regards, > Andre > > On Wednesday 05 June 2002 11:01 pm, you wrote: > > I still really don't understand. You have a list of documents, 20 per > > page. You want to be able to sort it by country or another column, yet > > still display them in alphabetical order? > > > > Maybe an example page would help...is this page public anywhere? > > > > Like I said before, if you sort by new columns, then your paging has to > > start over back at the beginning. You said it already did this, but it > > still is messing up. Is it messing up when you click Next 20? Does it go > > back to the old sorting, or what? If you're looking at page 4 sorted by > > name, then you want to sort by country, you can't go back to page 4 > > (well, you can, but it's going to be a whole new page four because of > > the new sort order). > > > > Example page will really help here; maybe I'm just slow in visualizing > > this. We can take this off the list if necessary, too. > > > > ---John Holmes... > > > > > -Original Message- > > > From: Andre Dubuc [mailto:[EMAIL PROTECTED]] > > > Sent: Wednesday, June 05, 2002 10:49 PM > > > To: [EMAIL PROTECTED] > > > Cc: [EMAIL PROTECTED] > > > Subject: Re: [PHP] Modifying the sort order of a query > > > > > > Hi John, > > > > > > Sorry about that. I should have explained what I meant. The basic > > > > problem > > > > > that I'm trying (desparately) to skirt around is a troublesome paging > > > problem. > > > > > > The basic query loads all files in the db sorted by name. The first > > > > page > > > > > that > > > displays has a "Next 20" button that loads the next page, and so on > > > > until > > > > > the > > > last page which has no "Next 20" button, but deadends. > > > > > > The problem I've faced today, is that I originally allowed a "Sort by > > > Country" button to appear on each page -- thereby messing up the > > > > original > > > > > paging. It, of course, returned to the first page (which didn't know > > > > squat > > > > > about whatever the calling page asked for). Thus, the whole thing > > > > froze -- > > &g
RE: [PHP] Modifying the sort order of a query
Andre Don't know how useful this will be to you .. but was following thru this and thought why wouldn't you include a page with a switch statement or the like into all the pages... so that all pages know about but not always use the queries ... so that you can have it sorted by any and everything you want... any way just a thought cheers Peter -Original Message- From: Andre Dubuc [mailto:[EMAIL PROTECTED]] Sent: Thursday, 6 June 2002 1:27 PM To: [EMAIL PROTECTED] Subject: Re: [PHP] Modifying the sort order of a query Hi John, Oh, my apologies again. I haven't thought this through enough. I'm sorry that I've wasted your time. >"Is it messing up when you click Next 20? Does it go > back to the old sorting, or what? If you're looking at page 4 sorted by > name, then you want to sort by country, you can't go back to page 4 . . ." Yes, that's what I was attempting to do. Perhaps I was too close to the code to really visualize the basic flow. It went back to the first page, and froze -- that's because the first page had no reference to the "new" query -- it had no reference to a new query being made since "Page 1" has it's own code/query set that did not include the restructuring of the query that Page 4 attemped to do. So, I don't think it's possible to accomplish an "in-process" sort without re-paginating the whole thing. I'll probably have to do it ONLY on the first page, and not offer a continuous option on every page. [Btw, nothing is public yet . . . I'm still putting the pieces together -- hopefully sometime in September.] Further, I've changed most of the code that I first wrote, attempting to tryout the concepts. Regards, Andre On Wednesday 05 June 2002 11:01 pm, you wrote: > I still really don't understand. You have a list of documents, 20 per > page. You want to be able to sort it by country or another column, yet > still display them in alphabetical order? > > Maybe an example page would help...is this page public anywhere? > > Like I said before, if you sort by new columns, then your paging has to > start over back at the beginning. You said it already did this, but it > still is messing up. Is it messing up when you click Next 20? Does it go > back to the old sorting, or what? If you're looking at page 4 sorted by > name, then you want to sort by country, you can't go back to page 4 > (well, you can, but it's going to be a whole new page four because of > the new sort order). > > Example page will really help here; maybe I'm just slow in visualizing > this. We can take this off the list if necessary, too. > > ---John Holmes... > > > -----Original Message- > > From: Andre Dubuc [mailto:[EMAIL PROTECTED]] > > Sent: Wednesday, June 05, 2002 10:49 PM > > To: [EMAIL PROTECTED] > > Cc: [EMAIL PROTECTED] > > Subject: Re: [PHP] Modifying the sort order of a query > > > > Hi John, > > > > Sorry about that. I should have explained what I meant. The basic > > problem > > > that I'm trying (desparately) to skirt around is a troublesome paging > > problem. > > > > The basic query loads all files in the db sorted by name. The first > > page > > > that > > displays has a "Next 20" button that loads the next page, and so on > > until > > > the > > last page which has no "Next 20" button, but deadends. > > > > The problem I've faced today, is that I originally allowed a "Sort by > > Country" button to appear on each page -- thereby messing up the > > original > > > paging. It, of course, returned to the first page (which didn't know > > squat > > > about whatever the calling page asked for). Thus, the whole thing > > froze -- > > > which is to be expected. > > > > The resolution, in my mind, is simple: do not have that button > > anywhere > > > but > > on the first page (it took me a long time to figure that one out, duhh > > > > :>). > > > > The second solution, is somewhat better: using a sub-query, which will > > re-paginate all right, but I still need to retain the alpahbetical > > order > > > of > > names as well. Perhaps I can't have both. (I think I'll have to try > > both - > > > - > > my mind's a bit blitzed from the problems I've resolved today!) > > > > So, that's what I'm trying to do. The page I'm working on is a protype > > for > > > about three other similar ones. > > > > Regards, > > Andre > > > > On Wednesday 05 June 2002 10:22 pm, yo
Re: [PHP] Modifying the sort order of a query
Hi John, Oh, my apologies again. I haven't thought this through enough. I'm sorry that I've wasted your time. >"Is it messing up when you click Next 20? Does it go > back to the old sorting, or what? If you're looking at page 4 sorted by > name, then you want to sort by country, you can't go back to page 4 . . ." Yes, that's what I was attempting to do. Perhaps I was too close to the code to really visualize the basic flow. It went back to the first page, and froze -- that's because the first page had no reference to the "new" query -- it had no reference to a new query being made since "Page 1" has it's own code/query set that did not include the restructuring of the query that Page 4 attemped to do. So, I don't think it's possible to accomplish an "in-process" sort without re-paginating the whole thing. I'll probably have to do it ONLY on the first page, and not offer a continuous option on every page. [Btw, nothing is public yet . . . I'm still putting the pieces together -- hopefully sometime in September.] Further, I've changed most of the code that I first wrote, attempting to tryout the concepts. Regards, Andre On Wednesday 05 June 2002 11:01 pm, you wrote: > I still really don't understand. You have a list of documents, 20 per > page. You want to be able to sort it by country or another column, yet > still display them in alphabetical order? > > Maybe an example page would help...is this page public anywhere? > > Like I said before, if you sort by new columns, then your paging has to > start over back at the beginning. You said it already did this, but it > still is messing up. Is it messing up when you click Next 20? Does it go > back to the old sorting, or what? If you're looking at page 4 sorted by > name, then you want to sort by country, you can't go back to page 4 > (well, you can, but it's going to be a whole new page four because of > the new sort order). > > Example page will really help here; maybe I'm just slow in visualizing > this. We can take this off the list if necessary, too. > > ---John Holmes... > > > -----Original Message----- > > From: Andre Dubuc [mailto:[EMAIL PROTECTED]] > > Sent: Wednesday, June 05, 2002 10:49 PM > > To: [EMAIL PROTECTED] > > Cc: [EMAIL PROTECTED] > > Subject: Re: [PHP] Modifying the sort order of a query > > > > Hi John, > > > > Sorry about that. I should have explained what I meant. The basic > > problem > > > that I'm trying (desparately) to skirt around is a troublesome paging > > problem. > > > > The basic query loads all files in the db sorted by name. The first > > page > > > that > > displays has a "Next 20" button that loads the next page, and so on > > until > > > the > > last page which has no "Next 20" button, but deadends. > > > > The problem I've faced today, is that I originally allowed a "Sort by > > Country" button to appear on each page -- thereby messing up the > > original > > > paging. It, of course, returned to the first page (which didn't know > > squat > > > about whatever the calling page asked for). Thus, the whole thing > > froze -- > > > which is to be expected. > > > > The resolution, in my mind, is simple: do not have that button > > anywhere > > > but > > on the first page (it took me a long time to figure that one out, duhh > > > > :>). > > > > The second solution, is somewhat better: using a sub-query, which will > > re-paginate all right, but I still need to retain the alpahbetical > > order > > > of > > names as well. Perhaps I can't have both. (I think I'll have to try > > both - > > > - > > my mind's a bit blitzed from the problems I've resolved today!) > > > > So, that's what I'm trying to do. The page I'm working on is a protype > > for > > > about three other similar ones. > > > > Regards, > > Andre > > > > On Wednesday 05 June 2002 10:22 pm, you wrote: > > > What do you mean it'll "trash the current paging" ?? Can you give a > > more > > > > detailed explanation? > > > > > > Results 30-60 of one result set sorted by a certain column will be > > > totally different than results 30-60 of a result set sorted by a > > > different column. > > > > > > So the paging would have to start over, anyhow. Unless you're > > looking to > > > > maintain the same 30
RE: [PHP] Modifying the sort order of a query
isn't this just a matter of doing order by country, whatever ?? -Original Message- From: John Holmes [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 06, 2002 1:02 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: [PHP] Modifying the sort order of a query I still really don't understand. You have a list of documents, 20 per page. You want to be able to sort it by country or another column, yet still display them in alphabetical order? Maybe an example page would help...is this page public anywhere? Like I said before, if you sort by new columns, then your paging has to start over back at the beginning. You said it already did this, but it still is messing up. Is it messing up when you click Next 20? Does it go back to the old sorting, or what? If you're looking at page 4 sorted by name, then you want to sort by country, you can't go back to page 4 (well, you can, but it's going to be a whole new page four because of the new sort order). Example page will really help here; maybe I'm just slow in visualizing this. We can take this off the list if necessary, too. ---John Holmes... > -Original Message- > From: Andre Dubuc [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, June 05, 2002 10:49 PM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: Re: [PHP] Modifying the sort order of a query > > Hi John, > > Sorry about that. I should have explained what I meant. The basic problem > that I'm trying (desparately) to skirt around is a troublesome paging > problem. > > The basic query loads all files in the db sorted by name. The first page > that > displays has a "Next 20" button that loads the next page, and so on until > the > last page which has no "Next 20" button, but deadends. > > The problem I've faced today, is that I originally allowed a "Sort by > Country" button to appear on each page -- thereby messing up the original > paging. It, of course, returned to the first page (which didn't know squat > about whatever the calling page asked for). Thus, the whole thing froze -- > which is to be expected. > > The resolution, in my mind, is simple: do not have that button anywhere > but > on the first page (it took me a long time to figure that one out, duhh > :>). > The second solution, is somewhat better: using a sub-query, which will > re-paginate all right, but I still need to retain the alpahbetical order > of > names as well. Perhaps I can't have both. (I think I'll have to try both - > - > my mind's a bit blitzed from the problems I've resolved today!) > > So, that's what I'm trying to do. The page I'm working on is a protype for > about three other similar ones. > > Regards, > Andre > > > > On Wednesday 05 June 2002 10:22 pm, you wrote: > > What do you mean it'll "trash the current paging" ?? Can you give a more > > detailed explanation? > > > > Results 30-60 of one result set sorted by a certain column will be > > totally different than results 30-60 of a result set sorted by a > > different column. > > > > So the paging would have to start over, anyhow. Unless you're looking to > > maintain the same 30 results in the new result set, like my previous > > email mentioned... > > > > ---John Holmes... > > > > > -Original Message- > > > From: Andre Dubuc [mailto:[EMAIL PROTECTED]] > > > Sent: Wednesday, June 05, 2002 10:19 PM > > > To: Miguel Cruz > > > Cc: [EMAIL PROTECTED] > > > Subject: Re: [PHP] Modifying the sort order of a query > > > > > > Thanks Miguel, > > > > > > I haven't tried the sub-query approach yet, but that does seem to be > > > > the > > > > > way > > > to do this. My fear is that it will trash the current paging (which > > > > took > > > > > forever to get working with the current setup). Actually, after trying > > > > my > > > > > original code again in another smaller select function, I'm wondering > > > whether > > > I even need this refined search option -- it might be a case of > > > 'overkill'. > > > Still, it's got my curiosity going. > > > > > > The problem seems to be centered on how I wrote the original code > > > > (it's > > > > > amzing how it's changed!). I think I'll have to re-write using switch > > > functions to clarify exactly what I want it to do. > > > > > > Regards, > > > Andre > > > > > > On Wednesday 05 June 2002 09:43 pm,
RE: [PHP] Modifying the sort order of a query
I still really don't understand. You have a list of documents, 20 per page. You want to be able to sort it by country or another column, yet still display them in alphabetical order? Maybe an example page would help...is this page public anywhere? Like I said before, if you sort by new columns, then your paging has to start over back at the beginning. You said it already did this, but it still is messing up. Is it messing up when you click Next 20? Does it go back to the old sorting, or what? If you're looking at page 4 sorted by name, then you want to sort by country, you can't go back to page 4 (well, you can, but it's going to be a whole new page four because of the new sort order). Example page will really help here; maybe I'm just slow in visualizing this. We can take this off the list if necessary, too. ---John Holmes... > -Original Message- > From: Andre Dubuc [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, June 05, 2002 10:49 PM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: Re: [PHP] Modifying the sort order of a query > > Hi John, > > Sorry about that. I should have explained what I meant. The basic problem > that I'm trying (desparately) to skirt around is a troublesome paging > problem. > > The basic query loads all files in the db sorted by name. The first page > that > displays has a "Next 20" button that loads the next page, and so on until > the > last page which has no "Next 20" button, but deadends. > > The problem I've faced today, is that I originally allowed a "Sort by > Country" button to appear on each page -- thereby messing up the original > paging. It, of course, returned to the first page (which didn't know squat > about whatever the calling page asked for). Thus, the whole thing froze -- > which is to be expected. > > The resolution, in my mind, is simple: do not have that button anywhere > but > on the first page (it took me a long time to figure that one out, duhh > :>). > The second solution, is somewhat better: using a sub-query, which will > re-paginate all right, but I still need to retain the alpahbetical order > of > names as well. Perhaps I can't have both. (I think I'll have to try both - > - > my mind's a bit blitzed from the problems I've resolved today!) > > So, that's what I'm trying to do. The page I'm working on is a protype for > about three other similar ones. > > Regards, > Andre > > > > On Wednesday 05 June 2002 10:22 pm, you wrote: > > What do you mean it'll "trash the current paging" ?? Can you give a more > > detailed explanation? > > > > Results 30-60 of one result set sorted by a certain column will be > > totally different than results 30-60 of a result set sorted by a > > different column. > > > > So the paging would have to start over, anyhow. Unless you're looking to > > maintain the same 30 results in the new result set, like my previous > > email mentioned... > > > > ---John Holmes... > > > > > -Original Message- > > > From: Andre Dubuc [mailto:[EMAIL PROTECTED]] > > > Sent: Wednesday, June 05, 2002 10:19 PM > > > To: Miguel Cruz > > > Cc: [EMAIL PROTECTED] > > > Subject: Re: [PHP] Modifying the sort order of a query > > > > > > Thanks Miguel, > > > > > > I haven't tried the sub-query approach yet, but that does seem to be > > > > the > > > > > way > > > to do this. My fear is that it will trash the current paging (which > > > > took > > > > > forever to get working with the current setup). Actually, after trying > > > > my > > > > > original code again in another smaller select function, I'm wondering > > > whether > > > I even need this refined search option -- it might be a case of > > > 'overkill'. > > > Still, it's got my curiosity going. > > > > > > The problem seems to be centered on how I wrote the original code > > > > (it's > > > > > amzing how it's changed!). I think I'll have to re-write using switch > > > functions to clarify exactly what I want it to do. > > > > > > Regards, > > > Andre > > > > > > On Wednesday 05 June 2002 09:43 pm, you wrote: > > > > My understanding was that he wanted to see the same 30 rows, but > > > > sorted > > > > > in > > > > > > > a different way. > > > > > > > > For instance, he wanted to see en
Re: [PHP] Modifying the sort order of a query
Hi John, Sorry about that. I should have explained what I meant. The basic problem that I'm trying (desparately) to skirt around is a troublesome paging problem. The basic query loads all files in the db sorted by name. The first page that displays has a "Next 20" button that loads the next page, and so on until the last page which has no "Next 20" button, but deadends. The problem I've faced today, is that I originally allowed a "Sort by Country" button to appear on each page -- thereby messing up the original paging. It, of course, returned to the first page (which didn't know squat about whatever the calling page asked for). Thus, the whole thing froze -- which is to be expected. The resolution, in my mind, is simple: do not have that button anywhere but on the first page (it took me a long time to figure that one out, duhh :>). The second solution, is somewhat better: using a sub-query, which will re-paginate all right, but I still need to retain the alpahbetical order of names as well. Perhaps I can't have both. (I think I'll have to try both -- my mind's a bit blitzed from the problems I've resolved today!) So, that's what I'm trying to do. The page I'm working on is a protype for about three other similar ones. Regards, Andre On Wednesday 05 June 2002 10:22 pm, you wrote: > What do you mean it'll "trash the current paging" ?? Can you give a more > detailed explanation? > > Results 30-60 of one result set sorted by a certain column will be > totally different than results 30-60 of a result set sorted by a > different column. > > So the paging would have to start over, anyhow. Unless you're looking to > maintain the same 30 results in the new result set, like my previous > email mentioned... > > ---John Holmes... > > > -Original Message- > > From: Andre Dubuc [mailto:[EMAIL PROTECTED]] > > Sent: Wednesday, June 05, 2002 10:19 PM > > To: Miguel Cruz > > Cc: [EMAIL PROTECTED] > > Subject: Re: [PHP] Modifying the sort order of a query > > > > Thanks Miguel, > > > > I haven't tried the sub-query approach yet, but that does seem to be > > the > > > way > > to do this. My fear is that it will trash the current paging (which > > took > > > forever to get working with the current setup). Actually, after trying > > my > > > original code again in another smaller select function, I'm wondering > > whether > > I even need this refined search option -- it might be a case of > > 'overkill'. > > Still, it's got my curiosity going. > > > > The problem seems to be centered on how I wrote the original code > > (it's > > > amzing how it's changed!). I think I'll have to re-write using switch > > functions to clarify exactly what I want it to do. > > > > Regards, > > Andre > > > > On Wednesday 05 June 2002 09:43 pm, you wrote: > > > My understanding was that he wanted to see the same 30 rows, but > > sorted > > > in > > > > > a different way. > > > > > > For instance, he wanted to see entries 30-60 as sorted by age, but > > to > > > have > > > > > those sorted by height when displayed. > > > > > > miguel > > > > > > On Thu, 6 Jun 2002, Bogdan Stancescu wrote: > > > > That's at least curious - limiting and offsetting will most > > certainly > > > > > affect the results which are then sorted... I don't think that's > > what > > > he > > > > > > was after. Just my 2c. > > > > > > > > Bogdan > > > > > > > > Miguel Cruz wrote: > > > > >Try a sub-select: > > > > > > > > > >SELECT * FROM (SELECT * FROM rap ORDER BY > > > > rcountry,rcity,rsname,rfname > > > > > > >DESC LIMIT 30 OFFSET 30) ORDER BY whatever; > > > > > > > > > >miguel > > > > > > > > > >On Wed, 5 Jun 2002, Andre Dubuc wrote: > > > > >>Apache 1.3.23 + PHP 4.1.2 + PostgreSQL 7.2 > > > > >> > > > > >>I have a query that sorts by name, country, and city, then pages > > in > > > > > >> groups of 30 records. Originally, I had also coded two buttons: > > > > "Sort > > > > > > >> by Country', 'Sort by City' since I wanted to offer users the > > > > options > > > > > > >> of these choices. > > > >
Re: [PHP] Modifying the sort order of a query
Thanks John, That looks promising, but the question remains -- would it affect the current paging? I really don't wnat to spend another day getting that going again! :> Further, the actual ID numbers that would populate 'display_ids' . . . 'would be generated from your result set' -- I'm not clear how to achieve this. From the query results, how would I extract that info, for the current page, if my id column is 'rid', and I'm using $_SESSION['rid']? Regards, Andre On Wednesday 05 June 2002 10:15 pm, you wrote: > > My understanding was that he wanted to see the same 30 rows, but > > sorted in > > > a different way. > > > > For instance, he wanted to see entries 30-60 as sorted by age, but to > > have > > > those sorted by height when displayed. > > > > miguel > > Maybe you could have a hidden field that lists the 30 IDs that are > displayed on the page, as a comma separated list. Then, if the user > chooses to resort those 30 results, based on another column, you can use > that hidden field in your query to limit the IDs. > > i.e. > > > > Where the actual numbers would be generated from your result set. > > Then, use those numbers in your query. > > SELECT * FROM table WHERE ... AND ID IN($display_ids) ORDER BY > <> > > Hopefully PG supports IN(). > > My $0.02, I'm sure there are other ways...or maybe this isn't even what > you're looking for. :) > > ---John Holmes... > > > On Thu, 6 Jun 2002, Bogdan Stancescu wrote: > > > That's at least curious - limiting and offsetting will most > > certainly > > > > affect the results which are then sorted... I don't think that's > > what he > > > > was after. Just my 2c. > > > > > > Bogdan > > > > > > Miguel Cruz wrote: > > > >Try a sub-select: > > > > > > > >SELECT * FROM (SELECT * FROM rap ORDER BY > > rcountry,rcity,rsname,rfname > > > > >DESC LIMIT 30 OFFSET 30) ORDER BY whatever; > > > > > > > >miguel > > > > > > > >On Wed, 5 Jun 2002, Andre Dubuc wrote: > > > >>Apache 1.3.23 + PHP 4.1.2 + PostgreSQL 7.2 > > > >> > > > >>I have a query that sorts by name, country, and city, then pages > > in > > > groups of > > > > > >>30 records. Originally, I had also coded two buttons: "Sort by > > > > Country', > > > > > >>'Sort by City' since I wanted to offer users the options of these > > > > choices. > > > > > >>Unfortunately, these buttons work well, but re-select the whole > > > > shebang > > > > > >>(which I guess is to be expected), destroying any paging that had > > > > already > > > > > >>started. I've tried all sorts of ways, tried re-arranging the > > order of > > > > >>execution, but the result is the same. > > > >> > > > >>I'm at a loss on how to proceed. I've deleted the choice (for now) > > and > > > run it > > > > > >>as a simple select order by name query.The problem seems to be: I > > need > > > a > > > > > >>sub-query select function that retains the original query, simply > > > >>re-organizing it according to the new criteria, and retains the > > > > original > > > > > >>paging. (Perhaps I shouldn't care whether the paging is messed up > > as > > > long as > > > > > >>the results are the same.) > > > >> > > > >>I realize that the way it's set up below, it will automatically > > send a > > > NEW > > > > > >>query, which is not what I want. I'd like to work with the results > > of > > > the > > > > > >>original query and modify it with the new criteria > > > >> > > > >>Any suggestions how I can achieve this, or whether it's even > > possible? > > > I > > > > > >>would greatly appreciate any assistance or comments. > >> > >>** > > ** > > > ** > > > > > >>Snippet of offending code: > > > >> > > > >> > > >> > > > >>//snippet follows: > > > >> > > > >>print ""; > > > >>print " "; > > > >>print ""; > > > > >>// more code > > > >> > > > >>if($_POST['submit'] == "Sort by Name"){ > > > >>$query = "SELECT * FROM rap ORDER BY > > > >>rsname,rfname,rcountry,rcity,DESC LIMIT 30 OFFSET 30"; > > > >>} > > > >>elseif($_POST['submit'] == "Sort by Country"){ > > > >>$query = "SELECT * FROM rap ORDER BY > > > >>rcountry,rcity,rsname,rfname DESC LIMIT 30 OFFSET 30"; > > > >>} > > > >> > > > >>//more code, including paging functions > > > >>?> > >> > >>** > > ** > > > > > > > > >>Tia, > > > >>Andre > > > > -- > > PHP General Mailing List (http://www.php.net/) > > To unsubscribe, visit: http://www.php.net/unsub.php -- Please pray the Holy Rosary to end the holocaust of abortion. Remember in your prayers the Holy Souls in Purgatory. May God bless you abundantly in His love! For a free Cenacle Scriptural Rosary Booklet: http://www.webhart.net/csrb/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Modifying the sort order of a query
What do you mean it'll "trash the current paging" ?? Can you give a more detailed explanation? Results 30-60 of one result set sorted by a certain column will be totally different than results 30-60 of a result set sorted by a different column. So the paging would have to start over, anyhow. Unless you're looking to maintain the same 30 results in the new result set, like my previous email mentioned... ---John Holmes... > -Original Message- > From: Andre Dubuc [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, June 05, 2002 10:19 PM > To: Miguel Cruz > Cc: [EMAIL PROTECTED] > Subject: Re: [PHP] Modifying the sort order of a query > > Thanks Miguel, > > I haven't tried the sub-query approach yet, but that does seem to be the > way > to do this. My fear is that it will trash the current paging (which took > forever to get working with the current setup). Actually, after trying my > original code again in another smaller select function, I'm wondering > whether > I even need this refined search option -- it might be a case of > 'overkill'. > Still, it's got my curiosity going. > > The problem seems to be centered on how I wrote the original code (it's > amzing how it's changed!). I think I'll have to re-write using switch > functions to clarify exactly what I want it to do. > > Regards, > Andre > > On Wednesday 05 June 2002 09:43 pm, you wrote: > > My understanding was that he wanted to see the same 30 rows, but sorted > in > > a different way. > > > > For instance, he wanted to see entries 30-60 as sorted by age, but to > have > > those sorted by height when displayed. > > > > miguel > > > > On Thu, 6 Jun 2002, Bogdan Stancescu wrote: > > > That's at least curious - limiting and offsetting will most certainly > > > affect the results which are then sorted... I don't think that's what > he > > > was after. Just my 2c. > > > > > > Bogdan > > > > > > Miguel Cruz wrote: > > > >Try a sub-select: > > > > > > > >SELECT * FROM (SELECT * FROM rap ORDER BY > rcountry,rcity,rsname,rfname > > > >DESC LIMIT 30 OFFSET 30) ORDER BY whatever; > > > > > > > >miguel > > > > > > > >On Wed, 5 Jun 2002, Andre Dubuc wrote: > > > >>Apache 1.3.23 + PHP 4.1.2 + PostgreSQL 7.2 > > > >> > > > >>I have a query that sorts by name, country, and city, then pages in > > > >> groups of 30 records. Originally, I had also coded two buttons: > "Sort > > > >> by Country', 'Sort by City' since I wanted to offer users the > options > > > >> of these choices. > > > >> > > > >>Unfortunately, these buttons work well, but re-select the whole > shebang > > > >>(which I guess is to be expected), destroying any paging that had > > > >> already started. I've tried all sorts of ways, tried re-arranging > the > > > >> order of execution, but the result is the same. > > > >> > > > >>I'm at a loss on how to proceed. I've deleted the choice (for now) > and > > > >> run it as a simple select order by name query.The problem seems to > be: > > > >> I need a sub-query select function that retains the original query, > > > >> simply re-organizing it according to the new criteria, and retains > the > > > >> original paging. (Perhaps I shouldn't care whether the paging is > > > >> messed up as long as the results are the same.) > > > >> > > > >>I realize that the way it's set up below, it will automatically send > a > > > >> NEW query, which is not what I want. I'd like to work with the > results > > > >> of the original query and modify it with the new criteria > > > >> > > > >>Any suggestions how I can achieve this, or whether it's even > possible? > > > >> I would greatly appreciate any assistance or comments. > > > >> > > > > >>** * > > > >>*** > > > >> > > > >>Snippet of offending code: > > > >> > > > >> > > >> > > > >>//snippet follows: > > > >> > > > >>print ""; > > > >>print " "; > > > >>print ""; > > >
Re: [PHP] Modifying the sort order of a query
Thanks Miguel, I haven't tried the sub-query approach yet, but that does seem to be the way to do this. My fear is that it will trash the current paging (which took forever to get working with the current setup). Actually, after trying my original code again in another smaller select function, I'm wondering whether I even need this refined search option -- it might be a case of 'overkill'. Still, it's got my curiosity going. The problem seems to be centered on how I wrote the original code (it's amzing how it's changed!). I think I'll have to re-write using switch functions to clarify exactly what I want it to do. Regards, Andre On Wednesday 05 June 2002 09:43 pm, you wrote: > My understanding was that he wanted to see the same 30 rows, but sorted in > a different way. > > For instance, he wanted to see entries 30-60 as sorted by age, but to have > those sorted by height when displayed. > > miguel > > On Thu, 6 Jun 2002, Bogdan Stancescu wrote: > > That's at least curious - limiting and offsetting will most certainly > > affect the results which are then sorted... I don't think that's what he > > was after. Just my 2c. > > > > Bogdan > > > > Miguel Cruz wrote: > > >Try a sub-select: > > > > > >SELECT * FROM (SELECT * FROM rap ORDER BY rcountry,rcity,rsname,rfname > > >DESC LIMIT 30 OFFSET 30) ORDER BY whatever; > > > > > >miguel > > > > > >On Wed, 5 Jun 2002, Andre Dubuc wrote: > > >>Apache 1.3.23 + PHP 4.1.2 + PostgreSQL 7.2 > > >> > > >>I have a query that sorts by name, country, and city, then pages in > > >> groups of 30 records. Originally, I had also coded two buttons: "Sort > > >> by Country', 'Sort by City' since I wanted to offer users the options > > >> of these choices. > > >> > > >>Unfortunately, these buttons work well, but re-select the whole shebang > > >>(which I guess is to be expected), destroying any paging that had > > >> already started. I've tried all sorts of ways, tried re-arranging the > > >> order of execution, but the result is the same. > > >> > > >>I'm at a loss on how to proceed. I've deleted the choice (for now) and > > >> run it as a simple select order by name query.The problem seems to be: > > >> I need a sub-query select function that retains the original query, > > >> simply re-organizing it according to the new criteria, and retains the > > >> original paging. (Perhaps I shouldn't care whether the paging is > > >> messed up as long as the results are the same.) > > >> > > >>I realize that the way it's set up below, it will automatically send a > > >> NEW query, which is not what I want. I'd like to work with the results > > >> of the original query and modify it with the new criteria > > >> > > >>Any suggestions how I can achieve this, or whether it's even possible? > > >> I would greatly appreciate any assistance or comments. > > >> > > >>*** > > >>*** > > >> > > >>Snippet of offending code: > > >> > > >> > >> > > >>//snippet follows: > > >> > > >>print ""; > > >>print " "; > > >>print ""; > > >> > > >>// more code > > >> > > >>if($_POST['submit'] == "Sort by Name"){ > > >> $query = "SELECT * FROM rap ORDER BY > > >> rsname,rfname,rcountry,rcity,DESC LIMIT 30 OFFSET 30"; > > >>} > > >>elseif($_POST['submit'] == "Sort by Country"){ > > >> $query = "SELECT * FROM rap ORDER BY > > >> rcountry,rcity,rsname,rfname DESC LIMIT 30 OFFSET 30"; > > >>} > > >> > > >>//more code, including paging functions > > >>?> > > >> > > >>*** > > >>* > > >> > > >>Tia, > > >>Andre -- Please pray the Holy Rosary to end the holocaust of abortion. Remember in your prayers the Holy Souls in Purgatory. May God bless you abundantly in His love! For a free Cenacle Scriptural Rosary Booklet: http://www.webhart.net/csrb/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Modifying the sort order of a query
> My understanding was that he wanted to see the same 30 rows, but sorted in > a different way. > > For instance, he wanted to see entries 30-60 as sorted by age, but to have > those sorted by height when displayed. > > miguel Maybe you could have a hidden field that lists the 30 IDs that are displayed on the page, as a comma separated list. Then, if the user chooses to resort those 30 results, based on another column, you can use that hidden field in your query to limit the IDs. i.e. Where the actual numbers would be generated from your result set. Then, use those numbers in your query. SELECT * FROM table WHERE ... AND ID IN($display_ids) ORDER BY <> Hopefully PG supports IN(). My $0.02, I'm sure there are other ways...or maybe this isn't even what you're looking for. :) ---John Holmes... > On Thu, 6 Jun 2002, Bogdan Stancescu wrote: > > That's at least curious - limiting and offsetting will most certainly > > affect the results which are then sorted... I don't think that's what he > > was after. Just my 2c. > > > > Bogdan > > > > Miguel Cruz wrote: > > > > >Try a sub-select: > > > > > >SELECT * FROM (SELECT * FROM rap ORDER BY rcountry,rcity,rsname,rfname > > >DESC LIMIT 30 OFFSET 30) ORDER BY whatever; > > > > > >miguel > > > > > >On Wed, 5 Jun 2002, Andre Dubuc wrote: > > > > > > > > >>Apache 1.3.23 + PHP 4.1.2 + PostgreSQL 7.2 > > >> > > >>I have a query that sorts by name, country, and city, then pages in > groups of > > >>30 records. Originally, I had also coded two buttons: "Sort by > Country', > > >>'Sort by City' since I wanted to offer users the options of these > choices. > > >> > > >>Unfortunately, these buttons work well, but re-select the whole > shebang > > >>(which I guess is to be expected), destroying any paging that had > already > > >>started. I've tried all sorts of ways, tried re-arranging the order of > > >>execution, but the result is the same. > > >> > > >>I'm at a loss on how to proceed. I've deleted the choice (for now) and > run it > > >>as a simple select order by name query.The problem seems to be: I need > a > > >>sub-query select function that retains the original query, simply > > >>re-organizing it according to the new criteria, and retains the > original > > >>paging. (Perhaps I shouldn't care whether the paging is messed up as > long as > > >>the results are the same.) > > >> > > >>I realize that the way it's set up below, it will automatically send a > NEW > > >>query, which is not what I want. I'd like to work with the results of > the > > >>original query and modify it with the new criteria > > >> > > >>Any suggestions how I can achieve this, or whether it's even possible? > I > > >>would greatly appreciate any assistance or comments. > > >> > > > >>** ** > ** > > >> > > >>Snippet of offending code: > > >> > > >> > >> > > >>//snippet follows: > > >> > > >>print ""; > > >>print " "; > > >>print ""; > > >> > > >>// more code > > >> > > >>if($_POST['submit'] == "Sort by Name"){ > > >> $query = "SELECT * FROM rap ORDER BY > > >> rsname,rfname,rcountry,rcity,DESC LIMIT 30 OFFSET 30"; > > >>} > > >>elseif($_POST['submit'] == "Sort by Country"){ > > >> $query = "SELECT * FROM rap ORDER BY > > >> rcountry,rcity,rsname,rfname DESC LIMIT 30 OFFSET 30"; > > >>} > > >> > > >>//more code, including paging functions > > >>?> > > >> > > > >>** ** > > > >> > > >>Tia, > > >>Andre > > >> > > >> > > >> > > >> > > >> > > > > > > > > > > > > > > > > > > > > > > > > > -- > 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] Modifying the sort order of a query
My understanding was that he wanted to see the same 30 rows, but sorted in a different way. For instance, he wanted to see entries 30-60 as sorted by age, but to have those sorted by height when displayed. miguel On Thu, 6 Jun 2002, Bogdan Stancescu wrote: > That's at least curious - limiting and offsetting will most certainly > affect the results which are then sorted... I don't think that's what he > was after. Just my 2c. > > Bogdan > > Miguel Cruz wrote: > > >Try a sub-select: > > > >SELECT * FROM (SELECT * FROM rap ORDER BY rcountry,rcity,rsname,rfname > >DESC LIMIT 30 OFFSET 30) ORDER BY whatever; > > > >miguel > > > >On Wed, 5 Jun 2002, Andre Dubuc wrote: > > > > > >>Apache 1.3.23 + PHP 4.1.2 + PostgreSQL 7.2 > >> > >>I have a query that sorts by name, country, and city, then pages in groups of > >>30 records. Originally, I had also coded two buttons: "Sort by Country', > >>'Sort by City' since I wanted to offer users the options of these choices. > >> > >>Unfortunately, these buttons work well, but re-select the whole shebang > >>(which I guess is to be expected), destroying any paging that had already > >>started. I've tried all sorts of ways, tried re-arranging the order of > >>execution, but the result is the same. > >> > >>I'm at a loss on how to proceed. I've deleted the choice (for now) and run it > >>as a simple select order by name query.The problem seems to be: I need a > >>sub-query select function that retains the original query, simply > >>re-organizing it according to the new criteria, and retains the original > >>paging. (Perhaps I shouldn't care whether the paging is messed up as long as > >>the results are the same.) > >> > >>I realize that the way it's set up below, it will automatically send a NEW > >>query, which is not what I want. I'd like to work with the results of the > >>original query and modify it with the new criteria > >> > >>Any suggestions how I can achieve this, or whether it's even possible? I > >>would greatly appreciate any assistance or comments. > >> > >>>** > >> > >>Snippet of offending code: > >> > >> >> > >>//snippet follows: > >> > >>print ""; > >>print " "; > >>print ""; > >> > >>// more code > >> > >>if($_POST['submit'] == "Sort by Name"){ > >>$query = "SELECT * FROM rap ORDER BY > >>rsname,rfname,rcountry,rcity,DESC LIMIT 30 OFFSET 30"; > >>} > >>elseif($_POST['submit'] == "Sort by Country"){ > >>$query = "SELECT * FROM rap ORDER BY > >>rcountry,rcity,rsname,rfname DESC LIMIT 30 OFFSET 30"; > >>} > >> > >>//more code, including paging functions > >>?> > >> > >>> > >> > >>Tia, > >>Andre > >> > >> > >> > >> > >> > > > > > > > > > > > > > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Modifying the sort order of a query
That's at least curious - limiting and offsetting will most certainly affect the results which are then sorted... I don't think that's what he was after. Just my 2c. Bogdan Miguel Cruz wrote: >Try a sub-select: > >SELECT * FROM (SELECT * FROM rap ORDER BY rcountry,rcity,rsname,rfname >DESC LIMIT 30 OFFSET 30) ORDER BY whatever; > >miguel > >On Wed, 5 Jun 2002, Andre Dubuc wrote: > > >>Apache 1.3.23 + PHP 4.1.2 + PostgreSQL 7.2 >> >>I have a query that sorts by name, country, and city, then pages in groups of >>30 records. Originally, I had also coded two buttons: "Sort by Country', >>'Sort by City' since I wanted to offer users the options of these choices. >> >>Unfortunately, these buttons work well, but re-select the whole shebang >>(which I guess is to be expected), destroying any paging that had already >>started. I've tried all sorts of ways, tried re-arranging the order of >>execution, but the result is the same. >> >>I'm at a loss on how to proceed. I've deleted the choice (for now) and run it >>as a simple select order by name query.The problem seems to be: I need a >>sub-query select function that retains the original query, simply >>re-organizing it according to the new criteria, and retains the original >>paging. (Perhaps I shouldn't care whether the paging is messed up as long as >>the results are the same.) >> >>I realize that the way it's set up below, it will automatically send a NEW >>query, which is not what I want. I'd like to work with the results of the >>original query and modify it with the new criteria >> >>Any suggestions how I can achieve this, or whether it's even possible? I >>would greatly appreciate any assistance or comments. >> >>** >> >>Snippet of offending code: >> >>> >>//snippet follows: >> >>print ""; >>print " "; >>print ""; >> >>// more code >> >>if($_POST['submit'] == "Sort by Name"){ >> $query = "SELECT * FROM rap ORDER BY >> rsname,rfname,rcountry,rcity,DESC LIMIT 30 OFFSET 30"; >>} >>elseif($_POST['submit'] == "Sort by Country"){ >> $query = "SELECT * FROM rap ORDER BY >> rcountry,rcity,rsname,rfname DESC LIMIT 30 OFFSET 30"; >>} >> >>//more code, including paging functions >>?> >> >> >> >>Tia, >>Andre >> >> >> >> >> > > > > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Modifying the sort order of a query
Try a sub-select: SELECT * FROM (SELECT * FROM rap ORDER BY rcountry,rcity,rsname,rfname DESC LIMIT 30 OFFSET 30) ORDER BY whatever; miguel On Wed, 5 Jun 2002, Andre Dubuc wrote: > Apache 1.3.23 + PHP 4.1.2 + PostgreSQL 7.2 > > I have a query that sorts by name, country, and city, then pages in groups of > 30 records. Originally, I had also coded two buttons: "Sort by Country', > 'Sort by City' since I wanted to offer users the options of these choices. > > Unfortunately, these buttons work well, but re-select the whole shebang > (which I guess is to be expected), destroying any paging that had already > started. I've tried all sorts of ways, tried re-arranging the order of > execution, but the result is the same. > > I'm at a loss on how to proceed. I've deleted the choice (for now) and run it > as a simple select order by name query.The problem seems to be: I need a > sub-query select function that retains the original query, simply > re-organizing it according to the new criteria, and retains the original > paging. (Perhaps I shouldn't care whether the paging is messed up as long as > the results are the same.) > > I realize that the way it's set up below, it will automatically send a NEW > query, which is not what I want. I'd like to work with the results of the > original query and modify it with the new criteria > > Any suggestions how I can achieve this, or whether it's even possible? I > would greatly appreciate any assistance or comments. > > >** > > Snippet of offending code: > > > //snippet follows: > > print ""; > print " "; > print ""; > > // more code > > if($_POST['submit'] == "Sort by Name"){ > $query = "SELECT * FROM rap ORDER BY > rsname,rfname,rcountry,rcity,DESC LIMIT 30 OFFSET 30"; > } > elseif($_POST['submit'] == "Sort by Country"){ > $query = "SELECT * FROM rap ORDER BY > rcountry,rcity,rsname,rfname DESC LIMIT 30 OFFSET 30"; > } > > //more code, including paging functions > ?> > > > > > Tia, > Andre > > > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Modifying the sort order of a query
Apache 1.3.23 + PHP 4.1.2 + PostgreSQL 7.2 I have a query that sorts by name, country, and city, then pages in groups of 30 records. Originally, I had also coded two buttons: "Sort by Country', 'Sort by City' since I wanted to offer users the options of these choices. Unfortunately, these buttons work well, but re-select the whole shebang (which I guess is to be expected), destroying any paging that had already started. I've tried all sorts of ways, tried re-arranging the order of execution, but the result is the same. I'm at a loss on how to proceed. I've deleted the choice (for now) and run it as a simple select order by name query.The problem seems to be: I need a sub-query select function that retains the original query, simply re-organizing it according to the new criteria, and retains the original paging. (Perhaps I shouldn't care whether the paging is messed up as long as the results are the same.) I realize that the way it's set up below, it will automatically send a NEW query, which is not what I want. I'd like to work with the results of the original query and modify it with the new criteria Any suggestions how I can achieve this, or whether it's even possible? I would greatly appreciate any assistance or comments. ** Snippet of offending code: "; print " "; print ""; // more code if($_POST['submit'] == "Sort by Name"){ $query = "SELECT * FROM rap ORDER BY rsname,rfname,rcountry,rcity,DESC LIMIT 30 OFFSET 30"; } elseif($_POST['submit'] == "Sort by Country"){ $query = "SELECT * FROM rap ORDER BY rcountry,rcity,rsname,rfname DESC LIMIT 30 OFFSET 30"; } //more code, including paging functions ?> Tia, Andre -- Please pray the Holy Rosary to end the holocaust of abortion. Remember in your prayers the Holy Souls in Purgatory. May God bless you abundantly in His love! For a free Cenacle Scriptural Rosary Booklet: http://www.webhart.net/csrb/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php