CODEWITHSUNDEEP
mysqlpdo
steeped
steeped

Reputation: 2633

Properly joining tables without repeat data

I am trying to join tables to prevent too many database queries, but I don't like the way the data is returning.

The Query:

SELECT person.name, dog.dog_name FROM person JOIN dog ON person.id = dog.person_id

The return looks like this:

Array
(
[0] => Array
    (
        [name] => Jim
        [dog_name] => Wolf
    )

[1] => Array
    (
        [name] => Jim
        [dog_name] => Frisky
    )

[2] => Array
    (
        [name] => Tina
        [dog_name] => Pokedot
    )

[3] => Array
    (
        [name] => Tina
        [dog_name] => Spiky
    )
)

Is it possible to have the query instead return something like:

Array
(
[0] => Array
    (
        [name] => Jim
        [dog_name] => array(Wolf, Frisky)
    )

[1] => Array
    (
        [name] => Tina
        [dog_name] => array(Pokedot, Spiky)
    )
)

Upvotes: 1

Views: 418

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562270

The closest solution is:

SELECT person.name, GROUP_CONCAT(dog.dog_name) AS dog_names
FROM person JOIN dog ON person.id = dog.person_id
GROUP BY person.id

This returns a string which is a comma-separated list of dog names, not a PHP array. You'll have to explode() that string in application code.

Note that the default length limit for GROUP_CONCAT() is 1024 characters, and it is controlled by the configuration option group_concat_max_len.

I agree with the comment from @KonstantinWeitz, it's worthwhile to let the RDBMS do what it's best at, and then use PHP code to post-process the results into a format you want.

For example, here's how I'd do it to return the array you described:

$peoplesdogs = array();
$stmt = $pdo->query("SELECT person.name, dog.dog_name FROM person JOIN dog ON person.id = dog.person_id");
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  $peoplesdogs[$row["name"]]["name"] = $row["name"];
  $peoplesdogs[$row["name"]]["dog_name"][] = $row["dog_name"];
}
return array_values($peoplesdogs);

Upvotes: 8

Related Questions