Gandom Gandom - 7 months ago 35
PHP Question

Create PHP two dimensional array from MySQL linked tables

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?

Answer

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
     );
}
Comments