Artificial Intelligence Question posted in
ChatGBT is becoming a world-wide phenomena, try it out here.

Create PHP two dimensional array from MySQL linked tables – Artificial Intelligence

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?

Tags:

2

Answers


  1. 0 
    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.

    Login or Signup to reply.
  2. 0

    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']
     );
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search