Sorting

cpaoletto

I need to sort data that is primarily numbers, however some of the entries contain a dash. Ex. 12324-02

I need to be able to sort this data in increasing order. So basically only the numbers before the dash are taken into account when sorted. The problem I am having is what data type should I be using, I am currently using varchar for my variable however when I try sorting the data obviously it does not sort in ascending order. I tried using int but only the numbers after the dash are stored.

Any help would be greatly appreciated!

Thanks!!!

AstroTeg

I'd recommend staying with the varchar. You can do SUBSTRING_INDEX(str,delim,count) --> SUBSTRING_INDEX([your varchar field], '-', 1)

I can't remember if you can toss that chunk directly into the order by to get: "ORDER BY SUBSTRING_INDEX([your varchar field], '-', 1)" or if you have to put this in the SELECT clause and reference it like:

SELECT SUBSTRING_INDEX([your varchar field], '-', 1) AS sTrimmed FROM [mytable] ORDER BY sTrimmed

cpaoletto

I tried both methods and I am getting the following error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/antonint/public_html/class/createSolarturbine.php on line 16
Couldn't execute query

I have a question, what is sTrimmed?

j_miguel_y

why dont you just insert the number into an array. for example, if the id is 32748-32 why not putting it into an array like this |3|2|7|4|8|-|3|2|. Staring from the left, there are 5 digits. There is a function called TRIM in HTML that can partition this array. Im sorry i didnt program it, but I am a bit lazy.

AstroTeg

I'd recommend NOT doing it in PHP and do it from the database, if possible.

"sTrimmed" is a field name I made up to represent the new trimmed value. It allows me to reference it elsewhere.

Check out http://dev.mysql.com/doc/mysql/en/String_functions.html

There's all sorts of nifty MySQL string functions (we're assuming you're using MySQL). SUBSTRING() would be another one you could use, but you'd want to have the length be the position where the '-' is found. Using LOCATE('-', [your string])
looks like that would give you the position.

Also double check the MySQL error you're getting. The error you got was about not having a record set. I'd recommend you print out your query and then run it from phpmyadmin or something like that. Or at least post it here so we can take a look...

cpaoletto

I tried the following and it seems to partially be working:

$query = "select * from mytable order by SUBSTRING_INDEX(myvariable,'-', 1) DESC";

The problem I am encountering is that it is sorting as follows:

22546-1
173521-1
130805-201
128812-100
124543
119075-1
118699-1
1186774-1
117881-101

As you can see the pattern is that it is sorting by the first 5 numbers. So it is sorting correctly if you just take into account the first 5 numbers. However I need it to sort by everything before the dash and it is not doing that.

Any ideas as to what could be wrong would be greatly appreciated.

Thanks in advance for your time and help!

AstroTeg

Ah... MySQL is sorting perfectly. The problem is the field is a varchar.

Here's a query I tried and it worked out rather well:

SELECT CAST(SUBSTRING_INDEX(myvariable, '-', 1 ) AS UNSIGNED) AS sTrim
FROM mytable
ORDER BY sTrim DESC

or -

SELECT * FROM mytable
ORDER BY CAST(SUBSTRING_INDEX(myvariable, '-', 1 ) AS UNSIGNED) DESC

You just need to cast the varchar data as an integer (I chose unsigned in this example). Once its an integer, the order by clause will work as expected.

cpaoletto

I tried the following:

SELECT CAST(SUBSTRING_INDEX(part, '-', 1 ) AS UNSIGNED) AS sTrim FROM solarturbine ORDER BY sTrim DESC

And I am getting the following error:

You have an error in your SQL syntax near '(SUBSTRING_INDEX(part, '-', 1 ) AS UNSIGNED) AS sTrim FROM solarturbine ORDER BY' at line 1

THANX!