[SOLVED] selecting multiple rows with unique values into a single row

[jarednz]

Hi

I have 3 tables

1 table for holding text codes
1 table for holding text references
and my relationship table that holds all the foreign keys.

displayed as such.

code table:
[code]
id       |     code
------------------
1        |  code1
2        |  code2
3        |  code3

 

text reference table:

id     | text_ref
--------------------
1     | hello world
2     | world hello
3     | blah blah12345

 

relationship table (where I do my select)

text_id   | code_id
---------------------
1           | 1
1           | 2
2           | 1
2           | 3

My relationship table has duplicate text ids because there is multiple codes relating to a text reference.

 

so far I have a query like this

 

SELECT tr.text, FROM text_data LEFT JOIN text_codes AS group1 ON group1.id = code_id LEFT JOIN text_reference AS tr ON tr.id = text_id ORDER BY text_id

 

Now this query will return the right data, but obviously will display:

text                 |  code
----------------------------
hello world        | code1
hello world        | code2
blah blah12345  | code1
blah blah12345  | code2
blah blah12345  | code3

 

I want to display a result as:

text                 |  code
----------------------------
hello world        | code1, code2
blah blah12345  | code1, code2, code3

 

How can I attempt to join all the codes to a distinct text reference? I am trying everything I know with loops and arrays but with no luck.

 

Appreciate some help, TIA.

 

[/code]

[Barand]

There's no "obviously" about it.

 

Why don't you just copy and paste your current query as, apart from syntax errors, that query isn't going to give the data that you listed below it since it doesn't select the required columns.

 

try

<?php
include 'db2.php';

$sql = "SELECT tr.text_ref, tc.code 
        FROM text_data AS td
        INNER JOIN text_code AS tc ON tc.id = td.code_id 
        INNER JOIN text_reference AS tr ON tr.id = td.text_id 
        ORDER BY td.text_id, td.code_id";
$res = mysql_query($sql) or die (mysql_error().'<p>$sql</p>');

$ar = array();
while (list($text, $code) = mysql_fetch_row($res)) {
    $ar[$text][] = $code;
}

foreach ($ar as $text => $codes) {
    echo $text . ' : ' . join (', ', $codes) . '<br/>';
}
?>

[jarednz]

Thanks exactly what I needed.

Btw I've never used join() before... I looked it up in manual and it says "alias of implode()" is there a reason to use the function join() over implode() or just personal preference?

 

I wonder though if this task is better suited to be off in the database or not.. I heard group_concat might have done the trick.

 

But thanks anyways mate.

[jarednz]

Just another quick question, if I wanted to make that array in your code even deep with another set/group of codes. How would I do that?

 

Is there a way I can iterate over the array for more than 1 set of codes as its doing just fine now, but I have another column of codes that needs to be inputed.

 

I did manage to add the extra set of codes to the list() by adding another variable. and I checked the list to see if the data was in there and it was. (all good so far). But not sure how to iterate over the array to pick out my new set of codes.

 

Appreciate some more help. Thanks.

 

[Barand]

 

Btw I've never used join() before... I looked it up in manual and it says "alias of implode()" is there a reason to use the function join() over implode() or just personal preference?

 

Personal preference, almost 50% less typing. Same reason I prefer echo to print.

 

Same results, less effort!

 

To iterate over 2D array

 

foreach ($ar as $text => $codes) {
    echo "$text<br>";
    foreach ($codes as $code) {
         echo " - $code<br>";
    }
}

[Barand]

I had to look up group_concat as it isn't one I've used before

 

$sql = "SELECT tr.text_ref , GROUP_CONCAT( DISTINCT tc.code ORDER BY tc.code) as codes
        FROM text_data AS td
        INNER JOIN text_code AS tc ON tc.id = td.code_id 
        INNER JOIN text_reference AS tr ON tr.id = td.text_id
        GROUP BY tr.text_ref";

[jarednz]

I'm just working out how to add a second array of data into the main array at moment. But hoping GROUP_CONCAT will sort out this and I won't have to do much iterating over arrays for my data.

 

Once again, thanks for your help

[jarednz]

I'm not having much luck here mate could you please provide some more help I'd really appreciate it.

 

This is my code now

 

$ar = array();
while ($arraylist = list($text, $groupcode1, $groupcode2) = mysql_fetch_row($delQuery)) {
$ar[$delegation][] = $groupcode1;
$ar[$delegation][$groupcode1][] = $groupcode2;

}

 

my loops

 

foreach ($ar as $text => $groupcode1)
{
echo "<tr>\n";
//text references
echo "\t<td valign=\"top\">";
    echo $delegation;
    echo "</td>\n";

    //group codes
    echo "<td align=\"center\">";
    echo join("<br />", $groupcode1);
    echo "</td>\n";
    echo "<td align=\"center\">";
foreach ($ar as $text => $groupcode2) {
                  echo join("<br />", $groupcode2);
    }



echo "</td>\n";

 

but when i display my result in my table

text               |     group 1    |   group 2
-------------------------------------------
blahblah123    |  code1         |   code1
                    |  Array          |   Array
                    |  code2         |   Array
                    |  Array          |   code2
                    |                   |   Array
-------------------------------------------

 

And it just repeats that all over my table. I am stuffing my loops I know, I'm just not sure how to iterate my array properly.

 

TIA

 

Regards.

[Barand]

I haven't a clue what you are trying to do. Is there a 4th db table involved?

[jarednz]

Nope, i've specified all the tables in use, I think I didn't properly display the group code table however, whoops :/

 

What I probably haven't made clear is.. each code belongs to a group in the group code table

 

labeled such as

 

code 1 | group 1

code 2 | group 1

code 3 | group 2

code 4 | group 2

etc etc

 

my query divides all the codes into the relevant groups, but I'm only pulling one set of group codes in my array and I'm now trying to add in the 2nd group codes.

 

I hope that makes sense I realise I'm a bit vague.. apologies for this.

 

So basically.. I just want to add a seperate amount of data(codes) to my "ar Array()" from the same table (i have this data in my query already).

[Barand]

You originally said it should look like this

[pre]

text            |  code

----------------+--------------------

hello world    | code1, code2

blah blah12345  | code1, code2, code3

[/pre]

 

How do you want it to look now?

[jarednz]

sort of same way, except of course my table would have another colum to accomodate the new group of codes.

 

Essential what I have now is fine and dandy, I just need to know how to get a new array of codes into my "ar Array()".

[Barand]

Here's one way

<?php
include 'db2.php';

$sql = "SELECT tr.text_ref, tc.code, tc.code_group 
        FROM text_data AS td
        INNER JOIN text_code AS tc ON tc.id = td.code_id 
        INNER JOIN text_reference AS tr ON tr.id = td.text_id 
        ORDER BY td.text_id, td.code_id";
$res = mysql_query($sql) or die (mysql_error().'<p>$sql</p>');

$ar = array();
while (list($text, $code, $grp) = mysql_fetch_row($res)) {
    $ar[$text]['codes'][] = $code;
    $ar[$text]['groups'][] = $grp;
}

echo '<table border="1">';
echo '<tr><th>Text</th><th>Codes</th><th>Groups</th></tr>';
foreach ($ar as $text => $data) {
    echo '<tr><td>' . $text . '</td><td>' . join(', ', $data['codes']) . '</td><td>' . join(', ', $data['groups']) . '</td></tr>';
}
echo '</table>'
?>

[jarednz]

Damn.. I was close haha.

 

Awesome mate, exactly what I needed. Thanks so much... case closed