[Resolved] Join okay but order by problem

colinoo8

Hi there

I am havine a problem sorting on a field using the ORDER BY statment.

Using the below code, i get the result ORDER BY rows as
119
129
1449
255
74
98

i need to sort this field to
74
98
119
129
225
1449

but it wont work with the ORDER BY e.field_value ASC i am using below

select l.id, concat( a.field_value, ' ', b.field_value, ' ', d.field_value ) as name, a.field_value as make, b.field_value as model, c.field_value as full_desc, d.field_value as category, e.field_value as price
                from default_listingsDB l
                left join default_listingsDBElements a on l.id = a.listing_id and a.field_name = 'make'
                left join default_listingsDBElements b on l.id = b.listing_id and b.field_name = 'model'
                left join default_listingsDBElements c on l.id = c.listing_id and c.field_name = 'full_desc'
                left join default_listingsDBElements d on l.id = d.listing_id and d.field_name = 'category'
                left join default_listingsDBElements e on l.id = e.listing_id and e.field_name = 'price'
                where l.active = 'yes'
AND a.field_value ='janome'
                order by e.field_value ASC

any help would be great.
thanks

leatherback

Hi,

Not sure how you have structured your data here. It seems to me that you have either a very complex database structure, or else your query can be simplified much.

What is the database structure that you use (How many tables / which fields)?

colinoo8

Your right, i dont think the database layout is the best...

table 'default_listingsDBElements'
has 5 fields, the field 'field_name' contains the worlds 'make' 'model' 'full_desc' 'category' and 'price' and another field 'field_value' contains the info that i need, which corresponds the above words.

table 'default_listingsDB'
has 11 fields but i only need the value of 1 field 'acive' that had the same id as 'default_listingsDBElements' to see if the listing is active.

hope it helps

leatherback

Hm.. I would change the database structure, if possilble. unless you expect to extend the number of field_nameoften, I would give each field_name ( 'make' 'model' 'full_desc' 'category' and 'price') an individual colum. Also, I would add the colum active to this table, since it seems to have unique records then?

Then you have a very simple query to get the data from the database..

J./

colinoo8

I'm stuck with the tables layout as they are at the moment, do you think its because the select statment is a little complex that the ORDER BY wont sort correctly ?

leatherback

No. If the query is strctured properly, the sort should work, I think. It is just thinking towards the future: you are going to keep building complex queries to get your data.

I think this is the problem cause:
e.field_value as price

So in your sort, I think you should use: price, not e.field_value.

If that doesn't work, you could consider selecting all data ordered by the e.field_value colum, and use PhP for creating your output (So remove all the join statements, and do this in PhP. It is more straightforward, but a bit slower, I think.

colinoo8

I have just been testing and....
could the reason be because i am trying to sort numeric values in a text field ?

ORDER BY sorts rows as
119
129
1449
255
74
98

in stead of
74
98
119
129
225
1449

leatherback

eerh.. Yes..

Not sure how to tackle that. i think I saw it in one of the posts this morning. Hm.. Cant remember where..

leatherback

Yep, found it
here :

Use this:
mysql> select number from (table) order by (number+0);

+--------+
| number |
+--------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+--------+

The (field + 0 ) converts the text/character in the field into an integer.

colinoo8

Brill that will do the trick, thanks very much.

pete_bisby

Try multiplying the text field you're sorting on by 1, covnerting it to a number. E.g.

...
ORDER BY 1*e_field.value;

This was tried using MySQL 4.0.18 via phpMyAdmin, and sorted a text attribute with multiple values into numerical order.