Order By Problem

I am having a problem with an order by command in my select statement.
I have a column heading which a user can click on and it will sort that
column. Here is my code:

} elseif ($sort == "price") {
if(($stores != "") or ($type9 != "") or ($make9 != "")) {
$result .= " WHERE id is not null";
if($stores != "") {
$result .= " AND store = '{$stores}'";
}
if($type9 != "") {
$result .= " AND category = '{$type9}'";
}
if($make9 != "") {
$result .= " AND make = '{$make9}'";
}
}
$result .= " order by price";


The price column is defined as a VARCHAR(25) column. When I click on
the price column and have it sort, it sorts as follows:

1050.00
110.00
119.99
125.00
1250.00
129.99
1299.99
149.99
174.99
190.00

How can I make it sort in numerical order?

Thanks for any help you can offer me.

Jeff

In alt.comp.databases.mysql Jeff Grossman <jeff@stikman.com> wrote:
> I am having a problem with an order by command in my select statement.
> I have a column heading which a user can click on and it will sort that
> column. Here is my code:
>
[CODE SNIPPED]
>
> The price column is defined as a VARCHAR(25) column. When I click on
> the price column and have it sort, it sorts as follows:
>
> 1050.00
> 110.00
> 119.99
> 125.00
> 1250.00
> 129.99
> 1299.99
> 149.99
> 174.99
> 190.00
>

Sorry to waste anybody's time. I figured out my problem. I was using a
character field when I should have been using an integer field. I have
done an alter table and changed the field to be an integer and now it is
sorting the way I want.

Jeff

On Mon, 07 Nov 2005 20:21:15 -0800, Jeff Grossman wrote:
>> 119.99
>> 129.99
>> 1299.99
>> 149.99
>> 174.99
>>
> Sorry to waste anybody's time. I figured out my problem. I was using a
> character field when I should have been using an integer field. I have
> done an alter table and changed the field to be an integer and now it is
> sorting the way I want.

If you want to store 129.99 you want a float column not an integer column.

Cheers,


Andy

--
Andy Jeffries | gPHPEdit Lead Developer
http://www.gphpedit.org | PHP editor for Gnome 2
http://www.andyjeffries.co.uk | Personal site and photos

In alt.comp.databases.mysql Andy Jeffries <news@andyjeffries.co.uk> wrote:
> On Mon, 07 Nov 2005 20:21:15 -0800, Jeff Grossman wrote:
>>> 119.99
>>> 129.99
>>> 1299.99
>>> 149.99
>>> 174.99
>>>
>> Sorry to waste anybody's time. I figured out my problem. I was using a
>> character field when I should have been using an integer field. I have
>> done an alter table and changed the field to be an integer and now it is
>> sorting the way I want.
>
> If you want to store 129.99 you want a float column not an integer column.

Wow, it looks like I screwed that one up. I am going to go change the
column to a float. I am now noticing that I lost all of my cent figures
when I changed it to an integer.

Jeff

Jeff Grossman <jeff.nospam@stikman.com> wrote in
news:s25743xg2t.ln2@stikman.com:

> In alt.comp.databases.mysql Andy Jeffries <news@andyjeffries.co.uk>
> wrote:
>> On Mon, 07 Nov 2005 20:21:15 -0800, Jeff Grossman wrote:
>>>> 119.99
>>>> 129.99
>>>> 1299.99
>>>> 149.99
>>>> 174.99
>>>>
>>> Sorry to waste anybody's time. I figured out my problem. I was
>>> using a character field when I should have been using an integer
>>> field. I have done an alter table and changed the field to be an
>>> integer and now it is sorting the way I want.
>>
>> If you want to store 129.99 you want a float column not an integer
>> column.
>
> Wow, it looks like I screwed that one up. I am going to go change the
> column to a float. I am now noticing that I lost all of my cent
> figures when I changed it to an integer.

But of course, no time was lost, 'cuz you can restore from your backups,
right ?

<evil grin>



--
Marc Bissonnette
CGI / Database / Web Management Tools: http://www.internalysis.com
Looking for a new ISP? http://www.canadianisp.com

In alt.comp.databases.mysql Marc Bissonnette <dragnet@internalysis.com> wrote:
> But of course, no time was lost, 'cuz you can restore from your backups,
> right ?
>
> <evil grin>
>
Backups? Who needs backups?