order by ASC
am 17.01.2010 05:52:30 von John Taylor-Johnston
Ok, I think this is a MySQl question. Take pity on me?
$sql = "SELECT * FROM $db.`mailinglist` WHERE `type` IN ('Member',
'Affiliated', 'Life Member') ORDER BY `rollnumber` ASC";
rollnumber is a varchar(50). I need it to be a text field. ASC does not
order the way I want.
1000
1001
998
999
I want it to order like this:
998
999
1000
1001
How do I trick it? I cannot think of a way in MySQL. Is there a way in PHP?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: order by ASC
am 17.01.2010 05:58:16 von Robert Cummings
John Taylor-Johnston wrote:
> Ok, I think this is a MySQl question. Take pity on me?
>
> $sql = "SELECT * FROM $db.`mailinglist` WHERE `type` IN ('Member',
> 'Affiliated', 'Life Member') ORDER BY `rollnumber` ASC";
>
> rollnumber is a varchar(50). I need it to be a text field. ASC does not
> order the way I want.
>
> 1000
> 1001
> 998
> 999
>
> I want it to order like this:
>
> 998
> 999
> 1000
> 1001
>
> How do I trick it? I cannot think of a way in MySQL. Is there a way in PHP?
CAST it to an integer in the ORDER BY clause.
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: order by ASC
am 17.01.2010 06:24:43 von John Taylor-Johnston
It hates me:
SELECT * FROM ... WHERE `type` IN ('Member', 'Affiliated', 'Life
Member') ORDER BY CAST(rollnumber AS int)
SELECT * FROM ... WHERE `type` IN ('Member', 'Affiliated', 'Life
Member') ORDER BY CAST(`rollnumber` AS int)
SELECT * FROM ... WHERE `type` IN ('Member', 'Affiliated', 'Life
Member') ORDER BY CAST('rollnumber' AS int)
You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near 'int) ASC'
at line 1
I'll keep trying.
Robert Cummings wrote:
> CAST it to an integer in the ORDER BY clause.
>
> Cheers,
> Rob.
>
> John Taylor-Johnston wrote:
>> Ok, I think this is a MySQl question. Take pity on me?
>>
>> $sql = "SELECT * FROM $db.`mailinglist` WHERE `type` IN ('Member',
>> 'Affiliated', 'Life Member') ORDER BY `rollnumber` ASC";
>>
>> rollnumber is a varchar(50). I need it to be a text field. ASC does
>> not order the way I want.
>>
>> 1000
>> 1001
>> 998
>> 999
>>
>> I want it to order like this:
>>
>> 998
>> 999
>> 1000
>> 1001
>>
>> How do I trick it? I cannot think of a way in MySQL. Is there a way
>> in PHP?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: order by ASC
am 17.01.2010 06:28:12 von John Taylor-Johnston
Did some googling. This worked:
ORDER BY CAST(`rollnumber` AS SIGNED)
What is the difference? My problem in the meanwhile must be my version
of MySQL?
John Taylor-Johnston wrote:
> It hates me:
>
> SELECT * FROM ... WHERE `type` IN ('Member', 'Affiliated', 'Life
> Member') ORDER BY CAST(rollnumber AS int)
> SELECT * FROM ... WHERE `type` IN ('Member', 'Affiliated', 'Life
> Member') ORDER BY CAST(`rollnumber` AS int)
> SELECT * FROM ... WHERE `type` IN ('Member', 'Affiliated', 'Life
> Member') ORDER BY CAST('rollnumber' AS int)
>
> You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use
> near 'int) ASC' at line 1
>
> I'll keep trying.
>
> Robert Cummings wrote:
>> CAST it to an integer in the ORDER BY clause.
>>
>> Cheers,
>> Rob.
>>
>> John Taylor-Johnston wrote:
>>> Ok, I think this is a MySQl question. Take pity on me?
>>>
>>> $sql = "SELECT * FROM $db.`mailinglist` WHERE `type` IN ('Member',
>>> 'Affiliated', 'Life Member') ORDER BY `rollnumber` ASC";
>>>
>>> rollnumber is a varchar(50). I need it to be a text field. ASC does
>>> not order the way I want.
>>>
>>> 1000
>>> 1001
>>> 998
>>> 999
>>>
>>> I want it to order like this:
>>>
>>> 998
>>> 999
>>> 1000
>>> 1001
>>>
>>> How do I trick it? I cannot think of a way in MySQL. Is there a way
>>> in PHP?
>
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: order by ASC
am 17.01.2010 06:37:17 von Robert Cummings
John Taylor-Johnston wrote:
> Did some googling. This worked:
> ORDER BY CAST(`rollnumber` AS SIGNED)
>
> What is the difference? My problem in the meanwhile must be my version
> of MySQL?
You could have skipped quotes altogether. The difference is that you are
referencing a field name, not a string value.
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: order by ASC
am 20.01.2010 01:16:20 von Daevid Vincent
http://www.webdeveloper.com/forum/showthread.php?t=101174
You could do it like this too...
ORDER BY `rollnumber` + 0 ASC
And if you just got the data out in mysql (no ORDER BY -- which can be slow
in mysql), you could use PHP's sort as well...
http://php.net/manual/en/function.natsort.php
> -----Original Message-----
> From: Robert Cummings [mailto:robert@interjinn.com]
> Sent: Saturday, January 16, 2010 9:37 PM
> To: John Taylor-Johnston
> Cc: PHP-General
> Subject: Re: [PHP] order by ASC
>
> John Taylor-Johnston wrote:
> > Did some googling. This worked:
> > ORDER BY CAST(`rollnumber` AS SIGNED)
> >
> > What is the difference? My problem in the meanwhile must be
> my version
> > of MySQL?
>
> You could have skipped quotes altogether. The difference is
> that you are
> referencing a field name, not a string value.
>
> 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
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: order by ASC
am 20.01.2010 01:39:46 von Ashley Sheridan
--=-BmquSevZUb7g7+UwDaZ1
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
On Tue, 2010-01-19 at 16:16 -0800, Daevid Vincent wrote:
> http://www.webdeveloper.com/forum/showthread.php?t=101174
>
> You could do it like this too...
>
> ORDER BY `rollnumber` + 0 ASC
>
> And if you just got the data out in mysql (no ORDER BY -- which can be slow
> in mysql), you could use PHP's sort as well...
>
> http://php.net/manual/en/function.natsort.php
>
> > -----Original Message-----
> > From: Robert Cummings [mailto:robert@interjinn.com]
> > Sent: Saturday, January 16, 2010 9:37 PM
> > To: John Taylor-Johnston
> > Cc: PHP-General
> > Subject: Re: [PHP] order by ASC
> >
> > John Taylor-Johnston wrote:
> > > Did some googling. This worked:
> > > ORDER BY CAST(`rollnumber` AS SIGNED)
> > >
> > > What is the difference? My problem in the meanwhile must be
> > my version
> > > of MySQL?
> >
> > You could have skipped quotes altogether. The difference is
> > that you are
> > referencing a field name, not a string value.
> >
> > 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
> >
>
>
I've always found that ordering is much faster in MySQL than PHP. You
use quite a lot of memory sorting in PHP as well, as you first have to
load the whole result set into an array, which is additional to any
memory used in the result set by PHP and MySQL anyway.
Thanks,
Ash
http://www.ashleysheridan.co.uk
--=-BmquSevZUb7g7+UwDaZ1--
RE: order by ASC
am 20.01.2010 02:11:10 von Daevid Vincent
------=_NextPart_000_01DD_01CA992A.671D4C90
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
http://lmgtfy.com/?q=mysql+order+by+slow
it's notoriously slow to use ORDER BY with large tables. Mainly b/c mySQL
has to use a hash/temp table to re-sort AFAIK.
I wasn't thinking of sorting the whole set, only the list of numbers as the
OP only talked about sorting a single column...
>>> rollnumber is a varchar(50). I need it to be a text field. ASC does
>>> not order the way I want.
>>>
>>> 1000
>>> 1001
>>> 998
>>> 999
>>>
>>> I want it to order like this:
>>>
>>> 998
>>> 999
>>> 1000
>>> 1001
_____
From: Ashley Sheridan [mailto:ash@ashleysheridan.co.uk]
Sent: Tuesday, January 19, 2010 4:40 PM
To: Daevid Vincent
Cc: 'PHP-General'
Subject: RE: [PHP] order by ASC
On Tue, 2010-01-19 at 16:16 -0800, Daevid Vincent wrote:
http://www.webdeveloper.com/forum/showthread.php?t=101174
You could do it like this too...
ORDER BY `rollnumber` + 0 ASC
And if you just got the data out in mysql (no ORDER BY -- which can be slow
in mysql), you could use PHP's sort as well...
http://php.net/manual/en/function.natsort.php
> -----Original Message-----
> From: Robert Cummings [mailto:robert@interjinn.com]
> Sent: Saturday, January 16, 2010 9:37 PM
> To: John Taylor-Johnston
> Cc: PHP-General
> Subject: Re: [PHP] order by ASC
>
> John Taylor-Johnston wrote:
> > Did some googling. This worked:
> > ORDER BY CAST(`rollnumber` AS SIGNED)
> >
> > What is the difference? My problem in the meanwhile must be
> my version
> > of MySQL?
>
> You could have skipped quotes altogether. The difference is
> that you are
> referencing a field name, not a string value.
>
> 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
>
I've always found that ordering is much faster in MySQL than PHP. You use
quite a lot of memory sorting in PHP as well, as you first have to load the
whole result set into an array, which is additional to any memory used in
the result set by PHP and MySQL anyway.
Thanks,
Ash
http://www.ashleysheridan.co.uk
------=_NextPart_000_01DD_01CA992A.671D4C90--
RE: order by ASC
am 20.01.2010 02:22:21 von Ashley Sheridan
--=-gh4371k5VijRmhuhVYW2
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
On Tue, 2010-01-19 at 17:11 -0800, Daevid Vincent wrote:
> http://lmgtfy.com/?q=mysql+order+by+slow
>
> it's notoriously slow to use ORDER BY with large tables. Mainly b/c mySQL
> has to use a hash/temp table to re-sort AFAIK.
>
> I wasn't thinking of sorting the whole set, only the list of numbers as the
> OP only talked about sorting a single column...
>
> >>> rollnumber is a varchar(50). I need it to be a text field. ASC does
> >>> not order the way I want.
> >>>
> >>> 1000
> >>> 1001
> >>> 998
> >>> 999
> >>>
> >>> I want it to order like this:
> >>>
> >>> 998
> >>> 999
> >>> 1000
> >>> 1001
>
>
> _____
>
> From: Ashley Sheridan [mailto:ash@ashleysheridan.co.uk]
> Sent: Tuesday, January 19, 2010 4:40 PM
> To: Daevid Vincent
> Cc: 'PHP-General'
> Subject: RE: [PHP] order by ASC
>
>
> On Tue, 2010-01-19 at 16:16 -0800, Daevid Vincent wrote:
>
> http://www.webdeveloper.com/forum/showthread.php?t=101174
>
>
>
> You could do it like this too...
>
>
>
> ORDER BY `rollnumber` + 0 ASC
>
>
>
> And if you just got the data out in mysql (no ORDER BY -- which can be slow
>
> in mysql), you could use PHP's sort as well...
>
>
>
> http://php.net/manual/en/function.natsort.php
>
>
>
> > -----Original Message-----
>
> > From: Robert Cummings [mailto:robert@interjinn.com]
>
> > Sent: Saturday, January 16, 2010 9:37 PM
>
> > To: John Taylor-Johnston
>
> > Cc: PHP-General
>
> > Subject: Re: [PHP] order by ASC
>
> >
>
> > John Taylor-Johnston wrote:
>
> > > Did some googling. This worked:
>
> > > ORDER BY CAST(`rollnumber` AS SIGNED)
>
> > >
>
> > > What is the difference? My problem in the meanwhile must be
>
> > my version
>
> > > of MySQL?
>
> >
>
> > You could have skipped quotes altogether. The difference is
>
> > that you are
>
> > referencing a field name, not a string value.
>
> >
>
> > 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
>
> >
>
>
>
>
>
>
> I've always found that ordering is much faster in MySQL than PHP. You use
> quite a lot of memory sorting in PHP as well, as you first have to load the
> whole result set into an array, which is additional to any memory used in
> the result set by PHP and MySQL anyway.
>
>
> Thanks,
> Ash
> http://www.ashleysheridan.co.uk
>
>
>
>
I had a look at some of the results from that link to Google, but I
didn't see anything indicating MySQL was slower at ordering than PHP,
only that MySQL has issues ordering very large query sets on queries
that it cannot use an index to order on.
I've tried optimising ordering in both PHP and MySQL, and found that
usually it's best to leave as much to the database as possible, unless
the ordering becomes too complex.
Thanks,
Ash
http://www.ashleysheridan.co.uk
--=-gh4371k5VijRmhuhVYW2--