SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Zealot martin's Avatar
    Join Date
    Jun 2001
    Location
    London
    Posts
    138
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select Query Help please

    I'm trying to pull a product out of my database using PHP and MySQL with the following statement:

    /* get product list */
    $result = @mysql_query('SELECT *
    FROM bumpers, shape, colour, lookupcol
    WHERE lookupcol.bid = bumpers.bid
    AND lookupcol.cid = colour.cid
    AND shape.sname = '. $myshape);
    This works, although the problem I am having is that for each product there can be multiple colours. I have the colours in a seperate table. This SELECT statement pulls out the product, BUT because there are three colours assciated with this product, my results actually LIST the product 3 times with each colour, rather than ONCE with all the colours.

    Hope this is the right forum, and sorry if this doesnt make much sense!

    you can see a demo working at the following if it helps:-

    http://www.bumperstops.co.uk/beta/products/

    Thanks!
    Martin

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    very common problem

    the solution is to go ahead and return the data as is

    then "collapse" the multiple colours in your scripting language

    important: use an ORDER BY

    warning: if a product can have multiple shapes as well as colours, you'll get cross join effects, so tread carefully
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •