Re: [PHP] another pagination problem
On Fri, Jul 17, 2009 at 9:35 AM, PJaf.gour...@videotron.ca wrote: 4. when ordering by title, results are totally different for ASC DESC; not the same data at all (Is the ordering done on the entire db? ) Yes, sort of. ORDER BY is applied to the results of the entire query. Then LIMIT takes a slice out of the results (paginate). My guess, the problem you are experiencing is you are viewing page 2 of 5 of your output in ASC order, then switch over to DESC without adjusting the page number. What you are seeing (roughly) for page 2 of 5 in DESC order is what used to be on page 4 of 5 in ASC order, only upside-down. :-) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] another pagination problem
On Fri, 2009-07-17 at 11:55 -0400, PJ wrote: Ashley Sheridan wrote: On Friday 17 July 2009 16:35:10 PJ wrote: I noticed the thread on pagination here just after posting to mysql. But I am using php/mysql ;-) I have some rather complicated ordering problems: 1. need to order by 2 fields title, sub_title 2. need to display 10 items per page (hundreds, if not 1000s or pages) 3. order by ASC or DESC makes not difference when ordering by 2 fields 4. when ordering by title, results are totally different for ASC DESC; not the same data at all (Is the ordering done on the entire db? ) 5. those funny little dingles over foreign language characters refuse to follow our normal ordering rules; how do we do this? 6. The pagination and ordering syntax is correct working with no errors; I've double checked on commandline. 7. At worst, I'll have to leave it at ASC and let visitors pm; don't see a simple solution. Or am I wrong? -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php How are you attempting to order the query, do you have an excerpt? I've never had any problems ordering my results based on more than one field, where the orders are based on a mixture of ASC and DESC fields. The ordering part looks something like this: ORDER BY `field1` DESC, `field2` ASC, `field3` DESC and that works just fine for me. Also, what character set are you using on that table, as that may have a difference on how the ordering works. Sorry, forgot: another problem is the Thes - how do you exclude them from the ordering. Also: one output with just title for field returned a series of The Art.., The Birds..., The Birth...etc. in ASC; whereas DESC returned: Boats, Black Cumin, Birds of..., Biological..., Bioarchaeology.., Avaris... etc. -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php So you want to order by the text excluding the word 'The', so you'd have output like 'The Art', 'The Birds', 'Birds of...' ? Thanks Ash www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] another pagination problem
On Friday 17 July 2009 16:35:10 PJ wrote: I noticed the thread on pagination here just after posting to mysql. But I am using php/mysql ;-) I have some rather complicated ordering problems: 1. need to order by 2 fields title, sub_title 2. need to display 10 items per page (hundreds, if not 1000s or pages) 3. order by ASC or DESC makes not difference when ordering by 2 fields 4. when ordering by title, results are totally different for ASC DESC; not the same data at all (Is the ordering done on the entire db? ) 5. those funny little dingles over foreign language characters refuse to follow our normal ordering rules; how do we do this? 6. The pagination and ordering syntax is correct working with no errors; I've double checked on commandline. 7. At worst, I'll have to leave it at ASC and let visitors pm; don't see a simple solution. Or am I wrong? -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php How are you attempting to order the query, do you have an excerpt? I've never had any problems ordering my results based on more than one field, where the orders are based on a mixture of ASC and DESC fields. The ordering part looks something like this: ORDER BY `field1` DESC, `field2` ASC, `field3` DESC and that works just fine for me. Also, what character set are you using on that table, as that may have a difference on how the ordering works. -- 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] another pagination problem
Ashley Sheridan wrote: On Friday 17 July 2009 16:35:10 PJ wrote: I noticed the thread on pagination here just after posting to mysql. But I am using php/mysql ;-) I have some rather complicated ordering problems: 1. need to order by 2 fields title, sub_title 2. need to display 10 items per page (hundreds, if not 1000s or pages) 3. order by ASC or DESC makes not difference when ordering by 2 fields 4. when ordering by title, results are totally different for ASC DESC; not the same data at all (Is the ordering done on the entire db? ) 5. those funny little dingles over foreign language characters refuse to follow our normal ordering rules; how do we do this? 6. The pagination and ordering syntax is correct working with no errors; I've double checked on commandline. 7. At worst, I'll have to leave it at ASC and let visitors pm; don't see a simple solution. Or am I wrong? -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php How are you attempting to order the query, do you have an excerpt? I've never had any problems ordering my results based on more than one field, where the orders are based on a mixture of ASC and DESC fields. The ordering part looks something like this: ORDER BY `field1` DESC, `field2` ASC, `field3` DESC and that works just fine for me. Also, what character set are you using on that table, as that may have a difference on how the ordering works. Here's the query: SELECT * FROM book ORDER BY $sort $dir LIMIT $offset, $records_per_page; $sort == 'title, sub_title'; $dir==ASC (or DESC); $offset== (any multiple of 10); $records_per_page== 10; I could let the user change these, but I decided it is simpler to keep it at 10 books/page. Only the $dir is user settable. The character set is utf8-general; (I just went through some self-torture changing all those foreign curlicues to :cutesy_stuff; fortunately, the mysql replace() function helped - but six languages have quite a few weird accents. The title field can be fairly long - 182 chars; sub_title is 128, but it looks like I have to lengthen that too; found some truncated inputs. -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] another pagination problem
Ashley Sheridan wrote: On Friday 17 July 2009 16:35:10 PJ wrote: I noticed the thread on pagination here just after posting to mysql. But I am using php/mysql ;-) I have some rather complicated ordering problems: 1. need to order by 2 fields title, sub_title 2. need to display 10 items per page (hundreds, if not 1000s or pages) 3. order by ASC or DESC makes not difference when ordering by 2 fields 4. when ordering by title, results are totally different for ASC DESC; not the same data at all (Is the ordering done on the entire db? ) 5. those funny little dingles over foreign language characters refuse to follow our normal ordering rules; how do we do this? 6. The pagination and ordering syntax is correct working with no errors; I've double checked on commandline. 7. At worst, I'll have to leave it at ASC and let visitors pm; don't see a simple solution. Or am I wrong? -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php How are you attempting to order the query, do you have an excerpt? I've never had any problems ordering my results based on more than one field, where the orders are based on a mixture of ASC and DESC fields. The ordering part looks something like this: ORDER BY `field1` DESC, `field2` ASC, `field3` DESC and that works just fine for me. Also, what character set are you using on that table, as that may have a difference on how the ordering works. Sorry, forgot: another problem is the Thes - how do you exclude them from the ordering. Also: one output with just title for field returned a series of The Art.., The Birds..., The Birth...etc. in ASC; whereas DESC returned: Boats, Black Cumin, Birds of..., Biological..., Bioarchaeology.., Avaris... etc. -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php