Adding several records from one table to a single record from another table

Databases
1

Wow, what a verbose title I’ve used for this topic!

I have a table that contains meta-data for content items (such as pages on a website). Simplified:


CREATE TABLE `content` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I also have a table that contains ‘loose’ bits of content that belong to the pages stored in the first table (they are linked using the id from the first table stored as foreign key in the second table).


CREATE TABLE `custom_data` (
 `field_id` int(11) NOT NULL,
 `foreign_id` int(11) NOT NULL,
 `name` varchar(30) NOT NULL
 `value` text COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Let’s assume some data:

Table content:

id: 1, name: My Page
id: 2, name: My Other Page

Table custom_data:

id: 1, foreign_id: 1, name: title, value: This is my page!
id: 2, foreign_id: 1, name: text,  value: Lorem ipsum dolor sit amet.
id: 3, foreign_id: 2, name: title, value: This is my other page!
id: 4, foreign_id: 2, name: text,  value: Lorem ipsum dolor sit amet.

Now, if a user requests the page with id 1, I simply select the meta-data in content, then the data in custom_data, and using PHP make an array like so:

id:    1, 
name:  My Page, 
title: This is my page!,
text:  Lorem ipsum dolor sit amet.

So far so good. Now, if a user says “give me all the pages”, things get a little tricky. What I want to have returned from MySQL is:


First record in result set:
id:    1, 
name:  My Page, 
title: This is my page!,
text:  Lorem ipsum dolor sit amet.

Second record in result set:
id:    2, 
name:  My Other Page, 
title: This is other my page!,
text:  Lorem ipsum dolor sit amet.

I currently see two ways of doing that:

  1. Issue two queries, stick the data together using PHP
    ===========
    a) fetch all the data in content
    b) collect the ids of all the results from step a
    c) fetch all the data from custom_data using the ids from step b
    d) loop through those results in PHP, adding them to the results from step a

  2. Issue one query to get the data, then issue separate queries to fetch the customer_data in a foreach or while loop
    ===========
    This is so ugly I’m not even going to describe it :smiley:

Now for my question, finally:

Is there a way in MySQL to return the data as described above using just one query? I’m pretty sure there isn’t, but I’m no database expert so it’s very possible I’ve missed something which will help me find the perfect solution :slight_smile:

Thanks!

2

This database structure IMO is over complicating things because of the high normalisation. Have you considered a simple page table with the title and text fields in?

3

The system works by letting developers define their own fields into which editors can enter content (rich text fields, select inputs, multiple checkboxes, file uploads etc).

While I could build it so it simply adds a field to the content table, that’ll get tricky after a while when those fields are edited or deleted. As the system allows unlimited custom fields (more info here), using a single table (and updating the table structure continually) won’t work very well.

4

sure :slight_smile:

that’d be your basic join query:

SELECT content.id AS content_id
     , content.name AS content_name
     , custom_data.field_id
     , custom_data.name
     , custom_data.value
  FROM content
INNER
  JOIN custom_data
    ON custom_data.foreign_id = content.id
ORDER
    BY content.id
5

Rudy,

That’ll return 1 row per entry in the custom_data table for each page, which isn’t exactly what I’m after.
Here’s an image to show what I mean. Pictures speak a thousand words and all that :slight_smile:

6

your amazing query would indeed be amazing

too bad it’s not possible

just another shortcoming of the EAV model

7

Pity, but I thought as much. Formatting the data into the required array in PHP will work fine though, albeit with a little overhead.

Thanks! :slight_smile:

goes to read what an EAV model is