How Do I Keep Adding To This Array?

Question

I am running the following code, which runs a query, gets all the student_id's in a class, then for each student id it gets all their grades. My problem is though that the data put into the array doesnt stay in it when it goes back and runs the for loop for the second, third, etc student. Is there a way to keep the data in the array and keep adding to it? I have included some code below to help paint the picture. Thanks All

$sql = "SELECT student_id FROM users_table WHERE class_id ='5'";
$result = $db->sql_query($sql);
$row = $db->sql_fetchrow($result);
$row_cnt = mysqli_num_rows($result);
$students = array();

for($y=0; $y<$row_cnt; $y++)
{
    array_push($students, $row['student_id']);

    $sql2 = "SELECT student_grade FROM grades_table WHERE student_id ='".$students[$y]."'";
    $result2 = $db->sql_query($sql2);
    $row2 = $db->sql_fetchrow($result2);
    $row_cnt2 = mysqli_num_rows($result2);

    for($z=0; $z<$row_cnt2; $z++)
    {
         array_push($students, array($row['student_grade']));
    }
}

You might be better using an SQL JOIN to reduce this to a single query: `SELECT s.student_id, g.student_grade FROM users_table s LEFT JOIN grades_table g ON g.student_id = s.student_id WHERE class_id ='5'` — Mark Baker, Jul 31 '14 at 19:07
thanks for the suggestion, however, I use the array that is created to assemble a larger sql query. I was hoping for something more along the line of somehow declaring the variable a type of global so that it wont reset to an empty variable. I will sit and try to use your suggestion to see if I can utilize it. Thanks for your time — tracyaa80, Jul 31 '14 at 19:23

score 0 · Answer 1 · answered Aug 01 '14 at 08:08

0

In the second sql2 part you may want to do something like this instead:

for($z=0;$z<$row_cnt2;$z++) {
   $students[$row['student_id']] = $row['student_grade'];
}

Doing it like this, your array doesn't get overwritten, and you'll get something like this:

array(
    5 => "A"
    6 => "B"
)

Where 5 is the student_id and A is the grade.

But, of course, this can also easily be solved with the JOIN query Mark Baker provided.

What's the "larger query" you're writing?

answered Aug 01 '14 at 08:08

Simon Fredsted

964
2
14
26

As it's written right now you'll only store the last grade for each student. You might want to append the grades instead. – knrdk Aug 01 '14 at 11:14

score 0 · Answer 2 · edited May 23 '17 at 11:49

What you are doing at the moment will make the students array look like a big basket with everything poured in... You should create an index then assign the grades array to it

$students = array();
for($i = 0; $i < $row_cnt; $i++)
{
    $sql2 = "SELECT student_grade FROM grades_table WHERE student_id ='".$students[$y]."'";
    $result2 = $db->sql_query($sql2);
    $row2 = $db->sql_fetchrow($result2);
    $row_cnt2 = mysqli_num_rows($result2); //probably don't need this count anymore

    $students[$row['student_id']] = $row2;
}

You should take note of Mark's comment... it provides a more elegant way. Also learn about prepared statements and how the help prevent SQL injection

How Do I Keep Adding To This Array?

2 Answers2