query indicates an undesired event

Question

I want to make a friend search section in facebook style. But i just want to result my friends in a friends table. My tables are in this DEMO .

    $result = mysqli_query($this->db,"        
SELECT U.username
     , U.userid
     , U.userfullname
     , UT.username
     , UT.userid
     , UT.userfullname
     , F.role 
  FROM friends F 
  JOIN users U 
    ON F.userone = U.userid
  JOIN users UT 
    ON F.usertwo = UT.userid
 WHERE U.username Like '%$key' 
    OR UT.userfullname Like '$key%' 
    OR UT.username Like '$key%' 
    OR UT.userfullname Like '%$key' 
    OR UT.username Like '$key' 
    OR UT.userfullname Like '$key'  
   AND F.userone = '$uid' 
   AND (F.role='fri' OR F.role = 'flwr')
 ORDER 
    BY F.userone 
 LIMIT 10
") or die(mysqli_error($this->db));
    while($row=mysqli_fetch_array($result,MYSQLI_ASSOC)) {
         $data[]=$row;
    }
    if(!empty($data)) { 
     return $data;
    }

User id($uid) of the person searching is 1 and the key is for example aziz because of azizs' userid is 2 and if you check the friends table userone(1) and usertwo(2) are friends on there. How can we know they are friend because of the role is fri. You can see it in the DEMO page also.

Now, what is the problem! The problem is when $uid(1) search his/her friend the query showing also role ='me' but i just want to show F.role='fri' OR F.role = 'flwr' . What is the problem in my query.

Have a look at (e.g.) [SQL Logic Operator Precedence: And and Or](https://stackoverflow.com/q/1241142/6248528) and then add some parenthesis, they are not that expensive. — Solarflare, Aug 26 '18 at 14:16
You've wisely used parentheses for the role condition, but not for the userone condition. — Strawberry, Aug 26 '18 at 14:17

score 1 · Accepted Answer · answered Aug 26 '18 at 14:19

1

How about...

SELECT U.username
     , U.userid
     , U.userfullname
     , UT.username
     , UT.userid
     , UT.userfullname
     , F.role 
  FROM friends F 
  JOIN users U 
    ON F.userone = U.userid
  JOIN users UT 
    ON F.usertwo = UT.userid
 WHERE (
       U.username Like '%$key' 
    OR UT.userfullname Like '$key%' 
    OR UT.username Like '$key%' 
    OR UT.userfullname Like '%$key' 
    OR UT.username Like '$key' 
    OR UT.userfullname Like '$key' 
     ) 
   AND F.userone = '$uid' 
   AND F.role IN('fri','flwr')
 ORDER 
    BY F.userone 
 LIMIT 10

Note that you remain open to injection with this approach.

answered Aug 26 '18 at 14:19

Strawberry

33,750
13
40
57

you are talking about $uid and $key for injection ? If yes no problem i can fix it. If not please tell me why you said that ? – AlwaysStudent Aug 26 '18 at 14:46
Dear Can you explain me about injection, please ? – AlwaysStudent Aug 26 '18 at 15:10
1

Yep. You nailed it. – Strawberry Aug 26 '18 at 15:32

query indicates an undesired event

1 Answers1