MySQL Query many-to-many (i think)

programming
1

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.


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

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

3

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;

4

i’ll run that through php and see what comes up

cheers guys for you help.

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

6

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

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

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

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

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.


$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 …

11

thanks.

thats sort of what i’m doing now.

Just thought there would be a better way