Hey gurus. I have a query I'm trying to run that I can only figure out how to do with 2 queries, but there's this itch telling me there's a better way of doing it. I'll post what I've got going right now and then try to explain it.

What I'm doing here is grabbing items from the inventory table and sorting them by whether or not they have a picture associated with it (top priority) and then sorting it by the price of the item so the higher priced items are placed on top. Unfortunately, when sorting by the image (basically checking whether or not it is null), the sort by price becomes pretty much useless. The only workaround I could think of was to split it into 2 queries and set the WHERE clause to include thumb <> '' and run a second one to inlcude the ones that are blank. That way the order isn't touched by the name of the image. This will work, but like I said, I have this little itch telling me there's a better way to do this.

Hi PTW,

The query you posted seems as if it should work fine, but if it's not then there might be something else that's causing a problem. What data types are these two fields?

Maybe it's just a matter of setting the `thumb` data type to bool, or int(1), and setting the default value to 0.

Rich

well technically it does work fine. The problem is that the thumb field is a path to an image, so mysql sorts by the name of the image. Does that make sense? Unfortunately, adding a column is a more daunting task than it seems to be. I know it sounds weird but I'm working on a pretty horrible system for now. Is there a way to find out if it's null or not and sort that way?

Ok gotcha! Try this...



That should do the trick. Let me know if it doesn't work.

Rich

It's not working because it's only selecting what is not null (I rearranged the query just little - changed to ISNULL(`thumb`) - I actually need to grab all things, whether or not thumb is null, but stick all of the ones where thumb is null at the bottom of the list...

I got it working. problem was that the fields weren't NULL - so I just changed it to `thumb=' '` and it seemed to work ok. Thanks man - got me on the right track!