foreach循环中嵌套while循环的结果集结构

Question

I'm using Sphinx Search to search item on my website.I'm using foreach to find out the index,where the search is coming from? Foreach loop will iterate through all items that matches search term and will find out the index.

All items in database have user_id that reference to user from user table.So,what I want to output is show few items(searches for) and put it together under that user.If search for 'Book',then output Like this

Alex       //Book1,Book2,Book3 all of them has user_id of 2 which refers to Alex

Book1
Book2
Book3

Jordan     //Book11,Book12,Book18 all of them has user_id of 5 which refers to Joradn

Book11
Book12
Book18

and so on.

Here is the code for that

foreach ( $res["matches"] as $doc => $docinfo ) {
    switch($docinfo['attrs']['table_id']) {
       case 1:                         //if search come from index 1

        $res_db = mysqli_query($connect3,'select id_2, image, user_id, title, detail from lunch_menu where id_2 = (('.$doc.'-300000))');
            if ($res_db === false) {
                    echo "Error in mysql query #" . mysqli_errno($connect) . ' - ' . mysqli_error($connect);
            } else {
            $row = mysqli_fetch_assoc($res_db);
                            $connect4=mysqli_connect('localhost', 'root', '','user');
                  $sql2="SELECT * FROM `user`.`user_det` WHERE id='".$row['user_id']."' GROUP BY id ORDER
                  BY id DESC";
                  $query2=mysqli_query($connect4,$sql2);
                  while($row2 = mysqli_fetch_assoc($query2)){    //line X
                  $userx=$row2['id'];
                  $image=$row2['img'];
                  $busi_title=$row2['busi_title'];

                  $page_owner.="<img src='../../account/$userx/$image' width='140'
                  height='140'><b>$busi_title</b><hr>";

                    $res_db2 = mysqli_query($connect3,'select id_2, image, user_id, title, detail from
                    lunch_menu where id_2 in ((' . $doc . '-300000)) order by field(id_2,('.$doc.'-300000))');
                    $alu="";
                    while($row3 = mysqli_fetch_assoc($res_db2)){
                            $alu.=

                            '<img src="../../'.$row3['user_id'].'/lunch/'.$row3['image'].'" width="100" height="100">'
                            . '<a href="../../'.$row3['user_id'].'/menu_item2.php">' . $row3['title'] . '<a>'

                            . '<br/> '.$row3['detail'].'<br><br><br><br>' ;
                      }
                    $all.="<div id='' style='border:2px solid black;'>".$page_owner.".".$alu."</div>";
                    }
    } 

But above code is giving me result like this

Alex
Book1

Alex
Book2

Alex
Book3

Jordan
Book11

Jordan
Book12

Jordan
Book18

and so on.

What modification do I need to get my code work like expected.I have tried my best to find a solution,but didn't help.So,just made a SO account to post the question.

Please help,if you can.That would be a really awesome.

Thanks in advance

Thank you Ryan for your reply.We are almost there

1 more fix,then we are done.Now what I'm getting is

Problem 
Result is almost the way,I was looking for except for the first one.This is what I'm getting

Alex
Book1

Jordan
Book11
Book12
Book18

Alex
Book2
Book3

If I can do a temporary order by user_id before echoing out $all,this bug might be fixed.

Because $all prints user and it's corresponding item the way it is sorted out in database

Please be informed,I'm using the second way,you showed.First one is not working.

I have figured out Second part of the problem.To achieve that desired result,I needed to do this

$cl->SetSortMode(SPH_SORT_EXTENDED, '@relevance DESC,user_id ASC');

This line of code output most relevant search results first,and categorize by user_id Ascending.

Special Thanks to Ryan

Answer 1

So I'm a little confused about where the duplicate user is stemming from since I don't know exactly what you're querying but I have ideas what could be causing it.

First, if the inner loop is the problem, its a simple fix. Only show the 'user_id' on the first looping.

                $alu="";
                $firstLoop = true;
                while($row3 = mysqli_fetch_assoc($res_db2)){
                        if($firstLoop){
                             $alu.= '<img src="../../'.$row3['user_id'].'/lunch/'.$row3['image'].'" width="100" height="100">'
                             $firstLoop = false;
                         }
                       $alu.= '<a href="../../'.$row3['user_id'].'/menu_item2.php">' .  row3['title'] . '<a>'

                        . '<br/> '.$row3['detail'].'<br><br><br><br>' ;
                  }

Alternatively, it could be happening in the outter loop as Sean suggested. In which case, we can just keep track of the previous user to determine if $userx should be set and $page_owner used.

     $prevUsr = '';
     foreach ( $res["matches"] as $doc => $docinfo ) {
switch($docinfo['attrs']['table_id']) {
   case 1:                         //if search come from index 1

    $res_db = mysqli_query($connect3,'select id_2, image, user_id, title, detail from lunch_menu where id_2 = (('.$doc.'-300000))');
        if ($res_db === false) {
                echo "Error in mysql query #" . mysqli_errno($connect) . ' - ' . mysqli_error($connect);
        } else {
        $row = mysqli_fetch_assoc($res_db);
                        $connect4=mysqli_connect('localhost', 'root', '','user');
              $sql2="SELECT * FROM `user`.`user_det` WHERE id='".$row['user_id']."' GROUP BY id ORDER
              BY id DESC";
              $query2=mysqli_query($connect4,$sql2);
              while($row2 = mysqli_fetch_assoc($query2)){
              $userx=$row2['id'];
              $image=$row2['img'];
              $busi_title=$row2['busi_title'];

              $page_owner.="";

              // only set the page owner if the user id is different
              if($prevUsr != $userx){
                   $page_owner.="<img src='../../account/$userx/$image' width='140'
                   height='140'><b>$busi_title</b><hr>";

                   $prevUsr = $userx;
              }

                $res_db2 = mysqli_query($connect3,'select id_2, image, user_id, title, detail from
                lunch_menu where id_2 in ((' . $doc . '-300000)) order by field(id_2,('.$doc.'-300000))');
                $alu="";
                while($row3 = mysqli_fetch_assoc($res_db2)){
                        $alu.=

                        '<img src="../../'.$row3['user_id'].'/lunch/'.$row3['image'].'" width="100" height="100">'
                        . '<a href="../../'.$row3['user_id'].'/menu_item2.php">' . $row3['title'] . '<a>'

                        . '<br/> '.$row3['detail'].'<br><br><br><br>' ;
                  }
                $all="<div id='' style='border:2px solid black;'>".$page_owner.".".$alu."</div>";
                }
} 

I hope one of these helps you out.

Happy coding. Ryan