Combining mySQL tables in one row?

Question

I have four tables that are related.

images Table

cid     link
=======================
1       something.jpg
2       else.jpg

terms Table

cid     term         is_attr
================================
1      Location     0
2      Caption      1
3      Camera Lens  0

tags Table

cid      Name          term_id
==============================
1       somewhere      1
2       BFE            1
3       A word         2

linked_tags Table

cid    photo_id     tag_id
==========================
1      1            1
2      1            2
3      1            3

if the term is_attr == 1for this term should have only one entry in the table linked_tags.

If I were to query the image table and get the tags at the same time, how would I do it?

I would like to return (something):

_____________________________________________________________________________
cid      |link              |attributes        |tags                |
=========|==================|==================|====================|
1        |something.jpg     |__________________|____________________| 
         |                  ||term    |value  |||term    |value    ||
         |                  ||========|=======|||========|=========||
         |                  ||caption |A word |||Location|somewhere||
         |                  ||        |       |||Location|BFE      ||  

This is what I am looking for (PHP side):

//Row 1
array(
  'link' => "something.jpg",
  'attributes' => array('caption'=>"A word"),
  'tags' => array('Location'=>array('somewhere','BFE'))
);
//Notice 'caption' points to a string and 'location' points to an array
//Row 2
array(
  'link' => "else.jpg",
  'attributes' => array(),
  'tags' => array()
);
// OR
array(
  'link' => "else.jpg"
);
// OR
array(
  'link' => "else.jpg",
  'attributes' => array('caption'=>""),
  'tags' => array();
);

Answer 1

SELECT  i.*, GROUP_CONCAT(tag.name SEPARATOR ', ')
FROM    image i
JOIN    tag
ON      tag.image_id = i.id
GROUP BY
        i.id

Update:

In a tabular format, the same number of columns in each record is assumed, all of which have the same record type.

Your task is best accomplished in three queries:

SELECT  cid, link
FROM    image
WHERE   cid = 1

(cid and link)

SELECT  t.name, tag.name
FROM    linked_tags lt
JOIN    tags
ON      tags.cid = lt.tag_id
JOIN    terms t
ON      t.cid = tags.term_id
WHERE   lt.photo_id = 1
        AND  t.is_attr = 1

(attributes)

SELECT  t.name, tag.name
FROM    linked_tags lt
JOIN    tags
ON      tags.cid = lt.tag_id
JOIN    terms t
ON      t.cid = tags.term_id
WHERE   lt.photo_id = 1
        AND  t.is_attr = 0

(tags)

Answer 2

, GROUP_CONCAT.

:

. . : GROUP_CONCAT ( 1024 ), group_concat_max_length.

Answer 3

- , ?

$query = 'SELECT images.cid, images.link, terms.term, tags.name, terms.is_attr FROM images LEFT JOIN linked_tags ON images.cid = linked_tags.photo_id LEFT JOIN tags ON linked_tags.tag_id = tags.cid LEFT JOIN terms ON tags.term_id = terms.cid WHERE 1 ORDER BY images.cid';
$result = mysql_query($query);

, PHP , .

$image_list = array();
while ($line = mysql_fetch_assoc($result)) { // fetch mysql rows until there are no more
    if (!isset($image_list[$line['cid']]) { // checks to see if we have created this image array row yet
        // if not, create new image in image array using cid as the key
        $image_list[$line['cid']] = array('link' => $line['link'], 'attributes' => array(), 'tags' => array());
    }
    if ($line['term'] != null) { // if there are no linked attributes or tags, move on to the next image leaving both arrays empty, otherwise...
        if ($line['is_attr']) {
            // if attribute row, add attribute using term as key
            $image_list[$line['cid']]['attributes'][$line['term']] = $line['name'];
        } else {
            // if tag row, add tag using term as key
            $image_list[$line['cid']]['tags'][$line['term']] = $line['name']; 
        }
    }
}

, , .