Professional Web Applications Themes

Order By - Fair ordering on eccomerce site - MySQL

Hi Guys, I'm having a problem finding a solution to this, i know exactly what i want to do but its just doing it thats the problem. Heres the facts: - I cant change products database in any way - All results must be shown so no grouping The Problem: When searching for products i'd like to make the results fairer for each supplier currently, our database has a degree of random ordering but this is only due to records being removed then the empty record being taken by another product. So the default ordering of the database has large ...

  1. #1

    Default Order By - Fair ordering on eccomerce site

    Hi Guys,

    I'm having a problem finding a solution to this, i know exactly what i
    want to do but its just doing it thats the problem.

    Heres the facts:
    - I cant change products database in any way
    - All results must be shown so no grouping

    The Problem:

    When searching for products i'd like to make the results fairer for
    each supplier currently, our database has a degree of random ordering
    but this is only due to records being removed then the empty record
    being taken by another product. So the default ordering of the
    database has large chucks of products from the same supplier.

    Currently the ordering is done by 2 factors :
    - zero priced parts are placed at the bottom of results
    - products are ordered by price ASC (this can be changed from the user
    end)

    what i want to do is put an extra level of of ordering within price
    blocks that orders the products one from each manufacturer in a
    repeating pattern. Theres no problem pulling out the manufacturer
    codes randomising them butthe problem im having is ordering them in a
    set pattern so that each supplier has a part at the top of a price
    block and there no suppliers with large chunks of products hogging all
    the sales.

    For example

    Manufacturer manuID | Price
    id2 10.00
    id3 10.00
    id5 10.00
    id2 10.00
    id3 10.00
    id5 10.00

    i know i can use order by field
    (column_name,'field_value','field_value') but this needs to sort the
    results in this repeating pattern.

    Any ideas ?

    Tom Guest

  2. #2

    Default Re: Order By - Fair ordering on eccomerce site

    On 21 Feb 2007 04:28:57 -0800, Tom Wilson wrote: 

    Sure, add a "last order placed on" date column, and sort by that as
    well. The supplier you've ordered from longest ago will pop out on top.
    When you order from them, slap the current date into the column.

    --
    4. Shooting is not too good for my enemies.
    --Peter Anspach's list of things to do as an Evil Overlord
    Peter Guest

  3. #3

    Default Re: Order By - Fair ordering on eccomerce site

    On 21 Feb, 14:11, "Peter H. Coffin" <com> wrote: 

    Good answer apart from missing the first fact:
    - I cant change products database in any way

    Captain Guest

  4. #4

    Default Re: Order By - Fair ordering on eccomerce site

    On 21 Feb 2007 06:53:19 -0800, Captain Paralytic wrote: 
    >
    > Good answer apart from missing the first fact:
    > - I cant change products database in any way[/ref]

    Can you add another table, even? That could be joined in, by whatever
    the vendor ID is. (If you can't do ANYTHING with the database, then the
    answer is entirely "Must be handled programmatically.")

    --
    14. The hero is not entitled to a last kiss, a last cigarette, or any other
    form of last request.
    --Peter Anspach's list of things to do as an Evil Overlord
    Peter Guest

  5. #5

    Default Re: Order By - Fair ordering on eccomerce site

    On 21 Feb, 15:19, "Peter H. Coffin" <com> wrote: [/ref]

    >
    > Can you add another table, even? That could be joined in, by whatever
    > the vendor ID is. (If you can't do ANYTHING with the database, then the
    > answer is entirely "Must be handled programmatically.")
    >
    > --
    > 14. The hero is not entitled to a last kiss, a last cigarette, or any other
    > form of last request.
    > --Peter Anspach's list of things to do as an Evil Overlord[/ref]

    Well he said that he couldn't change he products "database". I guess
    he could have meant "table", but we'll have to wait for him to come
    back on that.

    Captain Guest

  6. #6

    Default Re: Order By - Fair ordering on eccomerce site

    On Feb 21, 4:44 pm, "Captain Paralytic" <com>
    wrote: 
    > [/ref]
    > [/ref]


    >
    > Well he said that he couldn't change he products "database". I guess
    > he could have meant "table", but we'll have to wait for him to come
    > back on that.[/ref]

    Hi Guys,

    Thanks for your help ,

    I think i might of over explained what i want to do. I see this as
    ordering problem.

    Right now when someone searches on the site they are served a pagnated
    list of products order by price low to high.
    What i want to do is inside a price block of say 10, say 6 suppliers
    have 10 products each at 10. Order them in a repeated pattern so that
    each supplier has a product listed in the top 6 for 10.

    supplier 1 - product title - $10
    supplier 2 - product title - $10
    supplier 3 - product title - $10
    supplier 4 - product title - $10
    supplier 5 - product title - $10
    supplier 6 - product title - $10
    supplier 1 - product title - $10
    supplier 2 - product title - $10
    supplier 3 - product title - $10
    and so on....


    Getting who comes where in the pattern list is already covered but
    actually getting the results to follow this pattern inside the price
    blocks is the problem.

    We found that some suppliers have large blocks of products in the
    database and hence for price blocks they are appearing in some
    instances all on the first page. Which isnt fair really to other
    suppliers.

    The products table i can't touch, but i can add tables and change the
    supllier records no problem.

    Thanks for your help,

    Tom

    Tom Guest

  7. #7

    Default Re: Order By - Fair ordering on eccomerce site

    On Feb 21, 5:05 pm, "Tom Wilson" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > Hi Guys,
    >
    > Thanks for your help ,
    >
    > I think i might of over explained what i want to do. I see this as
    > ordering problem.
    >
    > Right now when someone searches on the site they are served a pagnated
    > list of products order by price low to high.
    > What i want to do is inside a price block of say 10, say 6 suppliers
    > have 10 products each at 10. Order them in a repeated pattern so that
    > each supplier has a product listed in the top 6 for 10.
    >
    > supplier 1 - product title - $10
    > supplier 2 - product title - $10
    > supplier 3 - product title - $10
    > supplier 4 - product title - $10
    > supplier 5 - product title - $10
    > supplier 6 - product title - $10
    > supplier 1 - product title - $10
    > supplier 2 - product title - $10
    > supplier 3 - product title - $10
    > and so on....
    >
    > Getting who comes where in the pattern list is already covered but
    > actually getting the results to follow this pattern inside the price
    > blocks is the problem.
    >
    > We found that some suppliers have large blocks of products in the
    > database and hence for price blocks they are appearing in some
    > instances all on the first page. Which isnt fair really to other
    > suppliers.
    >
    > The products table i can't touch, but i can add tables and change the
    > supllier records no problem.
    >
    > Thanks for your help,
    >
    > Tom[/ref]

    It's not a perfect answer but by far the simplest way is ORDER BY
    RAND(),PRICE

    strawberry Guest

  8. #8

    Default Re: Order By - Fair ordering on eccomerce site

    On Feb 21, 6:16 pm, "strawberry" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]










    >
    > It's not a perfect answer but by far the simplest way is ORDER BY
    > RAND(),PRICE[/ref]

    I meant ORDER BY PRICE, RAND()

    strawberry Guest

  9. #9

    Default Re: Order By - Fair ordering on eccomerce site

    On Feb 21, 6:19 pm, "strawberry" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > I meant ORDER BY PRICE, RAND()[/ref]

    Tryed that strawberry but it doesnt do exactly what im after and the
    performance hit makes it not an option. We're talking over 500,000
    records

    Does anyone have any ideas?

    Tom Guest

Similar Threads

  1. Do clients have to purchase contribute in order toupdate site?
    By Paevo Kelley in forum Macromedia Contribute General Discussion
    Replies: 0
    Last Post: December 27th, 08:13 PM
  2. Replies: 2
    Last Post: October 11th, 12:25 AM
  3. Ordering by date ???
    By TomT in forum ASP Database
    Replies: 21
    Last Post: August 17th, 08:11 PM
  4. [PHP] File ordering
    By Lowell Allen in forum PHP Development
    Replies: 1
    Last Post: August 12th, 02:04 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139