CODEWITHSUNDEEP
mysqlsql
user1801605
user1801605

Reputation: 441

How to query single row from Table 1 and multiple rows from Table 2?

TABLE 1
student_id | name
-----------------
1          | A
2          | B
3          | C

TABLE 2
photo_id   | student_id | path
------------------------------
1          | 1          | /path/to/folder/apple.jpg
2          | 1          | /path/to/folder/orange.jpg
3          | 2          | /path/to/folder/cantaloupe.jpg
4          | 1          | /path/to/folder/lemon.jpg

public function studentPhotos($student_id) {
    $query = "SELECT table1.name,table2.path as photos FROM table1 INNER JOIN table2 ON table1.student_id = table2.student_id WHERE table1.student_id=?";
    $stmt = $this->con->prepare($query);
    $stmt->bind_param("i",$student_id);
    $stmt->execute();
    $stmt->bind_result($name,$photos);
    $student = array();
    while($stmt->fetch()){
        $temp = array(); 
        $temp['name'] = $name;
        $temp['photos'] = $photos;
        $student[] = $temp;
    }
    $stmt->close();
    return $student; 
}

Result: [{A,/path/to/folder/apple.jpg},{A,/path/to/folder/orange.jpg},{A,/path/to/folder/lemon.jpg}]

Desired Result {A,/path/to/folder/apple.jpg,/path/to/folder/orange.jpg,/path/to/folder/lemon.jpg}

Of course I can change it using PHP, but I'm talking hundreds of photos for every student. So all student's attributes will be repetitive for as many as the photos in the result array.

Is there a better way in query?

Upvotes: 0

Views: 117

Answers (2)

ascsoftw
ascsoftw

Reputation: 3476

You can use group_concat method. It will give you comma separated values for the column specified.

Below is the query:

SELECT table1.name, 
       Group_concat(table2.path) AS photos 
FROM   table1 
       INNER JOIN table2 
               ON table1.student_id = table2.student_id 
WHERE  table1.student_id =?

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522007

Do you want to group concatenate here:

SELECT
    t1.name,
    COALESCE(GROUP_CONCAT(t2.path), 'NA') AS paths
FROM table1 t1
LEFT JOIN table2 t2
    ON t1.student_id = t2.student_id
WHERE
    t1.student_id = ?;

Upvotes: 2

Related Questions