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
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