CODEWITHSUNDEEP
phpmysqlgroup-concat
Shaokan
Shaokan

Reputation: 7684

Mysql joining 1 row with multiple rows - group concat or php?

Consider the following example:

+----------+--------+-------------+----------+
| Person_id| Person | Language_id | Language |
+----------+--------+-------------+----------+
| 1        | Bob    | 5           | English  |
| 1        | Bob    | 3           | Italiano |
| 1        | Bob    | 8           | Deutsch  |
+----------+--------+-------------+----------+

and the query is (not that important, just scripting to show you the table structure):

SELECT pl.Person_id, Person, Language_id, Language FROM people as p
LEFT JOIN people_languages as pl ON p.Person_id = pl.Person_id 
LEFT JOIN languages as l ON pl.language_id = l.language_id
WHERE pl.Person = 1;

So basically, if the tables are constructed in this way, is it better to retrieve all results as shown above and then create a php function that creates a Person Model with languages_id and languages in an array, or using group_concat to retrieve a single row and then explode the languages and languages_id into an array?

By the way, no matter what I do, at the end I'd like to have a Person Model as the following:

class Person { 

    public $person_id; // 1
    public $person; // Bob
    public $language_id; // Array(5, 3, 8)
    public $language; // Array(English, Italiano, Deutsch);

    .
    . // Functions 
    .

}

Upvotes: 1

Views: 258

Answers (3)

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

Here is the answer. You can use both ways but in my opinion it is much much better to use group concat. The reason is that this will increase performance as well as reduce the php code. If you go on with the example you gave you will have to do much coding on the php end. And sometimes it becomes difficult to handle on php end. I had this experience a couple of months ago. Instead using group concat will fetch you single row having everything you need for each person. On the php end simple extract the Group Concated cell and make another loop or put it in array. That is easy to handle.

Upvotes: 2

codingbiz
codingbiz

Reputation: 26396

I think you should separate the queries into their separate model

There should be a Language model and will keep this simple

class Language
{
   function getId() { return $id; }
   function getDescription { return $description; }
}


class Person { 

public $person_id; // 1
public $person; // Bob
public $languages; //this will store array of Language object

}

//From DataAccess

function getPerson($person_id)
{
    $person = new Person();
    //select only from Person table
    //fill $person properties from records
    //$person.person_id = $row['person_id']; etc

    //select from people_languages joined to language where person_id=$person_id
    $person->languages = getLanguagesByPerson($person->person_id); //returns array of languages

    return $person;
}

You can now have

$person = getPerson(123);
$person->langauges[0]->getId(); //language id
$person->langauges[0]->getDescription(); //language id

$person->langauges[1]->getId(); //language id
$person->langauges[1]->getDescription(); //language id

Or loop through the languages

foreach($person->languages as $lang)
{
   //use($lang->getId());
   //use($lang->getDescription();
}

Upvotes: 2

Venkata Krishna
Venkata Krishna

Reputation: 15112

Consider using a Dictionary

    class Person { 

        public $person_id; // 1
        public $person; // Bob
        //I don't know php but for your idea
        public Dictionary<int,string> languageList; // KeyValuePairs {(5,English),(3,Italiano),(8,Deutsch)}
        .
        . // Functions 
        .

    }

Upvotes: 0

Related Questions