another pagination problem

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 p&m; don't
see a simple solution.

Or am I wrong?


--
Hervé Kempf: "Pour sauver la planète, sortez du capitalisme."
------------------------------------------------------------ -
Phil Jourdan --- pj@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: 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 p&m; don't
> see a simple solution.
>
> Or am I wrong?
>
>
> --
> Hervé Kempf: "Pour sauver la planète, sortez du capitalisme."
> ------------------------------------------------------------ -
> Phil Jourdan --- pj@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 nev=
er=20
had any problems ordering my results based on more than one field, where th=
e=20
orders are based on a mixture of ASC and DESC fields. The ordering part loo=
ks=20
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=
=20
that table, as that may have a difference on how the ordering works.

=2D-=20
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: 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 p&m; don't
>> see a simple solution.
>>
>> Or am I wrong?
>>
>>
>> --
>> Hervé Kempf: "Pour sauver la planète, sortez du capitalisme."
>> ------------------------------------------------------------ -
>> Phil Jourdan --- pj@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 --- pj@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: 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 p&m; don't
>> see a simple solution.
>>
>> Or am I wrong?
>>
>>
>> --
>> Hervé Kempf: "Pour sauver la planète, sortez du capitalisme."
>> ------------------------------------------------------------ -
>> Phil Jourdan --- pj@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 --- pj@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: another pagination problem

On Fri, Jul 17, 2009 at 9:35 AM, PJ 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: 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:
> > =20
> >> I noticed the thread on pagination here just after posting to mysql. B=
ut
> >> 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 & DES=
C;
> >> 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 p&m; don't
> >> see a simple solution.
> >>
> >> Or am I wrong?
> >>
> >>
> >> --
> >> Hervé Kempf: "Pour sauver la planète, sortez du capitalisme.=
"
> >> ------------------------------------------------------------ -
> >> Phil Jourdan --- pj@ptahhotep.com
> >> http://www.ptahhotep.com
> >> http://www.chiccantine.com/andypantry.php
> >> =20
> >
> > How are you attempting to order the query, do you have an excerpt? I've=
never=20
> > had any problems ordering my results based on more than one field, wher=
e the=20
> > orders are based on a mixture of ASC and DESC fields. The ordering part=
looks=20
> > 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=20
> > that table, as that may have a difference on how the ordering works.
> >
> > =20
> Sorry, forgot: another problem is the Thes - how do you exclude them
> from the ordering.
>=20
> 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.
>=20
> --=20
> Hervé Kempf: "Pour sauver la planète, sortez du capitalisme."
> ------------------------------------------------------------ -
> Phil Jourdan --- pj@ptahhotep.com
> http://www.ptahhotep.com
> http://www.chiccantine.com/andypantry.php
>=20
>=20
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