Nic Nic - 6 months ago 9
SQL Question

Combining results of Mysql Join

I am having a problem with Mysql join. I have two tables, a

center_contacts
table and a
center_contacts_notes
.
center_contacts_notes
are linked via a
contact_id
that is found in both tables.

Inside of
center_contacts_notes
there can be multiple rows per
contact_id
and I want to grab all of these and put them in a sub array of the results.

For example, here is what my
center_contacts_notes
table looks like:

contact_id | note
------------------------
1 test
2 hello
3 sup
1 moo


Here is where I am attempting to grab the data:

$this->db->select('center_contacts.id, FirstName, LastName, center_contacts_notes.note');
$this->db->from('center_contacts');
$this->db->join('center_contacts_notes', 'center_contacts_notes.contact_id = center_contacts.id');


Note that I am using Codeigniter 3.

Here is what I get from this:

Array
(
[id] => 1
[FirstName] => Bob
[LastName] => Smith
[note] => test
)
Array
(
[id] => 1
[FirstName] => Bob
[LastName] => Smith
[note] => moo
)


These are two different arrays inside of my results. This is impractical for my use as I need one array that contains both notes. Something like this:

Array
(
[id] => 1
[FirstName] => Bob
[LastName] => Smith
[note] => Array(test, moo)
)


Is this possible, and if so how would I accomplish it? Thanks.

Answer

I don't know if you can get the two dimensional array like you've mentioned, but there is a workaround to get similar results. This might be helpful to you.

What you need to do is use Group By and group_concat(). Group by the table by contact_id and apply group_concat() on center_contacts_notes.note.

Your query should look like this.

$this->db->select('center_contacts.id, FirstName, LastName, GROUP_CONCAT(center_contacts_notes.note)');
$this->db->from('center_contacts');
$this->db->join('center_contacts_notes', 'center_contacts_notes.contact_id = center_contacts.id');
$this->db->group_by('center_contacts.id');

By default group_concat will concat the column by ,. You can change it the following way.

GROUP_CONCAT(center_contacts_notes.note SEPARATOR 'YOUR_SEPARATOR_STRING')

This will return result as follow:

Array
(
    [id] => 1
    [FirstName] => Bob
    [LastName] => Smith
    [note] => test[YOUR_SEPARATOR_STRING] moo
)

You can use PHP explode to convert note string into array by providing separator value.