Make SQL selection from nested arrays

Question

I am trying to make a selection based on a nested array I get from a prior selection.

Here is where I make my first selection:

$coursequery = " 
            SELECT
                courseID
            FROM enrollments 
            WHERE 
                studentID = '$userid' 
        "; 

        try 
        { 
            $stmt = $db->prepare($coursequery); 
            $result = $stmt->execute(); 
        } 
        catch(PDOException $ex) 
        {  
            die("Failed to run query: " . $ex->getMessage()); 
        } 

        $rows = $stmt->fetchAll(); 

            $_SESSION['studentcourses'] = $rows;

This gets all the courseID's in the following format:

Array ( [0] => Array ( [courseID] => 6 ) [1] => Array ( [courseID] => 7 ) )

and I want to be able to access these ID's for selecting information from a different table. I've started by trying to use a for loop to grab all the "course information" depending on the ID.

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

    $coursequery = " 
            SELECT
                *
            FROM courses 
            WHERE courseID = '$studentcourses[$i]'

        "; 

        try 
        { 
            $stmt = $db->prepare($coursequery); 
            $result = $stmt->execute(); 
        } 
        catch(PDOException $ex) 
        {  
            die("Failed to run query: " . $ex->getMessage()); 
        } 

        $row = $stmt->fetchAll(); 

            $_SESSION['studentcourseinfo'] = $row;

    }

Any help would be greatly appreciated in accomplishing this!

Start with mysql JOIN.

u_mulder
– u_mulder

2016-02-24 19:45:13 +00:00
Commented Feb 24, 2016 at 19:45 — u_mulder
– u_mulder, Commented Feb 24, 2016 at 19:45

cmorrissey · Accepted Answer · 2016-02-24 19:51:47Z

1

You can accomplish this with one SQL query thus eliminating all these loops

SELECT
    *
FROM courses
     INNER JOIN enrollments ON 
         enrollments.courseID = courses.courseID 
         AND enrollments.studentID = '$userid'
WHERE 1

answered Feb 24, 2016 at 19:51

cmorrissey

8,5912 gold badges26 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Guillermo Andres Fuentes Moral · Accepted Answer · 2016-02-24 19:53:31Z

0

you can use same array in query, can use implode function

  $coursequery =  "SELECT  * FROM courses 
  WHERE courseID  IN (" . implode(",", $studentcourses) . ");";

As long as you change the output format

Array (6, 7)

where 6 and 7 would be the id of the courses

Also in you code have mistake in rewrite var $_SESSION['studentcourseinfo'] = $row; these overwriting the same variable at a time

answered Feb 24, 2016 at 19:53

Guillermo Andres Fuentes Moral

7336 silver badges18 bronze badges

Collectives™ on Stack Overflow

Make SQL selection from nested arrays

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related