Create PHP two dimensional array from MySQL linked tables

Question

I'm hoping someone could help me with this problem.

I have 2 table:

Courses

+------------+--------------+
| Field      | Type         |
+------------+--------------+
| id         | int(11)      |
| name       | varchar(255) |
+------------+--------------+

Files

+------------+--------------+
| Field      | Type         |
+------------+--------------+
| id         | int(11)      |
| course_id  | int(11)      |
| name       | varchar(255) |
+------------+--------------+

I would like to have code that returns a two dimensional PHP array something like:

Array
(
    [0] => Array
        (
            [id] => 3
            [name] => Digital image processing
            [files] => Array
                (
                    [0] => Array
                        (
                            [id] => 6
                            [name] => DIP-ch02-93-1.pdf
                        )

                    [1] => Array
                        (
                            [id] => 9
                            [name] => A_brief_introduction_to_matlab.pdf
                        )

                )

        )

    [1] => Array
        (
            [id] => 1
            [name] => Artificial intelligence
            [files] => Array
                (
                    [0] => Array
                        (
                            [id] => 12
                            [name] => DIP-ch02-93-1.pdf
                        )

                    [1] => Array
                        (
                            [id] => 1
                            [name] => AI-ch03-922.pdf
                        )

                    [2] => Array
                        (
                            [id] => 2
                            [name] => AI-ch04-932.pdf
                        )

                )

        )

)

I work with php PDO. I have the following code:

$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$stmt = $conn->prepare("SELECT c.id, c.name, f.id as fid, f.name as fname FROM courses c left outer join files f on c.id=f.course_id");
$stmt->execute();

// set the resulting array to associative
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
$courses = $stmt->fetchAll();

print_r($courses);

But it return me this array:

Array
(
    [0] => Array
        (
            [id] => 1
            [name] => Artificial intelligence
            [fid] => 1
            [fname] => AI-ch03-922.pdf
        )

    [1] => Array
        (
            [id] => 1
            [name] => Artificial intelligence
            [fid] => 2
            [fname] => AI-ch04-932.pdf
        )

    [4] => Array
        (
            [id] => 3
            [name] => Digital image processing
            [fid] => 6
            [fname] => DIP-ch02-93-1.pdf
        )

    [5] => Array
        (
            [id] => 3
            [name] => Digital image processing
            [fid] => 9
            [fname] => A_brief_introduction_to_matlab.pdf
        )

    [6] => Array
        (
            [id] => 1
            [name] => Artificial intelligence
            [fid] => 12
            [fname] => DIP-ch02-93-1.pdf
        )

)

I have the following correct code, too:

$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$stmt = $conn->prepare("SELECT id, name FROM courses");
$stmt->execute();

// set the resulting array to associative
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
$courses = $stmt->fetchAll();

for($i = 0; $i < count($courses); $i++){

    $stmt = $conn->prepare("SELECT id, name FROM files where course_id=".$courses[$i]['id']);
    $stmt->execute();

    // set the resulting array to associative
    $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
    $result = $stmt->fetchAll();

    $courses[$i]['files'] = $result;
}
print_r($courses);

It returns correct result But I want remove that for loop from my code. In fact, I would like to retrive data from Mysql with single query.
Could any one help me?

You set the error mode on exception, yet you don't use the try and catch blocks? Noor do you check if the query ran successfully.. — Xorifelse, Apr 29 '16 at 16:02
If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry, Apr 29 '16 at 16:04
Why do you need all that data directly in your Code? BTW use a subselect — inetphantom, Apr 29 '16 at 16:15
you can't fetch formated array from pdo , you have to format it you self — codeGig, Apr 29 '16 at 16:20
@Strawberry I use join in first code but But it return me different array to what i want — Gandom, Apr 29 '16 at 16:23
MySQL returns a result. Use PHP to fetch that result as a (single-dimensional) array. Then use a loop to transform that single-dimensional array to a multi-dimensional array. — Strawberry, Apr 29 '16 at 16:29

codeGig · Accepted Answer · 2016-04-29T16:49:33.173

1

You need to format array again. PDO query return result in row wise.

$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$stmt = $conn->prepare("SELECT c.id, c.name, f.id as fid, f.name as fname FROM courses c left outer join files f on c.id=f.course_id");
$stmt->execute();

// set the resulting array to associative
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
$courses = $stmt->fetchAll();

$formattedcourses = array();

foreach($courses as $course){
     $formattedcourses[$course['id']]['id'] = $course['id'];
     $formattedcourses[$course['id']]['name'] = $course['name'];
     $formattedcourses[$course['id']]['files'][] = array(
                  'id' =>  $course['fid'],
                  'name' => $course['fname']
     );
}

edited Apr 29 '16 at 16:49

answered Apr 29 '16 at 16:31

codeGig

1,024
1
8
11

Thanks @codeGig. It worked. But `$course` is not an object. please edit `$course->id` to `$course['id']` and so on. :) – Gandom Apr 29 '16 at 16:47
Ok, I have changed it – codeGig Apr 29 '16 at 16:49
you said: **_PDO query return result in row wise_**. Is there another better ways except PDO to return exact result without forming it? – Gandom Apr 29 '16 at 16:57
Structured database mysql returned everything in row wise, use ORM for database interaction, it is safe and object base mapping. – codeGig Apr 29 '16 at 17:04
Using `fetchAll()` then immediately looping the results in the same layer is inappropriate. If you are going to iterate the result set in the same layer, then fetch the rows individually. – mickmackusa Dec 14 '20 at 23:06

score -1 · Answer 2 · answered Apr 29 '16 at 16:20

-1

SELECT * 
FROM files AS f 
JOIN courses AS c ON c.id = f.course_id
WHERE course_id IN (Select id from courses)"

Last line does not make sense if you have no condition for the courses Just add a WHERE into the subselect.

answered Apr 29 '16 at 16:20

inetphantom

2,498
4
38
61

But it returs something like this: `Array ( [0] => Array ( [id] => 1 [name] => Artificial intelligence [fid] => 1 [fname] => AI-ch03-922.pdf ) )` – Gandom Apr 29 '16 at 16:32
That is a 2 dimensional array as you asked. Dinemsion 1: Files, Dimension 2: File Properties – inetphantom May 03 '16 at 14:35

Create PHP two dimensional array from MySQL linked tables

Courses

Files

2 Answers2