From: John Taylor-Johnston on
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?
From: Robert Cummings on
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
From: John Taylor-Johnston on
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?
From: John Taylor-Johnston on
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?
>
>
From: Robert Cummings on
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
 |  Next  |  Last
Pages: 1 2
Prev: Shipping calculator
Next: Zend debugger doesn't work