Compare Limit and Sort Arrays

[railanc4309]

Good afternoon all,

I'm pretty new to PHP, but have been picking it up slowly over the last few months. However, today i've reached a very large hurdle...

I have a database of products, the database also contains 'Brands'.

E.g.

Name Brand
Shirt Armani
TShirt Prada
Jeans Prada
Shirt Armani
TShirt Prada
Jeans Armani

etc...

I have a query to pull-out distinct Brands, and sort them by the number of products each Brand has.

This works OK for me.

I have an array where I've specified an ordering preference for displaying these Brands on the page...

$brand_order = array("armani" => 1, "prada" => 2, "g-star" => 3);

I want to limit the number of brands I show on the page to 15, however...
I can't do this by simply adding a limit to the first query, because we want to be a bit picky of it comes down to a 'tie-breaker' for brands that show the same number of products...

E.g.

The database pulls out 20 brands
The top Brands 1 - 13 are OK, and contain quite high product quantities, but say Brand number 14, 15, 16 and 17 each return 10 products- then I want to check against the $brand_order array and display them in the order shown.

at the same time, I don't want the number of brands shown to exceed 15.

Make sense? I'm not even sure I understand it any more, but i'm sure there's someone out there who could help me!!

In short, I want to:

• Pull out distinct brands

• Order by quantity.

• If quantities match

• Sort by the order of a pre-defined array.

• Keep looping through and sorting until the top 15 brands are shown.

Thanks in advance,
Rachael

[paulnaj]

Hi,

Without actually seeing the tables etc., one thing that might help would be to include the 'order' field in the 'brands' table.

Then you could sort on 'number of records returned' (grouped) and then 'brand order'.

Then just pluck off the first 15 with 'LIMIT 0, 15'

P.

[railanc4309]

Hi P !,

I'm not sure of the reasons, but I'm not allowed to modify any database tables, or add another one which contains the sort-order. If i'd been allowed to do that - it would have been the first thing I did!! 🙁

Any ideas how I can do this 'on the fly'?

Thanks again,
Rachael

[paulnaj]

Hi,

Oh - you didn't mention that you couldn't alter the db structure. You should still be able to do it with PHP. Can you post up the db table structure so I don't have to make up names of fields etc. and I'll get back later.

Also, roughly how many brands are we looking at, in total, and is that likely to increase quickly?

paul.