How to handle a n:m relation correctly with PHP&MySQL?

Question

i've got 3 tables:

+-----------------+
|   validations   |
+----+-------+----+
| id | param | ...
+----+-------+

+------------------+
|   replacements   |
+----+-------------+
| id | replacement |
+----+-------------+

+--------------------------------+
|    validations-replacements    |
+---------------+----------------+
| validation_id | replacement_id |
+---------------+----------------+

Now I'm running my SQL query on that tables (with joins of course). And what I receive in PHP is sth. like that:

...
[6] => stdClass Object
(
    [id] => 11
    [search_param] => Dänische Belletristik
    [replacement] => Denmark
)

[7] => stdClass Object
(
    [id] => 11
    [search_param] => Dänische Belletristik
    [replacement] => Fiction
)
...

Now, in my PHP-Array I've got the same 'search_param' and 'id' multiple times. That sucks for printing it to screen. I could group the data by 'id' to avoid that, but then I've only got 1 'replacement' value available.

What I'm looking for would be an result like this:

...
[7] => stdClass Object
(
    [id] => 11
    [search_param] => Dänische Belletristik
    [replacement] => array(Denmark, Fiction)
)
...

What I want to know: Is that possible with my table structure by just fixing the query? Or do I have to care about that in my PHP-Code - if so: any hints how to do it best? There's plenty of data... Is my table structure correct? I'm still a bit uncertain when it comes to databases...

Best regards.

score 1 · Accepted Answer · answered Jan 25 '11 at 22:58

It looks like you want to show all replacements for a certain search param? Assuming your query is something like:

SELECT * 
FROM validations_replacements vr 
INNER JOIN validations v ON v.id = vr.validation_id 
INNER JOIN replacements r ON r.id = vr.replacement_id
WHERE v.param = '$search_param'

You can either group them in PHP by playing with the result array, assuming your results object is $results:

$replacements = array() ;
foreach ($results as $result) {
    $currSearchParam = $result['search_param'];
    $currReplacement = $result['replacement'] ;
    if (!isset($replacements[$currSearchParam])) {
        $replacements[$currSearchParam] = array() ;
    }
    $replacements[$currSearchParam][] = $currReplacement;
}

//I'll let you fill in the blanks like object id or naming the array keys as you wish

OR you can do it in mysql, then just iterate over the result in PHP:

SELECT v.id, v.param, GROUP_CONCAT(r.replacement)
FROM validations_replacements vr 
INNER JOIN validations v ON v.id = vr.validation_id 
INNER JOIN replacements r ON r.id = vr.replacement_id
WHERE v.param = '$search_param'
GROUP BY v.id

With GROUP_CONCAT you will get all a single result line for each search param, and all its replacements in a comma separated string, which you can then easily work with by iterating over the result in PHP:

$replacements = array() ;
foreach ($results as $result) {
    $currSearchParam = $result['search_param'];
    $currReplacements = $result['replacements'] ;
    $replacements[$currSearchParam] = explode(',', $currReplacements) ;
}

Thank you very much. In fact GROUP_CONCAT is new to me but it seems to be exactly what I was looking for. — d.hill, Jan 25 '11 at 23:26
@d.hill You're quite welcome :) GROUP_CONCAT is very useful that way, saving you the extra iterations. — Fanis Hatzidakis, Jan 26 '11 at 00:11

score 0 · Answer 2 · answered Jan 25 '11 at 23:02

0

Looks like you could use GROUP_CONCAT

answered Jan 25 '11 at 23:02

troelskn

115,121
27
131
155

How to handle a n:m relation correctly with PHP&MySQL?

2 Answers2