SQL statement with DISTINCT

[Anon]

Hello,

My Product table has "main" categories in this order: 'Audio Components', 'Speakers', 'Video Components', 'Accessories'.

My client wants to preserve this order of the main categories and he doesn't care in what order all subcategories are displayed.

I'm using the following statement to fetch these main categories: $sql_main = "SELECT DISTINCT Maincat from $tablename";

The categories returned are in alphabetical order (from "a" to "z"), which is exactly what I'm trying to avoid.

I guess, sorting happens because of DISTINCT.

take a look: http://www.customav.com/enter.php3

Does anyone know how I can avoid sorting rows when I use DISTINCT in sql statements?

Thank you,

[e-mail]

[Anon]

You can't avoid this. The DB need to sort datas in order to do the distinct.

Consider normalizing your datas:

You could use one table for products:
product_id

category_id
subcategory_id
product_label

and one table for categories:
caterogory_id
category_order
category_label

In the category_order col put a number that indicates the order of category.
To retrieve categories you just need to select the content of the category table sorted by category order. It will be far more efficient than a DISTINCT.
You can do the same for subcategories.

JBL

[Anon]

Thanks for your reply.

The thing is that I don't want to break a table (even if it might be better solution).

Here's why:
I will not see client after the project is done - so, the client will be both user and administrator. I want to make it very simple for him to make database updates.

The table is created thru PHP script from a text file (client uploads this file periodically thru his admin page) - table structure is exact copy of text file and the text file is used by shopping cart.

MySQL table is only used to navigate thru product categories and subcategories and also to display product info for users. The shopping cart doesn't use table data at all -all the cart needs from PHP script is item_id, which is the same in MySQL table and text file.

The entire site was originally designed to work only with a text database file, but performance deteriorated when number of products increased (e.g. more than 1000).

That's where MySQL helps - it gives better navigation (speed). Shopping cart sleeps till user actually chooses to place an order.

I hope it all makes sense.

I'm still trying to play with sql commands to get the result I need and avoid changing table structure.

Thanks,

[e-mail]

[Anon]

One thing you may want to try is making the column for the product categories an ENUM type rather than a string type. To quote from the MySQL manual:

ENUM values are sorted according to the order in which the enumeration members were listed in the column specification. (In other words, ENUM values are sorted according to their index numbers.) For example, "a" sorts before "b" for ENUM("a", "b"), but "b" sorts before "a" for ENUM("b", "a"). The empty string sorts before non-empty strings, and NULL values sort before all other enumeration values.

This may work for your needs - I haven't played with it enough to be sure.

[Anon]

Try to use another column like Seq in table Main, where you put your own sorting sequence... It could work 🙂

[Anon]

Hi,

To make it work for now, I created another table with Maincat and Seq fields and changed sql command in PHP script to:

$sql_main = "SELECT DISTINCT $tablename.Maincat from $tablename,$tablesort where $tablename.Maincat = $tablesort.Maincat ORDER BY $tablesort.Seq";

http://customav.com/enter.php3

Thanks for your advice,

[e-mail]