在另一个while循环中使用while循环来输出特定数据

Question

I have a while loop that outputs users that have open tickets based on a mysql query. I also have another while loop that outputs all open tickets. They both work. I tried to put the second while loop in the first in such a way that i can list each user with their open tickets. I used "in_array(). The problem is that it Outputs the first user and tickets, but doesnt for the rest.

$dbc = mysqli_connect('localhost', 'root', 'root', 'tickets2') or die('Error connecting to Mysql Server');

$query1 = " SELECT DISTINCT number, username, subject, o.name " .
"FROM ost_ticket t, ost_staff s, ost_ticket__cdata d, ost_user u, ost_organization o " .
"WHERE status_id = 1 AND t.staff_id=s.staff_id AND d.ticket_id=t.ticket_id AND u.id=t.user_id AND o.id=u.org_id " .
"ORDER by username" ;

$query2 = " SELECT DISTINCT username " .
"FROM ost_ticket t, ost_staff s, ost_ticket__cdata d, ost_user u, ost_organization o " .
"WHERE status_id = 1 AND t.staff_id=s.staff_id AND d.ticket_id=t.ticket_id AND u.id=t.user_id AND o.id=u.org_id " .
"ORDER by username" ;

$tickets = mysqli_query($dbc, $query1) or die ('Error');

$users = mysqli_query($dbc, $query2) or die ('Error');

while($row = mysqli_fetch_assoc($users)){
  print_r($row);
  echo "<br />";
  $user = $row['username'];
   while($row1 = mysqli_fetch_assoc($tickets)){
   if(in_array($user, $row1)){
     print_r($row1);
     echo "<br />";
    }
 }

}

while($row1 = mysqli_fetch_assoc($tickets)){
print_r($row1);
echo "<br />";
}

This is the output i get

Array ( [username] => aolusola )
Array ( [number] => 905495 [username] => aolusola [subject] => PAssword change Tool [name] =>  A Hotel )
Array ( [number] => 770279 [username] => aolusola [subject] => Laundry posting  [name] => Bfish Hotel )
Array ( [number] => 972356 [username] => aolusola [subject] => New TC  RVC 8 [name] => Inter Hotel )
Array ( [username] => cakaluka )
Array ( [username] => fmbah )
Array ( [username] => habel )
Array ( [username] => iadebanjo )
Array ( [username] => iakintade )
Array ( [username] => jajani )
Array ( [username] => jakinmosin )
Array ( [username] => jomaivboje )
Array ( [username] => logunleye )
Array ( [username] => oogundimu )
Array ( [username] => panawe )
Array ( [username] => solushola )
Array ( [username] => ugouvietesivwi )

The First user outputs according to plan, but the rest not so much. Any Idea what i am missing

Thanks

Answer 1

Try the following code:

$users = mysqli_query($dbc, $query2) or die ('Error');

while($row = mysqli_fetch_assoc($users)){
  print_r($row);
  echo "<br />";
  $user = $row['username'];
$tickets = mysqli_query($dbc, $query1) or die ('Error');
   while($row1 = mysqli_fetch_assoc($tickets)){
   if(in_array($user, $row1)){
     print_r($row1);
     echo "<br />";
    }
 }

}

Execute the query inside the while loop, because mysqli_fetch_assoc is forward only. So, you need to execute it every time the loop runs. OR you can only get tickets for current user only by this:

$dbc = mysqli_connect('localhost', 'root', 'root', 'tickets2') or die('Error connecting to Mysql Server');

$query1 = " SELECT DISTINCT number, username, subject, o.name " .
"FROM ost_ticket t, ost_staff s, ost_ticket__cdata d, ost_user u, ost_organization o " .
"WHERE status_id = 1 AND t.staff_id=s.staff_id AND d.ticket_id=t.ticket_id AND u.id=t.user_id AND o.id=u.org_id " .
"ORDER by username";

$query2 = " SELECT DISTINCT username " .
"FROM ost_ticket t, ost_staff s, ost_ticket__cdata d, ost_user u, ost_organization o " .
"WHERE status_id = 1 AND t.staff_id=s.staff_id AND d.ticket_id=t.ticket_id AND u.id=t.user_id AND o.id=u.org_id " .
"ORDER by username";

$users = mysqli_query($dbc, $query2) or die ('Error');

while($row = mysqli_fetch_assoc($users)){
  print_r($row);
  echo "<br />";
  $user = $row['username'];
$query3 = " SELECT DISTINCT number, username, subject, o.name " .
"FROM ost_ticket t, ost_staff s, ost_ticket__cdata d, ost_user u, ost_organization o " .
"WHERE `user_name` = '{$user}' status_id = 1 AND t.staff_id=s.staff_id AND d.ticket_id=t.ticket_id AND u.id=t.user_id AND o.id=u.org_id " .
"ORDER by username" ;
$tickets = mysqli_query($dbc, $query1) or die ('Error');
   while($row1 = mysqli_fetch_assoc($tickets)){
     print_r($row1);
     echo "<br />";
 }

}

$tickets = mysqli_query($dbc, $query1) or die ('Error');
while($row1 = mysqli_fetch_assoc($tickets)){
print_r($row1);
echo "<br />";
}