drax
Member
Registered: 5th Feb 05
Location: Sittingbourne, Kent
User status: Offline
|
Hey guys,
Im pulling information out of a database, and one of the ways I need to do it is arranging by price, which is all well and good but the way its running the process is like this
1
24,600
4,000
If you can see what its doing, the way I need it to interpretate it is like this
1
4,000
24,600
Using decimal places, the field type is also a Varchar as it MAY need to have text in sometimes.
|
drax
Member
Registered: 5th Feb 05
Location: Sittingbourne, Kent
User status: Offline
|
I have solved this already! Doh..
Using the ABS function
as shown here http://www.thewatchmakerproject.com/journal/231/comparing-numbers-in-mysql
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
Looks to me like your data types are wrong - ie. you're storing as text not a number.
As text the order is correct - ie. 1,2,4 - rest of the 'word' doesn't affect the significance of the first value.
As a number, its length is important so they would order correctly.
You'll need to lose the commas though. Out of interest, past me the output from
SHOW FIELDS FROM tablename;
|
drax
Member
Registered: 5th Feb 05
Location: Sittingbourne, Kent
User status: Offline
|
Im using a non interger type of field as there is possibility that the client needs to put a word or two in with the number,
Here are the fields
Field Type Null Key Default Extra
id int(11) NO PRI NULL auto_increment
adTitle varchar(30) YES NULL
Year varchar(4) YES NULL
Make varchar(30) YES NULL
Model varchar(30) YES NULL
Mileage varchar(8) YES NULL
Engine varchar(30) YES NULL
Colour varchar(30) YES NULL
Spec varchar(256) YES NULL
Overview varchar(256) YES NULL
Engine_Specs varchar(256) YES NULL
Electronics_Management varchar(256) YES NULL
Transmission varchar(256) YES NULL
Differential varchar(256) YES NULL
Suspension varchar(256) YES NULL
Interior_Saftey varchar(256) YES NULL
Exterior varchar(256) YES NULL
Condition varchar(256) YES NULL
Driving_Impression varchar(256) YES NULL
FOB varchar(30) YES NULL
UK_Landed varchar(30) YES NULL
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
Is this the mileage field?
Might be better having an integer for miles and varchar for mileage_comment field or something?
That way you can do proper maths on the number - ie. order by / where .. between etc. and also format that comment field differently.
Don't forget also, if you allow text in with the number, your user might still break your ordering even with abs() function.
Also if the comma is left out you'll be in trouble.
2000
24,000
27000
... will order ...
24
2000
27000
You're also loosing the hundreds, tens and units of course.
OK unless you have cars with mileage all within 1000 of each other and you want an accurate order. Its not the end of the world but might look a bit shonky when its almost right but not actually accurate.
|
AndyKent
Member
Registered: 3rd Sep 05
User status: Offline
|
I agree 
Makes your life so much easier if you just choose int fields. I would always add a 'comment' field if I ever thought more information might be needed.....
Why might they need to add more info though? A mileage is fixed so what needs clarifying?
|
drax
Member
Registered: 5th Feb 05
Location: Sittingbourne, Kent
User status: Offline
|
Its by price which is the ordering issue You guys are right, I may just change it to an Int, cheers.
Still learning php and SQL alot, only really kicked into it this last week
|
