The forums have permanently moved to forum.kirupa.com. This forum will be kept around in read-only mode for archival purposes. To learn how to continue using your existing account on the new forums, check out this thread.

Results 1 to 11 of 11

Thread: MySQL Query many-to-many (i think)

  1. #1

    MySQL Query many-to-many (i think)

    Hello

    I hope your all well.

    I have a question which i just thought i'd ask to you kind people to see if anyone else has done or run into this.

    I'm trying to cut down my MySQL queries and make MySQL do all the hard work and not php.

    I have this system that pulls out items from a database then seperate images as well as colours for that item.

    The database design looks a bit like this.

    HTML Code:
    cms_item
    id (pk)
    title
    dscpn
    
    ------------------------
    
    cms_colour
    id (pk)
    title
    swatch
    
    ------------------------
    
    cms_item_colour
    item_id (fk)
    colour_id (fk)
    
    ------------------------
    
    cms_item_image
    id (pk)
    title
    image
    item_id (fk)
    Now when i want to display the item i'd usually pull the item then uses its primary key to call the other data leaving in total 3 queries

    1 for the items main data
    1 for the items colours
    1 for the items images

    Is it possible to get this down to one query

  2. #2
    select a.id,a.title,a.dscpn, c.title, c.swatch,
    d.id as imageid, d.title,
    d.image from cms_item a
    inner join cms_item_colour b
    on b.item_id = a.id
    inner join cms_colour c
    on c.id = b.colour_id
    inner join cms_item_image d
    on d.item_id = a.id;

    take care if there is no matching entry in cms_item_colour or in cms_colour or cms_item_image then no row will be shown.
    If you want to change this behavior you'll need to use outer joins
    Last edited by borrob; June 16th, 2008 at 07:45 AM.
    the world is getting more complex because people need simple solutions

  3. #3
    1,627
    posts
    hugeExplosions = true;
    As borrob said this will give you the cms_item regardless of a match in the other tables

    select a.id,a.title,a.dscpn, c.title, c.swatch,
    d.id as imageid, d.title,
    d.image from cms_item a
    left join cms_item_colour b
    on b.item_id = a.id
    left join cms_colour c
    on c.id = b.colour_id
    left join cms_item_image d
    on d.item_id = a.id;
    MS Paint FTW!


  4. #4
    i'll run that through php and see what comes up

    cheers guys for you help.

  5. #5
    I've tried those and they work the thing is each item can have more then one image and colour. so imagine you looking at this item on a website and you see the images related to the item and the colours related to that item.

    At the moment the query is just outputting a long list of information i have no idea how to sort through to make it useable.

    Sorry but thanks so far
    Last edited by deletedUser2352352; June 16th, 2008 at 08:46 AM.

  6. #6
    1,627
    posts
    hugeExplosions = true;
    Well what is it that you require on the actual page? I'm sure the kirupa lot can help no matter what you want to display

    Most of the time once you have the data it's just a case of looping through the rowset and outputting some HTML accordingly
    MS Paint FTW!


  7. #7
    ha ha ha cheers.

    Yeah in the back end system i've created you can add colours to an item so you add and item and add colours the list of colours can grow or shrink depending on whats on offer. So each item has a list of colours that it can have. Also the item can have more then one image attached to it.

    think of it like a car website.

    a car has a list of colours and various images.

    From what i gather i have all the information in that join query which has to be more efficient then three queries then use php to sort through the data. Would that add more load onto the php part though.

    Sorry if this makes no sense at all.

  8. #8
    1,627
    posts
    hugeExplosions = true;
    Yeah the PHP is usually responsible for formatting the data ouput into a more meaningful layout - the SQL should just get the data in the quickest way possible. There's not actually a whole lot more to do than that

    Don't worry too much about the PHP overhead, as long as your queries are efficient the rest should be processed by PHP anyway
    MS Paint FTW!


  9. #9
    sweet so i should output the data even if it loops the same information loads of times and then use php to format the information into something useful.

    So how would i go about looping through that in php to provide me with the information i need in a useful way.

    eg all the images and colours

    Would i loop through it all and then seperate them into different arrays say all the images in one then colours in another and the item information into another?

    I really appreciate all this help.

    thanks
    Last edited by deletedUser2352352; June 16th, 2008 at 01:36 PM.

  10. #10
    There are a lot of ways to do this.
    If you don't want to loop through the whole result set ( with indeed a lot of repeated information ) you shouldn't use the sql statement above but rather go in a different approach.

    Code:
    $link = mysql_connect($host, $user, $pw);
    $sql = "select id,title,dscpn from cms_item";
    $item_result = mysql_query( $sql, $link );
    $item_row = mysql_fetch_object( $item_result );
    echo "<table>";
    while( $item_row )
    {
        echo "<tr>";
        echo "<td>";
            echo $item_row->title;
        echo "</td>";
        $sql = " select b.title from cms_item_colour a " .
               " inner join cms_colour b " .
               " on b.id = a.colour_id " .
               " where a.item_id=" . $item_row->id;
        $colour_result = mysql_query( $sql, $link );
        $colour_row = mysql_fetch_object( $colour_result );
        while( $colour_row )
        {
            echo "<td>";
                echo $colour_row->title;
            echo "</td>";
            $colour_row = mysql_fetch_object( $colour_result );
        }
        echo "</tr>";
        $item_row = mysql_fetch_object( $item_result );
    }
    echo "</table>";
    this should ( not tested ) give you:
    a table with:
    item title colour colour colour ......
    item title colour ......
    item title colour colour ......
    the world is getting more complex because people need simple solutions

  11. #11
    thanks.

    thats sort of what i'm doing now.

    Just thought there would be a better way

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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

Home About kirupa.com Meet the Moderators Advertise

 Link to Us

 Credits

Copyright 1999 - 2012