order by ASC - why the #'s go 100 then 1000 to 200 and 2000
Moderator: General Moderators
order by ASC - why the #'s go 100 then 1000 to 200 and 2000
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.
$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.
Fortunately there's a way around this without changing the table structure.
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.
Code: Select all
SELECT CAST( order_of_importance AS unsigned ) AS order_of_importance
FROM dcplog
ORDER BY order_of_importance ASCNote: Not sure that'll work on MySQL 3.23 .. I'm using 4.1 and it's fine.
- andre_c
- Forum Contributor
- Posts: 412
- Joined: Sun Feb 29, 2004 6:49 pm
- Location: Salt Lake City, Utah
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 ASCglad 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:
the results are:
TT-A1-BR1
TT-A1-BR11
TT-A1-BR15
TT-A1-BR2
TT-A1-BR22
TT-A1-BR25
TT-A1-BR3
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 ASCTT-A1-BR1
TT-A1-BR11
TT-A1-BR15
TT-A1-BR2
TT-A1-BR22
TT-A1-BR25
TT-A1-BR3
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 .
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 .
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=MyISAMCode: 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=MyISAMSecondly, 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."')";
?>Code: Select all
$sql = "SELECT * from items ORDER BY item_partial_code ASC";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.
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.