order by ASC - why the #'s go 100 then 1000 to 200 and 2000

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
dooms2u
Forum Newbie
Posts: 4
Joined: Mon Jul 18, 2005 4:00 pm
Location: Broomfield, CO.

order by ASC - why the #'s go 100 then 1000 to 200 and 2000

Post by dooms2u »

I think this a simple fix, but not to me right now.

$sql = "select * from dcplog order by order_of_importance asc";

output looks like this:
order_of_importance
100
100
1000
1000
1000
1010
1100
1200
200
2000

Need the output to go:
order_of_importance
100
200
300
1000
2000
3000

Thanks for the help.
Sorry about evilwalrus.
User avatar
andre_c
Forum Contributor
Posts: 412
Joined: Sun Feb 29, 2004 6:49 pm
Location: Salt Lake City, Utah

Post by andre_c »

you're probably using a varchar instead of an int field...
mysql is ordering as if the field was a string

show the CREATE statement used to create the table
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

Fortunately there's a way around this without changing the table structure.

Code: Select all

SELECT CAST( order_of_importance AS unsigned ) AS order_of_importance
FROM dcplog
ORDER BY order_of_importance ASC
The SQL cast() function allows you to take a column of a particular type and treat it as if it's a different type, in this case taking a varchar or char column and ordering it as if it's an unsigned integer.

Note: Not sure that'll work on MySQL 3.23 .. I'm using 4.1 and it's fine.
dooms2u
Forum Newbie
Posts: 4
Joined: Mon Jul 18, 2005 4:00 pm
Location: Broomfield, CO.

Post by dooms2u »

Now the plot thickens, I have some text in this field also.

on-hold
BOASC Hold
Null

order_of_importance char(50)

can I change this to a largeinit and still have the text?
User avatar
andre_c
Forum Contributor
Posts: 412
Joined: Sun Feb 29, 2004 6:49 pm
Location: Salt Lake City, Utah

Post by andre_c »

try something like (untested):

Code: Select all

SELECT CAST( IF(order_of_importance IS NULL OR order_or_importance = 'on-hold' OR   order_of_importance = 'BOASC hold', 0, order_of_importance) AS unsigned ) AS order_of_importance
FROM dcplog
ORDER BY order_of_importance ASC
dooms2u
Forum Newbie
Posts: 4
Joined: Mon Jul 18, 2005 4:00 pm
Location: Broomfield, CO.

Post by dooms2u »

Thanks, that has moved me in the right direction.
neugent
Forum Newbie
Posts: 24
Joined: Wed Jul 06, 2005 3:35 am
Location: Philippines

Post by neugent »

glad someone brought up this topic, i have the following,

TT-A1-BR1
TT-A1-BR2
TT-A1-BR3
TT-A1-BR11
TT-A1-BR15
TT-A1-BR22
TT-A1-BR25

but when i do the following:

Code: Select all

SELECT item_code FROM items ORDER BY item_code ASC
the results are:

TT-A1-BR1
TT-A1-BR11
TT-A1-BR15
TT-A1-BR2
TT-A1-BR22
TT-A1-BR25
TT-A1-BR3
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

the best solution for both of your problems is to redesign your tables so that you can order it better. First answer of the first poster to set the field to an INT is definately a good idea. Your integer values should go into an integer field, and your text into a varchar field. Otherwise, you are gonna have data integrety problems.

Second poster should look into the type cast functions in mysql that were posted above. however, the solution to your problem is also redesigning the table.

if either of you could post a table structure, i'm sure we could give a lot better solution .
neugent
Forum Newbie
Posts: 24
Joined: Wed Jul 06, 2005 3:35 am
Location: Philippines

Post by neugent »

This is my table structure:

Code: Select all

CREATE TABLE `items` (                           
          `item_code` varchar(255) NOT NULL default '',  
          `Item_type` varchar(255) default NULL,         
          PRIMARY KEY  (`item_code`)                     
        ) TYPE=MyISAM
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

Code: Select all

CREATE TABLE `items` (
`item_code_id` int AUTO_INCREMENT,
`item_partial_code` INT,
item_code` varchar(255) NOT NULL default '',
Item_type` varchar(255) default NULL,
PRIMARY KEY (`item_code_id`)) TYPE=MyISAM
you want to make sure to ALWAYS use an integer for use as an Index/Primary Key. Indexes need to be unique #2, and it's hard to make that assurance if you don't have a unique field such as an auto-inc field. #3, I would split the table up even further if it was me. Have 3 tables total : a master table that contains NOTHING but indexes, and then the other 2 tables. 1 being for the item_code, and the other being for the item_type. But i dunno how big of a table you have here, so it may be a little too much optomization for this particular job..

Secondly, with this table structure I would also format my data in order before I updated it to my table.. something like this :

let's say that you don't know what your new code is going ot be.. so, let's create an index.

Code: Select all

<?php
$my_new_code = $_POST['get_new_code'];
$my_new_type = $_POST['get_new_type'];
$split_once = explode('-',$my_new_code);
$split_once = trim($split_once[2]);
$split_twice = explode('BR',$split_once);
$partial_code = trim($split_twice);
$sql = "INSERT into items (item_partial_code, item_code, item_type) VALUES ('".$partial_code."','".$my_new_code."','".my_new_type."')";
?>
now if you want to do a select in order, just order by item_partial_code

Code: Select all

$sql = "SELECT * from items ORDER BY item_partial_code ASC";
neugent
Forum Newbie
Posts: 24
Joined: Wed Jul 06, 2005 3:35 am
Location: Philippines

Post by neugent »

I think this would be a big problem for me since those item_code(s) are filenames as well plus i have around 3000 items.

This is my usual way of adding data to MySQL,

1. I use a program, which i made, to get all filenames in my directory then paste them in notepad.

Example:
ER-B1-BR1
ER-B1-N1
ER-B2-BR2

2. Replace all '-BR1' to '-BR1 Bracelet' then for -N1 to -N1 Necklace
which will then be imported to MS Excel and use a delimiter, which in here the space between the code and type, to divide them into columns, then becomes item_code and item_type.

3. Open up my MSAccess to import those data, then i use SQLYOG Enterprise a third party tool to connect to my database on the web, then import the database file thru ODBC.

I know this is very tasking, but its the easiest and i can track down filename errors easily considering the number of files im handling.
Post Reply