Page 1 of 1

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

Posted: Mon Jul 18, 2005 4:20 pm
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.

Posted: Mon Jul 18, 2005 4:23 pm
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

Posted: Mon Jul 18, 2005 4:35 pm
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.

Posted: Mon Jul 18, 2005 5:15 pm
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?

Posted: Mon Jul 18, 2005 7:26 pm
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

Posted: Tue Jul 19, 2005 3:57 pm
by dooms2u
Thanks, that has moved me in the right direction.

Posted: Tue Jul 19, 2005 10:04 pm
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

Posted: Tue Jul 19, 2005 10:48 pm
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 .

Posted: Tue Jul 19, 2005 10:55 pm
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

Posted: Tue Jul 19, 2005 11:11 pm
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";

Posted: Wed Jul 20, 2005 12:09 am
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.