optimize my loop to return instead of doing 10 queries

Question

I have a limited number of students (10) returned in an array in the below variable,

$result variable is a array, it contains student ID's like the example below

Array ( 
[student1] => 4 
[student2] => 1 
[student3] => 3 
[student4] => 2 
[student5] => 5 
[student6] => 10 
[student7] => 12 
[student8] => 16 
[student9] => 17 
[student10] => 18 
)

what i tried

$values = implode(", ", $result);
$sql = "SELECT sub1, sub2 FROM students WHERE students.id IN (" . $values . ")";

try{
    $db = new db();
    $db = $db->connect();
    $stmt = $db->query($sql);

    $subject = $stmt->fetchAll(PDO::FETCH_OBJ);

    $db = null;
    if(empty($subject)) {
        $response->getBody()->write
        ('{"error":{"message":"Invalid Request}}');
    } else {
        $subject = json_decode(json_encode($subject), True);
        if (in_array("BIO", $subject))
          {
            return true;
          }
        else
          {
            return false;
          }
     print_r($subject);
    }
  } catch(PDOException $e) {}

I am sure How i can be optimize my query in one instead of looping 10 queries

Use the IN() clause, it's much more efficient than running a single query inside your loop. — BenM
– BenM, Commented Aug 23, 2017 at 14:36
Putting the different subjects they are enrolled in into columns in the student table really breaks normalization and will probably cause you other troubles as you continue developing this. You should consider putting the enrollments into a separate table that maps student ids to courses for a given term. — Don't Panic
– Don't Panic, Commented Aug 23, 2017 at 14:45
thank you @Don'tPanic i agree with you, but its not a scalable project and limited to always 10 students and 2 subs for each, need to figure out how to do it in the way they provided me the db — mirvatJ
– mirvatJ, Commented Aug 23, 2017 at 14:49

Momo Kebab · Accepted Answer · 2017-08-23 15:02:11Z

1

If you want to do the query once you can make a request like this :

$values = implode(", ", $result);
$sql = "SELECT sub1, sub2 FROM students WHERE students.id IN (" . $values . ")";

This will return you all your students with one request

EDIT : removed the foreach, thanks Don't Panic

edited Aug 23, 2017 at 15:02

answered Aug 23, 2017 at 14:41

Momo Kebab

2122 silver badges11 bronze badges

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

5 Comments

mirvatJ Over a year ago

this only returned the first students sub1 and sub2

Don't Panic Over a year ago

That foreach loop is a really long-winded way to do $values = implode(", ", $result);

Momo Kebab Over a year ago

try to print $values to make sure you have all the ids

Momo Kebab Over a year ago

and the query still only returns the first student's sub1 and sub2 and not all the sudents ?

mirvatJ Over a year ago

Let us continue this discussion in chat.

Collectives™ on Stack Overflow

optimize my loop to return instead of doing 10 queries

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related